Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id AEC26D0B84 for ; Mon, 2 Jul 2012 23:27:50 -0300 (ADT) Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SlsqD-0003Et-Vm for pgsql-admin@postgresql.org; Tue, 03 Jul 2012 02:27:50 +0000 Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.5/8.14.5) with ESMTP id q632RVSK008356; Mon, 2 Jul 2012 22:27:31 -0400 (EDT) From: Tom Lane To: Samuel Stearns cc: "pgsql-admin@postgresql.org" Subject: Re: Duplicate Index Creation In-reply-to: References: Comments: In-reply-to Samuel Stearns message dated "Tue, 03 Jul 2012 01:43:46 -0000" Date: Mon, 02 Jul 2012 22:27:31 -0400 Message-ID: <8355.1341282451@sss.pgh.pa.us> X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201207/3 X-Sequence-Number: 39720 Samuel Stearns 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