On 02/21/2017 03:41 PM, Patrick B wrote: > 2017-02-22 11:11 GMT+13:00 Patrick B <patrickbakerbr@xxxxxxxxx > <mailto:patrickbakerbr@xxxxxxxxx>>: > > 2017-02-22 10:59 GMT+13:00 Adrian Klaver <adrian.klaver@xxxxxxxxxxx > <mailto:adrian.klaver@xxxxxxxxxxx>>: > > 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. > > > *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. > > > 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? 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 > > Cheers > Patrick > -- Adrian Klaver adrian.klaver@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
