Re: unexpected result for wastedbytes query after vacuum full

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux