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.
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.