Re: index question

On Mon, May 2, 2016 at 12:44 PM, drum(dot)lucas(at)gmail(dot)com <drum(dot)lucas(at)gmail(dot)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.
​