Re: bloat indexes - opinion

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.
> 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
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>