Postgres Pro
Downloads
Home   >   mailing lists

Thread: Statistics on index usage

Statistics on index usage

From
François Battail
Date:
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.

Re: Statistics on index usage

From
Scott Marlowe
Date:
On Tue, Nov 1, 2016 at 8:43 AM, François Battail
<francois.battail@sipibox.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.

Yup it does keep track of index usage. To see all the various stuff
postgres keeps track of etc, try typing

select * from pg_ then hit tab. The two common ones I look at are:

pg_statio_all_indexes
pg_stat_user_indexes

Re: Statistics on index usage

From
Melvin Davidson
Date:


On Tue, Nov 1, 2016 at 10:43 AM, François Battail <francois.battail@sipibox.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@postgresql.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.