On Tue, Jul 3, 2012 at 12:48 PM, Samuel Stearns <sstea...@internode.com.au>wrote:
> Before and after analyze:**** > > ** ** > > select schemaname,relid,indexrelid,relname,indexrelname from**** > > pg_stat_all_indexes where relname='input_transaction_snbs';**** > > ** ** > > schemaname | relid | indexrelid | relname | > indexrelname**** > > > ------------+-----------+------------+------------------------+---------------------------------- > **** > > snbs | 535026046 | 616672654 | input_transaction_snbs | i1**** > > snbs | 535026046 | 616576519 | input_transaction_snbs | > input_transaction_snbs_prod_pkey**** > > (2 rows)**** > > ** ** > > > ------------------------------------------------------------------------------------------------------------------------------- > **** > > ** > Seems only one "i1" index here. Because pg_stat_all_indexes view is based on pg_class,pg_index and pg_namespace catalog tables. SELECT idstat.schemaname AS schema_name,**** > > idstat.relname AS table_name,**** > > idstat.indexrelname AS index_name,**** > > idstat.idx_scan AS times_used,**** > > 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.relid = tabstat.relid**** > > WHERE idstat.relname = 'input_transaction_snbs'**** > > AND indexdef !~* 'unique'**** > > ORDER BY index_size desc;**** > > ** ** > > 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)**** > > ** > Ok. A small correction to above query, added schema filter clause in JOIN and indexrelid column. Please try. SELECT idstat.indexrelid as indexrelid, idstat.schemaname AS schema_name, idstat.relname AS table_name, idstat.indexrelname AS index_name, idstat.idx_scan AS times_used, 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 as pi ON indexrelname = indexname and idstat.schemaname = pi.schemaname JOIN pg_stat_user_tables AS tabstat ON idstat.relid = tabstat.relid WHERE idstat.relname = 'input_transaction_snbs' AND indexdef !~* 'unique' ORDER BY index_size desc; --Raghav