Re: index question

On Mon, May 2, 2016 at 4:08 PM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Mon, May 2, 2016 at 12:56 PM, drum(dot)lucas(at)gmail(dot)com <
> drum(dot)lucas(at)gmail(dot)com> wrote:
>
>>
>>>>
>>> ​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.​
>>>
>>>
>>> So only those with* 0 size*, should be deleted? Is that you're saying?
>>
>
> I'm not offering advice as to when to delete or not delete any particular
> index.
>
>
>> Can you be more clear please?
>>
>>
> Probably not :)
>
> ​You cannot make an inference about an index's usage by looking at its
> size.​ Similarly, a seldom used but large index is not necessarily one you
> want to remove if doing so causes a once-a-month process that usually take
> seconds or minutes to now take hours.
>
> David J.
>
> ​
>
>
Generically speaking, if the total of dx_scan + idx_tup_read +
idx_tup_fetch are 0, then it is an _indication_ that those indexes should
be dropped.
You should also consider how long those indexes have existed and how often
queries are executed.

A good practice would be to save the SQL to recreate the indexes before you
drop any. In that way, if you notice a degradation in performance, you can
just rebuild
You can use the following query to do that, but you might want to edit and
add the CONCURRENT option.

SELECT pg_get_indexdef(idx.indexrelid) || ';'
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 NOT idx.indisprimary
AND NOT idx.indisunique
AND i.relname NOT LIKE 'pg_%'
AND i.idx_scan + idx_tup_read + idx_tup_fetch = 0
ORDER BY n.nspname,
i.relname;

The following query generates the drop statements.

SELECT 'DROP INDEX CONCURRENTLY IF EXISTS "' || quote_ident(n.nspname) ||
'"' || '.' || '"' || quote_ident(i.indexrelname) || '"' ||';'
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 NOT idx.indisprimary
AND i.relname NOT LIKE 'pg_%'
AND i.idx_scan + idx_tup_read + idx_tup_fetch = 0
ORDER BY i.indexrelname;

I would not place any concern on the size of the index. That is just what
is needed to keep track of all associated rows.
Once you drop the indexes you determine are not needed, you will gain back
the space that they use up.

Please stay in touch and let me know how it goes.

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.