2017-02-25 17:53 GMT+13:00 Patrick B <patrickbake...@gmail.com>: > > > 2017-02-23 11:46 GMT+13:00 Jeff Janes <jeff.ja...@gmail.com>: > >> On Tue, Feb 21, 2017 at 1:44 PM, Patrick B <patrickbake...@gmail.com> >> wrote: >> >>> Hi guys, >>> >>> I've got a lot of bloat indexes on my 4TB database. >>> >>> Let's take this example: >>> >>> Table: seg >>> Index: ix_filter_by_tree >>> Times_used: 1018082183 >>> Table_size: 18 GB -- wrong. The table is mostly on pg_toast table. Its >>> real size is 2TB >>> Index_size: 17 GB >>> Num_writes 16245023 >>> Index definition: CREATE INDEX ix_filter_by_tree ON seg USING btree >>> (full_path varchar_pattern_ops) WHERE (full_path IS NOT NULL) >>> >>> >> What is this from? If you think the table size reported should include >> toast, then change it to do that, or request the author of whatever-this-is >> to make that change. >> >> What indication is there that the index is bloated? If the >> meat-and-potatoes of a table is held in toast, then wouldn't you expect the >> size of the table and the size of the index to be about the same? >> >> Cheers, >> >> Jeff >> > > > I am running queries to see bloat indexes [1]. Also i understand an index > can't have same size as table. > If you have any other table that can prove the index is indeed bloat, > please let me know and i will be happy to post results here. > > [1] https://wiki.postgresql.org/wiki/Index_Maintenance > > Patrick > >
FYI - using this query to see the index size: 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 indexrelname = 'index_name';