Skip site navigation (1) Skip section navigation (2)

Re: Duplicate Index Creation

From: Samuel Stearns <SStearns(at)internode(dot)com(dot)au>
To: Raghavendra <raghavendra(dot)rao(at)enterprisedb(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "pgsql-admin(at)postgresql(dot)org"<pgsql-admin(at)postgresql(dot)org>
Subject: Re: Duplicate Index Creation
Date: 2012-07-09 00:00:46
Message-ID: CBAC86BE623FDB4E8B6225471691724291A33F94@EXCHMBX-ADL6-01.staff.internode.com.au (view raw or flat)
Thread:
Lists: pgsql-admin
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/

In response to

pgsql-admin by date

Next:From: Eduardo Sá dos ReisDate: 2012-07-09 13:26:23
Subject: Error: could not send data to client: Connection reset by peer
Previous:From: Craig RingerDate: 2012-07-06 05:28:43
Subject: Re: Re: Delay in completion of aggregation inserts when run in a single commit - PG 9.1.2

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group