Re: index question

On Sun, May 1, 2016 at 9:18 PM, drum(dot)lucas(at)gmail(dot)com <drum(dot)lucas(at)gmail(dot)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.