Skip site navigation (1) Skip section navigation (2)

Duplicate Index Creation

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 (view raw or flat)
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





Responses

pgsql-admin by date

Next:From: RaghavendraDate: 2012-07-03 02:14:38
Subject: Re: Duplicate Index Creation
Previous:From: Fujii MasaoDate: 2012-07-02 18:17:16
Subject: Re: [ADMIN] pg_basebackup blocking all queries with horrible performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group