Re: unexpected result for wastedbytes query after vacuum full - Mailing list pgsql-performance
| From | Guillaume Lelarge |
|---|---|
| Subject | Re: unexpected result for wastedbytes query after vacuum full |
| Date | |
| Msg-id | CAECtzeX-BwRbgvp_40AyJAA8hdbXh3=a=f6ZEfYr+-1_wHSMjQ@mail.gmail.com Whole thread Raw |
| In response to | unexpected result for wastedbytes query after vacuum full (Mike Schanne <mschanne@kns.com>) |
| Responses |
Re: unexpected result for wastedbytes query after vacuum full
(Jeff Janes <jeff.janes@gmail.com>)
|
| List | pgsql-performance |
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?
--
pgsql-performance by date: