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-03 07:18:11 |
Message-ID: | CBAC86BE623FDB4E8B6225471691724291A2FBB8@EXCHMBX-ADL6-01.staff.internode.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(dot)rao(at)enterprisedb(dot)com]
Sent: Tuesday, 3 July 2012 2:34 PM
To: Samuel Stearns
Cc: Tom Lane; pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] Duplicate Index Creation
On Tue, Jul 3, 2012 at 10:19 AM, Raghavendra <raghavendra(dot)rao(at)enterprisedb(dot)com<mailto:raghavendra(dot)rao(at)enterprisedb(dot)com>> wrote:
On Tue, Jul 3, 2012 at 9:25 AM, Samuel Stearns <SStearns(at)internode(dot)com(dot)au<mailto:SStearns(at)internode(dot)com(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Raghavendra | 2012-07-03 15:15:37 | Re: Duplicate Index Creation |
Previous Message | Raghavendra | 2012-07-03 05:04:08 | Re: Duplicate Index Creation |