Re: index question

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

I will. Thanks for the help/tips!

Cheers
Lucas