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