2017-02-22 13:10 GMT+13:00 Adrian Klaver <adrian.klaver@xxxxxxxxxxx>:
On 02/21/2017 03:41 PM, Patrick B wrote:
> 2017-02-22 11:11 GMT+13:00 Patrick B <patrickbakerbr@xxxxxxxxx
> <mailto:patrickbakerbr@gmail.com >>:
>
> 2017-02-22 10:59 GMT+13:00 Adrian Klaver <adrian.klaver@xxxxxxxxxxx
> <mailto:adrian.klaver@aklaver.com >>:
> pg_toast2706 GB82.62112838877240860000 <-- this belongs to the seg>
> 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
>
>
> table.
>
>
>
>
> Have you looked at the functions here?:
> https://www.postgresql.org/docs/9.6/static/functions- admin.html#FUNCTIONS-ADMIN- DBOBJECT
> <https://www.postgresql.org/docs/9.6/static/functions- >admin.html#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';
The table schema would be useful.
Why? If i just wanna know how bloat indexes work?
>
>
> *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)
Not showing the complete explain analyze makes the above not all that enlightening.
I am showing the whole explain analyze mate.
>
>
> 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?
Because it is an indication that you may not have index bloat?
Queries for bloat indexes show that I do have bloat indexes. Also, it is really simple to look.
\d tablename
The table is 18GB big and the index is 17GB big.. this clearly shows me bloated index.
Not sure a runtime of 0.036 to 0.036 ms over a 2TB table is symptomatic of a problem.
Might be worth taking a look at:
https://www.postgresql.org/docs/9.6/static/monitoring- stats.html#PG-STAT-ALL-TABLES- VIEW
Patrick.
