Re: Duplicate Index Creation - Mailing list pgsql-admin
| From | Samuel Stearns |
|---|---|
| Subject | Re: Duplicate Index Creation |
| Date | |
| Msg-id | CBAC86BE623FDB4E8B6225471691724291A300A7@EXCHMBX-ADL6-01.staff.internode.com.au Whole thread Raw |
| In response to | Re: Duplicate Index Creation (Raghavendra <raghavendra.rao@enterprisedb.com>) |
| Responses |
Re: Duplicate Index Creation
|
| List | 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.rao@enterprisedb.com]
Sent: Wednesday, 4 July 2012 12:46 AM
To: Samuel Stearns
Cc: Tom Lane; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Duplicate Index Creation
On Tue, Jul 3, 2012 at 12:48 PM, Samuel Stearns <SStearns@internode.com.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
pgsql-admin by date: