Re: Duplicate Index Creation

[Raghavendra <raghavendra.rao@xxxxxxxxxxxxxxxx>]

On Tue, Jul 3, 2012 at 12:48 PM, Samuel Stearns <SStearns@xxxxxxxxxxxxxxxx> 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