Re: Duplicate Index Creation

From: Raghavendra [mailto:raghavendra.rao@enterprisedb.com]
Sent: Wednesday, 4 July 2012 2:31 PM
To: Samuel Stearns
Cc: Tom Lane; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Duplicate Index Creation

Ok, that returns only the 1 row:

 

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;

 

indexrelid | schema_name |       table_name       | index_name | times_used | times_used | table_size | index_size | num_writes

------------+-------------+------------------------+------------+------------+------------+------------+------------+------------

  727108742 | snbs        | input_transaction_snbs | i1         |         33 |         33 | 2941 MB    |  305160192 |   10381291

(1 row)

 

Out of all the tables in the db why is it that input_transaction_snbs is the only one that returns duplicates from the original query?