From: Tom Lane <tgl@sss.pgh.pa.us>
To: Samuel Stearns <SStearns@internode.com.au>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Duplicate Index Creation
Date: Mon, 02 Jul 2012 22:27:31 -0400
Message-ID: <8355.1341282451@sss.pgh.pa.us> (raw)
In-Reply-To: <CBAC86BE623FDB4E8B6225471691724291A2E5F3@EXCHMBX-ADL6-01.staff.internode.com.au>
References: <CBAC86BE623FDB4E8B6225471691724291A2E5F3@EXCHMBX-ADL6-01.staff.internode.com.au>
Samuel Stearns <SStearns@internode.com.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, SStearns@internode.com.au
Subject: Re: Duplicate Index Creation
In-Reply-To: <8355.1341282451@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox