Will, Much thanks. Let's keep up the sharing with the community.
On Tue, May 26, 2015 at 11:32 AM, William Dunn <dunn...@gmail.com> wrote: > The query I previously sent was table level. Here is an index level one: > SELECT pg_stat_user_indexes.schemaname, > pg_stat_user_indexes.relname, > pg_stat_user_indexes.indexrelid, > pg_stat_user_indexes.indexrelname, > pg_stat_user_indexes.idx_scan, > pg_stat_user_tables.seq_scan, > (100 * pg_stat_user_indexes.idx_scan / > (pg_stat_user_tables.seq_scan + pg_stat_user_indexes.idx_scan)) AS > perc_idx_used > FROM pg_stat_user_indexes INNER JOIN pg_stat_user_tables ON > pg_stat_user_indexes.relid = pg_stat_user_tables.relid > WHERE pg_relation_size(pg_stat_user_indexes.relid)>(5*8192) > AND NOT ((pg_stat_user_indexes.idx_scan=0 > OR pg_stat_user_indexes.idx_scan=NULL) > AND pg_stat_user_tables.seq_scan=0) > ORDER BY perc_idx_used; > > *Will J. Dunn* > *willjdunn.com <http://willjdunn.com>* > > On Tue, May 26, 2015 at 10:31 AM, William Dunn <dunn...@gmail.com> wrote: > >> Melvin - thanks for sharing. >> >> Here is the query I use which lists the percent of queries against the >> table which use the index ordered by least used first. >> >> The 'pg_relation_size(relid)>(5*8192)' is used to remove any tables that >> would be so small the optimizer would just choose a table scan. >> >> SELECT schemaname, >> relname, >> idx_scan, >> seq_scan, >> (100 * idx_scan / (seq_scan + idx_scan)) AS perc_idx_used >> FROM pg_stat_user_tables >> WHERE pg_relation_size(relid)>(5*8192) >> AND NOT ((idx_scan=0 >> OR idx_scan=NULL) >> AND seq_scan=0) >> ORDER BY perc_idx_used; >> >> *Will J. Dunn* >> *willjdunn.com <http://willjdunn.com>* >> >> On Mon, May 25, 2015 at 12:39 PM, Peter J. Holzer <h...@hjp.at> wrote: >> >>> On 2015-05-25 12:25:01 -0400, Melvin Davidson wrote: >>> > I'm not sure why you are using "pg_stat_user_indexes". >>> >>> Because you did. I didn't change that. >>> >>> > My original query below >>> > uses "pg_stat_all_indexes" and the schema names are joined and it does >>> work. >>> >>> I'm not sure what you mean by "original", but this: >>> >>> > SELECT n.nspname as schema, >>> > i.relname as table, >>> > i.indexrelname as index, >>> > i.idx_scan, >>> > i.idx_tup_read, >>> > i.idx_tup_fetch, >>> > pg_size_pretty(pg_relation_size(quote_ident(n.nspname) || '.' || >>> > quote_ident(i.relname))) AS table_size, >>> > pg_size_pretty(pg_relation_size(quote_ident(n.nspname) || '.' || >>> > quote_ident(i.indexrelname))) AS index_size, >>> > pg_get_indexdef(idx.indexrelid) as idx_definition >>> > FROM pg_stat_all_indexes i >>> > JOIN pg_class c ON (c.oid = i.relid) >>> > JOIN pg_namespace n ON (n.oid = c.relnamespace) >>> > JOIN pg_index idx ON (idx.indexrelid = i.indexrelid ) >>> > WHERE i.idx_scan < 200 >>> > AND NOT idx.indisprimary >>> > AND NOT idx.indisunique >>> > ORDER BY 1, 2, 3; >>> >>> is not the query you posted in your original message. >>> >>> Here is what you posted: >>> >>> > On Mon, May 25, 2015 at 10:41 AM, Peter J. Holzer <hjp-pg...@hjp.at> >>> wrote: >>> > >>> > On 2015-05-22 09:41:57 -0400, Melvin Davidson wrote: >>> > > I'd like to share those queries with the community, as I know >>> there must >>> > be >>> > > others out there with the same problem. >>> > > >>> > > /* useless_indexes.sql */ >>> > > SELECT >>> > > idstat.schemaname AS schema, >>> > > idstat.relname AS table_name, >>> > > indexrelname AS index_name, >>> > > idstat.idx_scan AS times_used, >>> > > >>> pg_size_pretty(pg_relation_size(quote_ident(idstat.schemaname) || >>> > '.' || >>> > > quote_ident(idstat.relname))) AS table_size, >>> > > >>> pg_size_pretty(pg_relation_size(quote_ident(idstat.schemaname) || >>> > '.' || >>> > > quote_ident(indexrelname))) AS index_size, >>> > > n_tup_upd + n_tup_ins + n_tup_del as num_writes, >>> > > indexdef AS definition >>> > > 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 idstat.idx_scan < 200 >>> > > AND indexdef !~* 'unique' >>> > > ORDER BY idstat.schemaname, >>> > > idstat.relname, >>> > > indexrelname; >>> >>> -- >>> _ | Peter J. Holzer | I want to forget all about both belts and >>> |_|_) | | suspenders; instead, I want to buy pants >>> | | | h...@hjp.at | that actually fit. >>> __/ | http://www.hjp.at/ | -- http://noncombatant.org/ >>> >> >> > -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
