Re: Duplicate Index Creation

On Wed, Jul 4, 2012 at 7:09 AM, Samuel Stearns <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/