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

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
Message-ID: (view raw, whole thread or download thread 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


pgsql-admin by date

Next:From: Samuel StearnsDate: 2012-07-03 03:55:52
Subject: Re: Duplicate Index Creation
Previous:From: RaghavendraDate: 2012-07-03 02:14:38
Subject: Re: Duplicate Index Creation

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