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
