Re: Duplicate Index Creation

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Samuel Stearns <SStearns(at)internode(dot)com(dot)au>
Cc: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: Re: Duplicate Index Creation
Date: 2012-07-03 02:27:31
Views: Raw Message | Whole Thread | Download mbox
Lists: pgsql-admin

Samuel Stearns <SStearns(at)internode(dot)com(dot)au> writes:
> I create an index as: ...
> 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;

I don't think that query proves much at all: indexname is not a unique
key for pg_indexes, nor is relname a unique key for pg_stat_user_tables,
so most likely you're getting an unrelated hit in one or the other of
those views.

Personally I'd rely on the table OID columns (relid) to join the two
pg_stat views. If you want to join to pg_indexes it looks like you
need to compare all of schemaname, tablename, indexname to be safe.
But really you could skip that join and just use

regards, tom lane

In response to


Browse pgsql-admin by date

  From Date Subject
Next Message Samuel Stearns 2012-07-03 03:55:52 Re: Duplicate Index Creation
Previous Message Raghavendra 2012-07-03 02:14:38 Re: Duplicate Index Creation