Samuel Stearns <SStearns@xxxxxxxxxxxxxxxx> 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 -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
