[ADMIN] Duplicate Index Creation

[Raghavendra]

which is creating a 2nd duplicate index in error as this query shows:****
**

Hmm,,, that's strange.. Can you update with the output of the below command.

\d input_transaction_snbs

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

[Tom Lane]

I create an index as: ...
SELECT idstat.relname AS table_name,
idstat.indexrelname AS index_name,
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 ON indexrelname = indexname
JOIN pg_stat_user_tables AS tabstat ON idstat.relname = tabstat.relname
WHERE indexdef !~* 'unique'
AND idstat.relname = 'input_transaction_snbs'
ORDER BY index_size desc;

I don't think that query proves much at all: indexname is not a unique
key for pg_indexes, nor is relname a unique key for pg_stat_user_tables,
so most likely you're getting an unrelated hit in one or the other of
those views.

Personally I'd rely on the table OID columns (relid) to join the two
pg_stat views. If you want to join to pg_indexes it looks like you
need to compare all of schemaname, tablename, indexname to be safe.
But really you could skip that join and just use
pg_get_indexdef(indexrelid).

regards, tom lane

[Raghavendra]

"input_transaction_snbs_prod_pkey" PRIMARY KEY, btree (id)
"i1" btree (trans_client)
"chk_charge" CHECK (charge_type IS NULL OR charge_type =
'Recurring'::text OR charge_type = 'Usage'::text OR charge_type =
'Fee'::text OR charge_type = 'Equipment'::text OR charge_type = 'One-t
ime'::text OR charge_type = 'Reversal'::text OR charge_type =
'Adjustment'::text)
Thanks. I was checking any INVALID indexes on the table. Its fine, can try

below query.

select schemaname,relid,indexrelid,relname,indexrelname from
pg_stat_all_indexes where relname='i1';

Also, try to ANALYZE the database and retry the queries. Its just to
confirm that query results are getting from updated catalogs.

--Raghav

[Raghavendra]

On Tue, Jul 3, 2012 at 10:19 AM, Raghavendra <

"input_transaction_snbs_prod_pkey" PRIMARY KEY, btree (id)
"i1" btree (trans_client)
"chk_charge" CHECK (charge_type IS NULL OR charge_type =
'Recurring'::text OR charge_type = 'Usage'::text OR charge_type =
'Fee'::text OR charge_type = 'Equipment'::text OR charge_type = 'One-t
ime'::text OR charge_type = 'Reversal'::text OR charge_type =
'Adjustment'::text)
Thanks. I was checking any INVALID indexes on the table. Its fine, can

try below query.
select schemaname,relid,indexrelid,relname,indexrelname from
pg_stat_all_indexes where relname='i1';
Also, try to ANALYZE the database and retry the queries. Its just to
confirm that query results are getting from updated catalogs.
--Raghav

Opps... correction in my query WHERE clause, it should be pointing to
relname not index -- >where relname=' input_transaction_snbs' <--

--Raghav

[Raghavendra]

On Tue, Jul 3, 2012 at 12:48 PM, Samuel Stearns

Before and after analyze:****
** **
select schemaname,relid,indexrelid,relname,indexrelname from****
pg_stat_all_indexes where relname='input_transaction_snbs';****
** **
schemaname | relid | indexrelid | relname |
indexrelname****
------------+-----------+------------+------------------------+----------------------------------
****
snbs | 535026046 | 616672654 | input_transaction_snbs | i1****
snbs | 535026046 | 616576519 | input_transaction_snbs |
input_transaction_snbs_prod_pkey****
(2 rows)****
** **
-------------------------------------------------------------------------------------------------------------------------------
****
**

Seems only one "i1" index here. Because pg_stat_all_indexes view is based
on pg_class,pg_index and pg_namespace catalog tables.

SELECT 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 ON indexrelname = indexname****
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;****
** **
schema_name | table_name | index_name | times_used |
table_size | index_size | num_writes****
-------------+------------------------+------------+------------+------------+------------+------------
****
snbs | input_transaction_snbs | i1 | 0 | 2932
MB | 304242688 | 10350357****
snbs | input_transaction_snbs | i1 | 0 | 2932
MB | 304242688 | 10350357****
(2 rows)****
**

Ok. A small correction to above query, added schema filter clause in JOIN
and indexrelid column. Please try.

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;

--Raghav

[Raghavendra]

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/