Re: Duplicate Index Creation

On Tue, Jul 3, 2012 at 12:48 PM, Samuel Stearns
<SStearns(at)internode(dot)com(dot)au>wrote:

> Before and after analyze:****
>
> ** **
>
> select schemaname,relid,indexrelid,relname,indexrelname from****
>
> pg_stat_all_indexes where relname='input_transaction_snbs';****
>
> ** **
>
> schemaname | relid | indexrelid | relname |
> indexrelname****
>
>
> ------------+-----------+------------+------------------------+----------------------------------
> ****
>
> snbs | 535026046 | 616672654 | input_transaction_snbs | i1****
>
> snbs | 535026046 | 616576519 | input_transaction_snbs |
> input_transaction_snbs_prod_pkey****
>
> (2 rows)****
>
> ** **
>
>
> -------------------------------------------------------------------------------------------------------------------------------
> ****
>
> **
>

Seems only one "i1" index here. Because pg_stat_all_indexes view is based
on pg_class,pg_index and pg_namespace catalog tables.

SELECT idstat.schemaname AS schema_name,****
>
> idstat.relname AS table_name,****
>
> idstat.indexrelname AS index_name,****
>
> idstat.idx_scan AS times_used,****
>
> idstat.idx_scan AS times_used,****
>
> pg_size_pretty(pg_relation_size(idstat.relid)) AS table_size,****
>
> pg_relation_size(indexrelid) AS index_size,****
>
> n_tup_upd + n_tup_ins + n_tup_del as num_writes****
>
> FROM pg_stat_user_indexes AS idstat****
>
> JOIN pg_indexes ON indexrelname = indexname****
>
> JOIN pg_stat_user_tables AS tabstat ON idstat.relid = tabstat.relid****
>
> WHERE idstat.relname = 'input_transaction_snbs'****
>
> AND indexdef !~* 'unique'****
>
> ORDER BY index_size desc;****
>
> ** **
>
> schema_name | table_name | index_name | times_used |
> table_size | index_size | num_writes****
>
>
> -------------+------------------------+------------+------------+------------+------------+------------
> ****
>
> snbs | input_transaction_snbs | i1 | 0 | 2932
> MB | 304242688 | 10350357****
>
> snbs | input_transaction_snbs | i1 | 0 | 2932
> MB | 304242688 | 10350357****
>
> (2 rows)****
>
> **
>

Ok. A small correction to above query, added schema filter clause in JOIN
and indexrelid column. Please try.

SELECT idstat.indexrelid as indexrelid,
idstat.schemaname AS schema_name,
idstat.relname AS table_name,
idstat.indexrelname AS index_name,
idstat.idx_scan AS times_used,
idstat.idx_scan AS times_used,
pg_size_pretty(pg_relation_size(idstat.relid)) AS table_size,
pg_relation_size(indexrelid) AS index_size,
n_tup_upd + n_tup_ins + n_tup_del as num_writes
FROM pg_stat_user_indexes AS idstat
JOIN pg_indexes as pi ON indexrelname = indexname and idstat.schemaname =
pi.schemaname
JOIN pg_stat_user_tables AS tabstat ON idstat.relid = tabstat.relid
WHERE idstat.relname = 'input_transaction_snbs'
AND indexdef !~* 'unique'
ORDER BY index_size desc;

--Raghav