On Mon, May 2, 2016 at 12:44 PM, drum.lu...@gmail.com <drum.lu...@gmail.com> wrote:
> Melvin, that Query you sent is very interesting.. > > 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; > > > > I've found more then 100 indexes that the columns: > > "idx_scan", "idx_tup_read" and "idx_tup_fetch" are 0. > So, it's safe to say that they are not being used, is that right? > > But some indexes have almost 100GB on the size column. This means they are > not being used now, but they could be used in the past? > > Index size and index usage are unrelated. Modifications to the index to keep it in sync with the table do not count as "usage" - only reading it for where clause use counts. David J.
