Duplicate Index Creation

[Samuel Stearns <SStearns@xxxxxxxxxxxxxxxx>]

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 2^nd 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