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....@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....@enterprisedb.com<mailto:raghavendra....@enterprisedb.com>> wrote: On Tue, Jul 3, 2012 at 9:25 AM, Samuel Stearns <sstea...@internode.com.au<mailto:sstea...@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