Re: unexpected result for wastedbytes query after vacuum full

Le ven. 6 déc. 2019 à 18:18, Mike Schanne <mschanne(at)kns(dot)com> a écrit :

> Hi all,
>
>
>
> This question is somewhat related to my previous question:
>
>
> https://www.postgresql.org/message-id/0871fcf35ceb4caa8a2204ca9c38e330%40USEPRDEX1.corp.kns.com
>
>
>
> I was attempting to measure the benefit of doing a VACUUM FULL on my
> database. I was using the query found here:
>
>
>
> https://wiki.postgresql.org/wiki/Show_database_bloat
>
>
>
> However, I got an unexpected result in that the “wastedbytes” value
> actually increased for some tables after doing the vacuum.
>
>
>
> Before VACUUM FULL:
>
> current_database | schemaname | tablename | tbloat |
> wastedbytes |
> iname | ibloat | wastedibytes
>
>
> ------------------+----------------+---------------------------+--------+-------------+-----------------------------------------------------------------+--------+--------------
>
> postgres | myschema | mytableA | 1.1 |
> 74440704 | myindex1
> | 0.2 | 0
>
> postgres | myschema | mytableA | 1.1 |
> 74440704 | myindex2
> | 0.2 | 0
>
> postgres | myschema | mytableA | 1.1 |
> 74440704 | myindex3
> | 0.2 | 0
>
> postgres | myschema | mytableA | 1.1 |
> 74440704 | myindex4
> | 0.2 | 0
>
> postgres | myschema | mytableB | 1.0 |
> 63324160 | myindex5
> | 0.0 | 0
>
> ...
>
> After VACUUM FULL:
>
> current_database | schemaname | tablename | tbloat |
> wastedbytes |
> iname | ibloat | wastedibytes
>
>
> ------------------+----------------+---------------------------+--------+-------------+-----------------------------------------------------------------+--------+--------------
>
> postgres | myschema | mytableA | 1.1 |
> 74506240 |
> myindex4 | 0.2
> | 0
>
> postgres | myschema | mytableA | 1.1 |
> 74506240 |
> myindex3 | 0.2
> | 0
>
> postgres | myschema | mytableA | 1.1 |
> 74506240 | myindex2
> | 0.2 | 0
>
> postgres | myschema | mytableA | 1.1 |
> 74506240 |
> myindex1 | 0.2
> | 0
>
> postgres | myschema | mytableB | 1.0 |
> 63332352 |
> myindex5 | 0.0
> | 0
>
> ...
>
>
>
> This is the schema for mytableA above:
>
>
>
> Column | Type |
> Modifiers
>
>
> ---------------+-----------------------------+----------------------------------------------------------------
>
> colA | integer | not null default
> nextval('myschema.myseq'::regclass)
>
> colB | integer |
>
> colC | integer |
>
> colD | timestamp without time zone |
>
> colE | json |
>
> colF | integer |
>
> colG | integer |
>
>
>
> I was wondering if the fact that we use a json column could be interfering
> with the wastedbytes calculation. Can anyone explain how wastedbytes could
> increase from a vacuum?
>
>
>

This query uses the column statistics to estimate bloat. AFAIK, json
columns don't have statistics, so the estimation can't be relied on (for
this specific table at least).

--
Guillaume.