pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedDuplicate Index Creation
11+ messages / 3 participants
[nested] [flat]
* Duplicate Index Creation
@ 2012-07-03 01:43 Samuel Stearns <SStearns@internode.com.au>
2012-07-03 02:14 ` Re: Duplicate Index Creation Raghavendra <raghavendra.rao@enterprisedb.com>
2012-07-03 02:27 ` Re: Duplicate Index Creation Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 11+ messages in thread
From: Samuel Stearns @ 2012-07-03 01:43 UTC (permalink / raw)
To: pgsql-admin
Howdy,
Environment:
Postgres 8.4.7 64-bit
Solaris 10
I create an index as:
CREATE INDEX i1
ON input_transaction_snbs
USING btree
(trans_client);
which is creating a 2nd duplicate index in error as this query shows:
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;
Results of the above SELECT:
table_name | index_name | times_used | table_size | index_size | num_writes
------------------------+------------+------------+------------+------------+------------
input_transaction_snbs | i1 | 0 | 2932 MB | 304242688 | 10350357
input_transaction_snbs | i1 | 0 | 2932 MB | 304242688 | 10350357
(2 rows)
This is causing poor query performance. Any ideas?
Thank you,
Samuel Stearns
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Duplicate Index Creation
2012-07-03 01:43 Duplicate Index Creation Samuel Stearns <SStearns@internode.com.au>
@ 2012-07-03 02:14 ` Raghavendra <raghavendra.rao@enterprisedb.com>
1 sibling, 0 replies; 11+ messages in thread
From: Raghavendra @ 2012-07-03 02:14 UTC (permalink / raw)
To: Samuel Stearns <SStearns@internode.com.au>; +Cc: pgsql-admin
>
>
> 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/
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Duplicate Index Creation
2012-07-03 01:43 Duplicate Index Creation Samuel Stearns <SStearns@internode.com.au>
@ 2012-07-03 02:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-07-03 03:55 ` Re: Duplicate Index Creation Samuel Stearns <SStearns@internode.com.au>
1 sibling, 1 reply; 11+ messages in thread
From: Tom Lane @ 2012-07-03 02:27 UTC (permalink / raw)
To: Samuel Stearns <SStearns@internode.com.au>; +Cc: pgsql-admin
Samuel Stearns <SStearns@internode.com.au> writes:
> I create an index as: ...
> which is creating a 2nd duplicate index in error as this query shows:
> 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Duplicate Index Creation
2012-07-03 01:43 Duplicate Index Creation Samuel Stearns <SStearns@internode.com.au>
2012-07-03 02:27 ` Re: Duplicate Index Creation Tom Lane <tgl@sss.pgh.pa.us>
@ 2012-07-03 03:55 ` Samuel Stearns <SStearns@internode.com.au>
2012-07-03 04:49 ` Re: Duplicate Index Creation Raghavendra <raghavendra.rao@enterprisedb.com>
0 siblings, 1 reply; 11+ messages in thread
From: Samuel Stearns @ 2012-07-03 03:55 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
Results of \d (without all the column defs):
Indexes:
"input_transaction_snbs_prod_pkey" PRIMARY KEY, btree (id)
"i1" btree (trans_client)
Check constraints:
"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)
------------------------------------------------------------------------------------------
Results of changing the query to add schemaname and join on relid:
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)
----------------------------------------------------------------------------------------------
Results of removing pg_indexes from query and adding pg_get_indexdef:
table_name | index_name | times_used | index_def | table_size | index_size |
num_writes
------------------------+----------------------------------+------------+-------------------------------------------------------------------------------------------------+------------+------------+
------------
input_transaction_snbs | i1 | 0 | CREATE INDEX i1 ON input_transaction_snbs USING btree (trans_client) | 2932 MB | 304242688 |
10350357
input_transaction_snbs | input_transaction_snbs_prod_pkey | 0 | CREATE UNIQUE INDEX input_transaction_snbs_prod_pkey ON input_transaction_snbs USING btree (id) | 2932 MB | 232505344 |
10350357
(2 rows)
----------------------------------------------------------------------------------------------------
The thing is, if I drop that index and run that originally posted query I get no results. When I create the index and run the query I get the duplicate entries. Also, if I do not limit that query to input_transaction_snbs and run it against everything, input_transaction_snbs is the only table that shows up with duplicate entries.
Sam
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, 3 July 2012 11:58 AM
To: Samuel Stearns
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Duplicate Index Creation
Samuel Stearns <SStearns@internode.com.au> writes:
> I create an index as: ...
> which is creating a 2nd duplicate index in error as this query shows:
> 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Duplicate Index Creation
2012-07-03 01:43 Duplicate Index Creation Samuel Stearns <SStearns@internode.com.au>
2012-07-03 02:27 ` Re: Duplicate Index Creation Tom Lane <tgl@sss.pgh.pa.us>
2012-07-03 03:55 ` Re: Duplicate Index Creation Samuel Stearns <SStearns@internode.com.au>
@ 2012-07-03 04:49 ` Raghavendra <raghavendra.rao@enterprisedb.com>
2012-07-03 05:04 ` Re: Duplicate Index Creation Raghavendra <raghavendra.rao@enterprisedb.com>
0 siblings, 1 reply; 11+ messages in thread
From: Raghavendra @ 2012-07-03 04:49 UTC (permalink / raw)
To: Samuel Stearns <SStearns@internode.com.au>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
On Tue, Jul 3, 2012 at 9:25 AM, Samuel Stearns <SStearns@internode.com.au>wrote:
> Results of \d (without all the column defs):
>
> Indexes:
> "input_transaction_snbs_prod_pkey" PRIMARY KEY, btree (id)
> "i1" btree (trans_client)
> Check constraints:
> "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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Duplicate Index Creation
2012-07-03 01:43 Duplicate Index Creation Samuel Stearns <SStearns@internode.com.au>
2012-07-03 02:27 ` Re: Duplicate Index Creation Tom Lane <tgl@sss.pgh.pa.us>
2012-07-03 03:55 ` Re: Duplicate Index Creation Samuel Stearns <SStearns@internode.com.au>
2012-07-03 04:49 ` Re: Duplicate Index Creation Raghavendra <raghavendra.rao@enterprisedb.com>
@ 2012-07-03 05:04 ` Raghavendra <raghavendra.rao@enterprisedb.com>
2012-07-03 07:18 ` Re: Duplicate Index Creation Samuel Stearns <SStearns@internode.com.au>
0 siblings, 1 reply; 11+ messages in thread
From: Raghavendra @ 2012-07-03 05:04 UTC (permalink / raw)
To: Samuel Stearns <SStearns@internode.com.au>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
On Tue, Jul 3, 2012 at 10:19 AM, Raghavendra <
raghavendra.rao@enterprisedb.com> wrote:
>
> On Tue, Jul 3, 2012 at 9:25 AM, Samuel Stearns <SStearns@internode.com.au>wrote:
>
>> Results of \d (without all the column defs):
>>
>> Indexes:
>> "input_transaction_snbs_prod_pkey" PRIMARY KEY, btree (id)
>> "i1" btree (trans_client)
>> Check constraints:
>> "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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Duplicate Index Creation
2012-07-03 01:43 Duplicate Index Creation Samuel Stearns <SStearns@internode.com.au>
2012-07-03 02:27 ` Re: Duplicate Index Creation Tom Lane <tgl@sss.pgh.pa.us>
2012-07-03 03:55 ` Re: Duplicate Index Creation Samuel Stearns <SStearns@internode.com.au>
2012-07-03 04:49 ` Re: Duplicate Index Creation Raghavendra <raghavendra.rao@enterprisedb.com>
2012-07-03 05:04 ` Re: Duplicate Index Creation Raghavendra <raghavendra.rao@enterprisedb.com>
@ 2012-07-03 07:18 ` Samuel Stearns <SStearns@internode.com.au>
2012-07-03 15:15 ` Re: Duplicate Index Creation Raghavendra <raghavendra.rao@enterprisedb.com>
0 siblings, 1 reply; 11+ messages in thread
From: Samuel Stearns @ 2012-07-03 07:18 UTC (permalink / raw)
To: Raghavendra <raghavendra.rao@enterprisedb.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
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)
-------------------------------------------------------------------------------------------------------------------------------
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)
Sam
From: Raghavendra [mailto:raghavendra.rao@enterprisedb.com]
Sent: Tuesday, 3 July 2012 2:34 PM
To: Samuel Stearns
Cc: Tom Lane; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Duplicate Index Creation
On Tue, Jul 3, 2012 at 10:19 AM, Raghavendra <raghavendra.rao@enterprisedb.com<mailto:raghavendra.rao@enterprisedb.com>> wrote:
On Tue, Jul 3, 2012 at 9:25 AM, Samuel Stearns <SStearns@internode.com.au<mailto:SStearns@internode.com.au>> wrote:
Results of \d (without all the column defs):
Indexes:
"input_transaction_snbs_prod_pkey" PRIMARY KEY, btree (id)
"i1" btree (trans_client)
Check constraints:
"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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Duplicate Index Creation
2012-07-03 01:43 Duplicate Index Creation Samuel Stearns <SStearns@internode.com.au>
2012-07-03 02:27 ` Re: Duplicate Index Creation Tom Lane <tgl@sss.pgh.pa.us>
2012-07-03 03:55 ` Re: Duplicate Index Creation Samuel Stearns <SStearns@internode.com.au>
2012-07-03 04:49 ` Re: Duplicate Index Creation Raghavendra <raghavendra.rao@enterprisedb.com>
2012-07-03 05:04 ` Re: Duplicate Index Creation Raghavendra <raghavendra.rao@enterprisedb.com>
2012-07-03 07:18 ` Re: Duplicate Index Creation Samuel Stearns <SStearns@internode.com.au>
@ 2012-07-03 15:15 ` Raghavendra <raghavendra.rao@enterprisedb.com>
2012-07-04 01:39 ` Re: Duplicate Index Creation Samuel Stearns <SStearns@internode.com.au>
0 siblings, 1 reply; 11+ messages in thread
From: Raghavendra @ 2012-07-03 15:15 UTC (permalink / raw)
To: Samuel Stearns <SStearns@internode.com.au>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Duplicate Index Creation
2012-07-03 01:43 Duplicate Index Creation Samuel Stearns <SStearns@internode.com.au>
2012-07-03 02:27 ` Re: Duplicate Index Creation Tom Lane <tgl@sss.pgh.pa.us>
2012-07-03 03:55 ` Re: Duplicate Index Creation Samuel Stearns <SStearns@internode.com.au>
2012-07-03 04:49 ` Re: Duplicate Index Creation Raghavendra <raghavendra.rao@enterprisedb.com>
2012-07-03 05:04 ` Re: Duplicate Index Creation Raghavendra <raghavendra.rao@enterprisedb.com>
2012-07-03 07:18 ` Re: Duplicate Index Creation Samuel Stearns <SStearns@internode.com.au>
2012-07-03 15:15 ` Re: Duplicate Index Creation Raghavendra <raghavendra.rao@enterprisedb.com>
@ 2012-07-04 01:39 ` Samuel Stearns <SStearns@internode.com.au>
2012-07-04 05:00 ` Re: Duplicate Index Creation Raghavendra <raghavendra.rao@enterprisedb.com>
0 siblings, 1 reply; 11+ messages in thread
From: Samuel Stearns @ 2012-07-04 01:39 UTC (permalink / raw)
To: Raghavendra <raghavendra.rao@enterprisedb.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; 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<mailto: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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Duplicate Index Creation
2012-07-03 01:43 Duplicate Index Creation Samuel Stearns <SStearns@internode.com.au>
2012-07-03 02:27 ` Re: Duplicate Index Creation Tom Lane <tgl@sss.pgh.pa.us>
2012-07-03 03:55 ` Re: Duplicate Index Creation Samuel Stearns <SStearns@internode.com.au>
2012-07-03 04:49 ` Re: Duplicate Index Creation Raghavendra <raghavendra.rao@enterprisedb.com>
2012-07-03 05:04 ` Re: Duplicate Index Creation Raghavendra <raghavendra.rao@enterprisedb.com>
2012-07-03 07:18 ` Re: Duplicate Index Creation Samuel Stearns <SStearns@internode.com.au>
2012-07-03 15:15 ` Re: Duplicate Index Creation Raghavendra <raghavendra.rao@enterprisedb.com>
2012-07-04 01:39 ` Re: Duplicate Index Creation Samuel Stearns <SStearns@internode.com.au>
@ 2012-07-04 05:00 ` Raghavendra <raghavendra.rao@enterprisedb.com>
2012-07-09 00:00 ` Re: Duplicate Index Creation Samuel Stearns <SStearns@internode.com.au>
0 siblings, 1 reply; 11+ messages in thread
From: Raghavendra @ 2012-07-04 05:00 UTC (permalink / raw)
To: Samuel Stearns <SStearns@internode.com.au>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
On Wed, Jul 4, 2012 at 7:09 AM, Samuel Stearns <SStearns@internode.com.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/
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Duplicate Index Creation
2012-07-03 01:43 Duplicate Index Creation Samuel Stearns <SStearns@internode.com.au>
2012-07-03 02:27 ` Re: Duplicate Index Creation Tom Lane <tgl@sss.pgh.pa.us>
2012-07-03 03:55 ` Re: Duplicate Index Creation Samuel Stearns <SStearns@internode.com.au>
2012-07-03 04:49 ` Re: Duplicate Index Creation Raghavendra <raghavendra.rao@enterprisedb.com>
2012-07-03 05:04 ` Re: Duplicate Index Creation Raghavendra <raghavendra.rao@enterprisedb.com>
2012-07-03 07:18 ` Re: Duplicate Index Creation Samuel Stearns <SStearns@internode.com.au>
2012-07-03 15:15 ` Re: Duplicate Index Creation Raghavendra <raghavendra.rao@enterprisedb.com>
2012-07-04 01:39 ` Re: Duplicate Index Creation Samuel Stearns <SStearns@internode.com.au>
2012-07-04 05:00 ` Re: Duplicate Index Creation Raghavendra <raghavendra.rao@enterprisedb.com>
@ 2012-07-09 00:00 ` Samuel Stearns <SStearns@internode.com.au>
0 siblings, 0 replies; 11+ messages in thread
From: Samuel Stearns @ 2012-07-09 00:00 UTC (permalink / raw)
To: Raghavendra <raghavendra.rao@enterprisedb.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; 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.rao@enterprisedb.com]
Sent: Wednesday, 4 July 2012 2:31 PM
To: Samuel Stearns
Cc: Tom Lane; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Duplicate Index Creation
On Wed, Jul 4, 2012 at 7:09 AM, Samuel Stearns <SStearns@internode.com.au<mailto:SStearns@internode.com.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/
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2012-07-09 00:00 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-07-03 01:43 Duplicate Index Creation Samuel Stearns <SStearns@internode.com.au>
2012-07-03 02:14 ` Raghavendra <raghavendra.rao@enterprisedb.com>
2012-07-03 02:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-07-03 03:55 ` Samuel Stearns <SStearns@internode.com.au>
2012-07-03 04:49 ` Raghavendra <raghavendra.rao@enterprisedb.com>
2012-07-03 05:04 ` Raghavendra <raghavendra.rao@enterprisedb.com>
2012-07-03 07:18 ` Samuel Stearns <SStearns@internode.com.au>
2012-07-03 15:15 ` Raghavendra <raghavendra.rao@enterprisedb.com>
2012-07-04 01:39 ` Samuel Stearns <SStearns@internode.com.au>
2012-07-04 05:00 ` Raghavendra <raghavendra.rao@enterprisedb.com>
2012-07-09 00:00 ` Samuel Stearns <SStearns@internode.com.au>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox