2017-02-22 13:10 GMT+13:00 Adrian Klaver <adrian.kla...@aklaver.com>:
> On 02/21/2017 03:41 PM, Patrick B wrote: > > 2017-02-22 11:11 GMT+13:00 Patrick B <patrickbake...@gmail.com > > <mailto:patrickbake...@gmail.com>>: > > > > 2017-02-22 10:59 GMT+13:00 Adrian Klaver <adrian.kla...@aklaver.com > > <mailto:adrian.kla...@aklaver.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_toast2706 GB82.62112838877240860000 <-- this belongs to the seg > > 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 > > <adrian.kla...@aklaver.com> > Patrick.
