Le ven. 6 déc. 2019 à 18:18, Mike Schanne <mscha...@kns.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.
