Обсуждение: Duplicate Index Creation

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

Results of \d (without all the column defs):

Indexes:
    "input_transaction_snbs_prod_pkey" PRIMARY KEY, btree (id)
    "i1" btree (trans_client)
Check constraints:
    "chk_charge" CHECK (charge_type IS NULL OR charge_type = 'Recurring'::text OR charge_type = 'Usage'::text OR
charge_type= 'Fee'::text OR charge_type = 'Equipment'::text OR charge_type = 'One-t 
ime'::text OR charge_type = 'Reversal'::text OR charge_type = 'Adjustment'::text)

------------------------------------------------------------------------------------------

Results of changing the query to add schemaname and join on relid:

schema_name |       table_name       | index_name | times_used | table_size | index_size | num_writes
-------------+------------------------+------------+------------+------------+------------+------------
 snbs        | input_transaction_snbs | i1         |          0 | 2932 MB    |  304242688 |   10350357
 snbs        | input_transaction_snbs | i1         |          0 | 2932 MB    |  304242688 |   10350357
(2 rows)

----------------------------------------------------------------------------------------------

Results of removing pg_indexes from query and adding pg_get_indexdef:

       table_name       |            index_name            | times_used |
index_def                                           | table_size | index_size | 
 num_writes

------------------------+----------------------------------+------------+-------------------------------------------------------------------------------------------------+------------+------------+
------------
 input_transaction_snbs | i1                               |          0 | CREATE INDEX i1 ON input_transaction_snbs
USINGbtree (trans_client)                            | 2932 MB    |  304242688 | 
   10350357
 input_transaction_snbs | input_transaction_snbs_prod_pkey |          0 | CREATE UNIQUE INDEX
input_transaction_snbs_prod_pkeyON input_transaction_snbs USING btree (id) | 2932 MB    |  232505344 | 
   10350357
(2 rows)

----------------------------------------------------------------------------------------------------

The thing is, if I drop that index and run that originally posted query I get no results.  When I create the index and
runthe query I get the duplicate entries.  Also, if I do not limit that query to input_transaction_snbs and run it
againsteverything, input_transaction_snbs is the only table that shows up with duplicate entries. 

Sam

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, 3 July 2012 11:58 AM
To: Samuel Stearns
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Duplicate Index Creation

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
forpg_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
itlooks 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