Re: Statistics on index usage

On Tue, Nov 1, 2016 at 10:43 AM, François Battail <
francois(dot)battail(at)sipibox(dot)fr> wrote:

> Dear List,
>
> does pgsql maintains statistics on index usage? I mean just a counter
> for each index in the database, incremented each time time it is used.
> It would be useful to help cleaning almost unused index and to avoid
> poisoning the global cache.
>
> I've found nothing so far but may be I've been missing something.
>
> Best regards.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

>does pgsql maintains statistics on index usage?
Yes, try this query. Look at idx_scna, & idx_tup_fetch

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,
idx.indisexclusion,
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 i.relname LIKE '%%'
AND 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.