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

Re: Duplicate Index Creation

From: Raghavendra <raghavendra(dot)rao(at)enterprisedb(dot)com>
To: Samuel Stearns <SStearns(at)internode(dot)com(dot)au>
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-04 05:00:40
Message-ID: CA+h6Ahi_BXzgTb8NF0SOjKSE+X5nkCoRSczaMxbNTihp2nzjEA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
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/

In response to

Responses

pgsql-admin by date

Next:From: RaghavendraDate: 2012-07-04 05:21:54
Subject: Re: webclient for postgresql
Previous:From: Madhu.LankaDate: 2012-07-04 04:55:21
Subject: webclient for postgresql

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