Re: index question

[Melvin Davidson <melvin6925@xxxxxxxxx>]

On Sun, May 1, 2016 at 9:18 PM, drum.lucas@xxxxxxxxx <drum.lucas@xxxxxxxxx> 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.