Re: Duplicate Index Creation

Raghavendra and Tom,

Thanks for your help and time on this. I found the problem. There was an index with the same name in another schema. I discovered it just by sheer digging around in the db using different queries. Not sure why it returned the duplicate index in the original query even 'though I had it limited to input_transaction_snbs.

Anyway, all good now. Thanks again.

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

On Wed, Jul 4, 2012 at 7:09 AM, Samuel Stearns <SStearns(at)internode(dot)com(dot)au<mailto:SStearns(at)internode(dot)com(dot)au>> 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/