2017-02-25 17:53 GMT+13:00 Patrick B <patrickbakerbr@xxxxxxxxx>:
2017-02-23 11:46 GMT+13:00 Jeff Janes <jeff.janes@xxxxxxxxx>:On Tue, Feb 21, 2017 at 1:44 PM, Patrick B <patrickbakerbr@xxxxxxxxx> wrote:Hi guys,I've got a lot of bloat indexes on my 4TB database.Let's take this example:Table: segIndex: ix_filter_by_treeTimes_used: 1018082183Table_size: 18 GB -- wrong. The table is mostly on pg_toast table. Its real size is 2TBIndex_size: 17 GBNum_writes 16245023Index 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,JeffI 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.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 definitionFROM pg_stat_user_indexes AS idstatJOIN pg_indexes ON indexrelname = indexnameJOIN pg_stat_user_tables AS tabstat ON idstat.relname = tabstat.relnameWHERE indexrelname = 'index_name';
