From: | Samuel Stearns <SStearns(at)internode(dot)com(dot)au> |
---|---|
To: | "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org> |
Subject: | Duplicate Index Creation |
Date: | 2012-07-03 01:43:46 |
Message-ID: | CBAC86BE623FDB4E8B6225471691724291A2E5F3@EXCHMBX-ADL6-01.staff.internode.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Raghavendra | 2012-07-03 02:14:38 | Re: Duplicate Index Creation |
Previous Message | Fujii Masao | 2012-07-02 18:17:16 | Re: [ADMIN] pg_basebackup blocking all queries with horrible performance |