Re: Duplicate Index Creation

[Raghavendra <raghavendra.rao@xxxxxxxxxxxxxxxx>]

On Wed, Jul 4, 2012 at 7:09 AM, Samuel Stearns <SStearns@xxxxxxxxxxxxxxxx> wrote:

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)

 

This is good.. My guess is correct, there is no duplicate indexes. 

 

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?

 

In your original query, the First join is broken, which won't come out of uniqueness with only comparing on relname=relname, It should also need to use Schemaname=schemaname, and second join is with relid=relid (As Tom Said) its very unique. First join was broken and by adding schemaname its now correct.

Coming *WHY*. if you see the indexrelid's of both queries, they are different. 

schemaname |   relid   | indexrelid |        relname         |           indexrelname

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

snbs       | 535026046 |  616672654 | input_transaction_snbs | i1

And 

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

Am not sure, how often you do maintenance on database like VACUUM, REINDEX etc., because all these activities will keep update the pg_catalogs. Presently, in mind I can only think reindexing the system catalog would be right option "reinidexdb -s". 

Other's might have good options in fixing this, you should wait for another suggestion.

---

Regards,

Raghavendra

EnterpriseDB Corporation

Blog: http://raghavt.blogspot.com/