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-04 01:39:39
Message-ID: CBAC86BE623FDB4E8B6225471691724291A300A7@EXCHMBX-ADL6-01.staff.internode.com.au (view raw or flat)
Thread:
Lists: pgsql-admin
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)

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?

Sam

From: Raghavendra [mailto:raghavendra(dot)rao(at)enterprisedb(dot)com]
Sent: Wednesday, 4 July 2012 12:46 AM
To: Samuel Stearns
Cc: Tom Lane; pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] Duplicate Index Creation

On Tue, Jul 3, 2012 at 12:48 PM, Samuel Stearns <SStearns(at)internode(dot)com(dot)au<mailto:SStearns(at)internode(dot)com(dot)au>> wrote:
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

In response to

Responses

pgsql-admin by date

Next:From: Madhu.LankaDate: 2012-07-04 04:55:21
Subject: webclient for postgresql
Previous:From: Greg SmithDate: 2012-07-03 16:40:06
Subject: Re: replication recovery/startup question

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