Re: bloat indexes - opinion

2017-02-22 11:11 GMT+13:00 Patrick B <patrickbakerbr(at)gmail(dot)com>:

> 2017-02-22 10:59 GMT+13:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>:
>
>> On 02/21/2017 01:44 PM, Patrick B 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
>>
>> How do you know one number is right and the other is wrong?
>>
>
>
> 1. on that table (seg) i store binary data. It is impossible to have only
> 18GB of it.
> 2.
>
> SELECT schema_name,
>
> pg_size_pretty(sum(table_size)::bigint),
>
> (sum(table_size) / pg_database_size(current_database())) * 100
>
> FROM (
>
> SELECT pg_catalog.pg_namespace.nspname as schema_name,
>
> pg_relation_size(pg_catalog.pg_class.oid) as table_size
>
> FROM pg_catalog.pg_class
>
> JOIN pg_catalog.pg_namespace ON relnamespace =
> pg_catalog.pg_namespace.oid
>
> ) t
>
> GROUP BY schema_name
>
> ORDER BY schema_name
>
>
> pg_toast 2706 GB 82.62112838877240860000 <-- this belongs to the seg
> table.
>
>
>
>>
>> Have you looked at the functions here?:
>> https://www.postgresql.org/docs/9.6/static/functions-admin.h
>> tml#FUNCTIONS-ADMIN-DBOBJECT
>>
>> > 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 the real impact of a bloat index? If I reindex it, queries will
>> > be faster?
>> >
>> > Thanks
>> > Patrick
>>
>
>

I ran the query before and after the reindex, and it seems it did not help
on performance.

*The query I used:*

explain analyze select * from seg where full_path = '/userfile/123';

*Before reindex:*

Index Scan using ix_filter_by_tree on seg (cost=0.00..144.87 rows=215
width=8) (actual time=0.047..0.047 rows=1 loops=1)
Index Cond: (full_path = '/userfile/123')
Total runtime: 0.059 ms
(3 rows)

*After reindex:*

Index Scan using ix_filter_by_tree on seg (cost=0.00..141.83 rows=220
width=8) (actual time=0.021..0.021 rows=1 loops=1)
Index Cond: (full_path = '/userfile/123')
Total runtime: 0.036 ms
(3 rows)

Note that the '*cost*' is pretty much the same.

*My question is:*
If I have a bloat index. Why do I need to reindex it if I got none
performance improvements?

Cheers
Patrick