On Sun, May 1, 2016 at 9:18 PM, drum.lu...@gmail.com <drum.lu...@gmail.com> wrote:
> To clarify, the index is based on a function called "split_part(....) >> The WHERE clause is only referencing the full_part column, so the planner >> cannot associate the index with the full_part column. >> > > Thanks for the explanation, Melvin. > > It would be simple like: > > CREATE INDEX CONCURRENTLY ON gorfs.inode_segments USING btree > ("full_path"); > > ? > > Thanks again. > Lucas > >CREATE INDEX CONCURRENTLY ON gorfs.inode_segments USING btree ("full_path"); Yes, that should work. A word of caution, only create additional indexes that will actually be used in queries. You can check how often indexes are used (and status) with: SELECT n.nspname as schema, i.relname as table, i.indexrelname as index, i.idx_scan, i.idx_tup_read, i.idx_tup_fetch, CASE WHEN idx.indisprimary THEN 'pkey' WHEN idx.indisunique THEN 'uidx' ELSE 'idx' END AS type, pg_get_indexdef(idx.indexrelid), CASE WHEN idx.indisvalid THEN 'valid' ELSE 'INVALID' END as statusi, pg_relation_size(quote_ident(n.nspname)|| '.' || quote_ident(i.relname)) as size_in_bytes, pg_size_pretty(pg_relation_size(quote_ident(n.nspname)|| '.' || quote_ident(i.relname))) as size FROM pg_stat_all_indexes i JOIN pg_class c ON (c.oid = i.relid) JOIN pg_namespace n ON (n.oid = c.relnamespace) JOIN pg_index idx ON (idx.indexrelid = i.indexrelid ) WHERE n.nspname NOT LIKE 'pg_%' ORDER BY 1, 2, 3; -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.