|
Ok, that returns only the 1 row: 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; indexrelid | schema_name | table_name | index_name | times_used | times_used | table_size | index_size | num_writes ------------+-------------+------------------------+------------+------------+------------+------------+------------+------------ 727108742 | snbs | input_transaction_snbs | i1 | 33 | 33 | 2941 MB | 305160192 | 10381291 (1 row) Out of all the tables in the db why is it that input_transaction_snbs is the only one that returns duplicates from the original query? Sam From: Raghavendra [mailto:raghavendra.rao@xxxxxxxxxxxxxxxx]
On Tue, Jul 3, 2012 at 12:48 PM, Samuel Stearns <SStearns@xxxxxxxxxxxxxxxx> wrote:
Seems only one "i1" index here. Because pg_stat_all_indexes view is based on pg_class,pg_index and pg_namespace catalog tables.
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 |
