Re: index question

[Melvin Davidson <melvin6925@xxxxxxxxx>]

Yes, my bad, please add

AND NOT idx.indisunique

AND NOT indisexclusion

to the query that generates the DROPs.

Note that it only generates the SQL statement that does the drop, it does not execute or actaully drop them.

On Mon, May 2, 2016 at 5:29 PM, Julien Rouhaud <julien.rouhaud@xxxxxxxxxx> wrote:

On 02/05/2016 23:02, drum.lucas@xxxxxxxxx wrote:
>
>     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!
>

Be careful, this query discards indexes used for primary key, but at
least unique (indisunique) and exclusion constraint (indisexclusion)
indexes should also be excluded, and also probably indexes used to
cluster tables (indisclustered).

You should also check since when the idsx_scan and other counters are
aggregating before dropping any index. Check
pg_stat_get_db_stat_reset_time(oid), with the oid of the related
database(s).

>
>
> Cheers
> Lucas


--
Julien Rouhaud
http://dalibo.com - http://dalibo.org

--

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