From: | Patrick B <patrickbakerbr(at)gmail(dot)com> |
---|---|
To: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: bloat indexes - opinion |
Date: | 2017-02-25 05:11:27 |
Message-ID: | CAJNY3iuwG64di_WjOZiAoG-G8szO7X49rmZtrU57y8_L6549PA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
2017-02-25 17:53 GMT+13:00 Patrick B <patrickbakerbr(at)gmail(dot)com>:
>
>
> 2017-02-23 11:46 GMT+13:00 Jeff Janes <jeff(dot)janes(at)gmail(dot)com>:
>
>> On Tue, Feb 21, 2017 at 1:44 PM, Patrick B <patrickbakerbr(at)gmail(dot)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';
From | Date | Subject | |
---|---|---|---|
Next Message | lisandro | 2017-02-25 12:19:38 | GMT FATAL: remaining connection slots are reserved for non-replication superuser connections, but I'm using pgBouncer for connection pooling |
Previous Message | Patrick B | 2017-02-25 04:53:21 | Re: bloat indexes - opinion |