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: 8355.1341282451@sss.pgh.pa.us (view raw or flat)
Thread:
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
pg_get_indexdef(indexrelid).

			regards, tom lane

In response to

Responses

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-2014 The PostgreSQL Global Development Group