Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1idHFT-0001Hc-C5 for pgsql-performance@arkaria.postgresql.org; Fri, 06 Dec 2019 17:18:35 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1idHFS-0007Cn-1f for pgsql-performance@arkaria.postgresql.org; Fri, 06 Dec 2019 17:18:34 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1idHFR-0007Cf-Go for pgsql-performance@lists.postgresql.org; Fri, 06 Dec 2019 17:18:33 +0000 Received: from mails2.kns.com ([199.171.180.12]) by makus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1idHFO-0000A2-6Q for pgsql-performance@postgresql.org; Fri, 06 Dec 2019 17:18:32 +0000 X-ASG-Debug-ID: 1575652706-0a96b771ee18f910001-c4zXsc Received: from USEPRDEX1.corp.kns.com (useprdex1.corp.kns.com [10.40.10.30]) by mails2.kns.com with ESMTP id WHu9ewdEPkP5TOor for ; Fri, 06 Dec 2019 12:18:26 -0500 (EST) X-Barracuda-Envelope-From: mschanne@kns.com Received: from USEPRDEX1.corp.kns.com (10.40.10.30) by USEPRDEX1.corp.kns.com (10.40.10.30) with Microsoft SMTP Server (TLS) id 15.0.1263.5; Fri, 6 Dec 2019 12:18:20 -0500 Received: from USEPRDEX1.corp.kns.com ([fe80::8902:ecdb:4dc3:cfac]) by USEPRDEX1.corp.kns.com ([fe80::8902:ecdb:4dc3:cfac%22]) with mapi id 15.00.1263.000; Fri, 6 Dec 2019 12:18:20 -0500 From: Mike Schanne To: "'pgsql-performance@postgresql.org'" Subject: unexpected result for wastedbytes query after vacuum full Thread-Topic: unexpected result for wastedbytes query after vacuum full X-ASG-Orig-Subj: unexpected result for wastedbytes query after vacuum full Thread-Index: AdWsWR8phvrkp70cRVmKqtyS5vftoQ== Date: Fri, 6 Dec 2019 17:18:20 +0000 Message-ID: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-ms-exchange-transport-fromentityheader: Hosted x-originating-ip: [10.40.22.174] Content-Type: multipart/alternative; boundary="_000_db92c5c1a0904209a4ef2d857276ab4bUSEPRDEX1corpknscom_" MIME-Version: 1.0 X-Barracuda-Connect: useprdex1.corp.kns.com[10.40.10.30] X-Barracuda-Start-Time: 1575652706 X-Barracuda-URL: https://Mails2.kns.com:443/cgi-mod/mark.cgi X-Virus-Scanned: by bsmtpd at kns.com X-Barracuda-Scan-Msg-Size: 19881 X-Barracuda-BRTS-Status: 1 X-Barracuda-Spam-Score: 0.00 X-Barracuda-Spam-Status: No, SCORE=0.00 using global scores of TAG_LEVEL=1000.0 QUARANTINE_LEVEL=1000.0 KILL_LEVEL=1000.0 tests=HTML_MESSAGE X-Barracuda-Spam-Report: Code version 3.2, rules version 3.2.3.78491 Rule breakdown below pts rule name description ---- ---------------------- -------------------------------------------------- 0.00 HTML_MESSAGE BODY: HTML included in message List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --_000_db92c5c1a0904209a4ef2d857276ab4bUSEPRDEX1corpknscom_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi all, This question is somewhat related to my previous question: https://www.postgresql.org/message-id/0871fcf35ceb4caa8a2204ca9c38e330%40US= EPRDEX1.corp.kns.com I was attempting to measure the benefit of doing a VACUUM FULL on my databa= se. 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 actuall= y increased for some tables after doing the vacuum. Before VACUUM FULL: current_database | schemaname | tablename | tbloat | wa= stedbytes | iname = | ibloat | wastedibytes ------------------+----------------+---------------------------+--------+--= -----------+---------------------------------------------------------------= --+--------+-------------- postgres | myschema | mytableA | 1.1 | 7= 4440704 | myindex1 = | 0.2 | 0 postgres | myschema | mytableA | 1.1 | 7= 4440704 | myindex2 = | 0.2 | 0 postgres | myschema | mytableA | 1.1 | 7= 4440704 | myindex3 = | 0.2 | 0 postgres | myschema | mytableA | 1.1 | 7= 4440704 | myindex4 = | 0.2 | 0 postgres | myschema | mytableB | 1.0 | 6= 3324160 | myindex5 = | 0.0 | 0 ... After VACUUM FULL: current_database | schemaname | tablename | tbloat | = wastedbytes | iname = | ibloat | wastedibytes ------------------+----------------+---------------------------+--------+--= -----------+---------------------------------------------------------------= --+--------+-------------- postgres | myschema | mytableA | 1.1 | 7= 4506240 | myindex4 = | 0.2 | 0 postgres | myschema | mytableA | 1.1 | 7= 4506240 | myindex3 = | 0.2 | 0 postgres | myschema | mytableA | 1.1 | 7= 4506240 | myindex2 = | 0.2 | 0 postgres | myschema | mytableA | 1.1 | 7= 4506240 | myindex1 = | 0.2 | 0 postgres | myschema | mytableB | 1.0 | 6= 3332352 | myindex5 = | 0.0 | 0 ... This is the schema for mytableA above: Column | Type | Mo= difiers ---------------+-----------------------------+-----------------------------= ----------------------------------- colA | integer | not null default nextval('mys= chema.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? Thanks, Mike ________________________________ This email is non-binding, is subject to contract, and neither Kulicke and = Soffa Industries, Inc. nor its subsidiaries (each and collectively "K&S") s= hall have any obligation to you to consummate the transactions herein or to= enter into any agreement, other than in accordance with the terms and cond= itions of a definitive agreement if and when negotiated, finalized and exec= uted between the parties. This email and all its contents are protected by = International and United States copyright laws. Any reproduction or use of = all or any part of this email without the express written consent of K&S is= prohibited. --_000_db92c5c1a0904209a4ef2d857276ab4bUSEPRDEX1corpknscom_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi all,

 

This question is somewhat related to my previous que= stion:

https://www.postgre= sql.org/message-id/0871fcf35ceb4caa8a2204ca9c38e330%40USEPRDEX1.corp.kns.co= m

 

I was attempting to measure the benefit of doing a V= ACUUM FULL on my database.  I was using the query found here:

 

https://wiki.postgresql.org/wiki/Show_database_bloat<= /o:p>

 

However, I got an unexpected result in that the R= 20;wastedbytes” value actually increased for some tables after doing = the vacuum. 

 

Before VACUUM FULL:

current_database |   schemaname  = ; |         tablename  &n= bsp;      | tbloat | wastedbytes |  &nbs= p;            &= nbsp;           &nbs= p;  iname          &= nbsp;           &nbs= p;       | ibloat | wastedibytes

------------------+----------------+--------= -------------------+--------+-------------+--------------------= ---------------------------------------------+--------+------------= --

postgres       &n= bsp; | myschema | mytableA        &= nbsp;           &nbs= p; |    1.1 |    74440704 | myindex1 &nb= sp;            =             &nb= sp;            =             &nb= sp;     |    0.2 |   &nbs= p;        0

postgres       &n= bsp; | myschema | mytableA         =             &nb= sp;|    1.1 |    74440704 | myindex2 &nb= sp;            =             &nb= sp;            =             &nb= sp;     |    0.2 |   &nbs= p;        0

postgres       &n= bsp; | myschema | mytableA        &= nbsp;           &nbs= p; |    1.1 |    74440704 | myindex3 &nb= sp;            =             &nb= sp;             = ;            &n= bsp;    |    0.2 |   &nbs= p;        0

postgres       &n= bsp; | myschema | mytableA        &= nbsp;           &nbs= p; |    1.1 |    74440704 | myindex4 &nb= sp;            =             &nb= sp;            =             &nb= sp;     |    0.2 |   &nbs= p;        0

postgres       &n= bsp; | myschema | mytableB        &= nbsp;           &nbs= p; |    1.0 |    63324160 | myindex5 &nb= sp;            =             &nb= sp;            =             &nb= sp;     |    0.0 |   &nbs= p;        0

...

After VACUUM FULL:

  current_database |   schemaname&nbs= p;  |         tablename &= nbsp;       | tbloat | wastedbytes | &nb= sp;            =             &nb= sp;   iname         =             &nb= sp;       | ibloat | wastedibytes

------------------+----------------+--------= -------------------+--------+-------------+--------------------= ---------------------------------------------+--------+------------= --

postgres       &n= bsp; | myschema | mytableA        &= nbsp;           &nbs= p; |    1.1 |    74506240 | myindex4 &nb= sp;            =             &nb= sp;            =             &nb= sp;      |    0.2 |  &nbs= p;         0

postgres       &n= bsp; | myschema | mytableA        &= nbsp;           &nbs= p; |    1.1 |    74506240 | myindex3 &nb= sp;            =             &nb= sp;            =             &nb= sp;      |    0.2 |  &nbs= p;         0

postgres       &n= bsp; | myschema | mytableA        &= nbsp;           &nbs= p; |    1.1 |    74506240 | myindex2 &nb= sp;            =             &nb= sp;            =             &nb= sp;      |    0.2 |  &nbs= p;         0

postgres       &n= bsp; | myschema | mytableA        &= nbsp;           &nbs= p; |    1.1 |    74506240 | myindex1 &nb= sp;            =             &nb= sp;            =             &nb= sp;      |    0.2 |  &nbs= p;         0

postgres       &n= bsp; | myschema | mytableB        &= nbsp;            &nb= sp;|    1.0 |    63332352 | myindex5 &nb= sp;            =             &nb= sp;            =             &nb= sp;      |    0.0 |  &nbs= p;         0

...

 

This is the schema for mytableA above:

 

    Column     |&= nbsp;           Type = ;            | =              &n= bsp;            = ;Modifiers

---------------+-----------------------------= 3;----------------------------------------------------------------

colA        =   | integer          = ;           | not null de= fault nextval('myschema.myseq'::regclass)

colB        =   | integer          = ;           |<= /p>

colC        =   | integer          = ;           |<= /p>

colD        =   | timestamp without time zone |

colE        =   | json          &n= bsp;            = ; |

colF        =   | integer          = ;           |<= /p>

colG        =   | integer          = ;           |<= /p>

 

I was wondering if the fact that we use a json colum= n could be interfering with the wastedbytes calculation.  Can anyone e= xplain how wastedbytes could increase from a vacuum?

 

Thanks,

Mike



This email is non-binding, is subject to contract, and neither Kulicke and = Soffa Industries, Inc. nor its subsidiaries (each and collectively “K= &S”) shall have any obligation to you to consummate the transacti= ons herein or to enter into any agreement, other than in accordance with the terms and conditions of a definitive agreement= if and when negotiated, finalized and executed between the parties. This e= mail and all its contents are protected by International and United States = copyright laws. Any reproduction or use of all or any part of this email without the express written consen= t of K&S is prohibited.
 --_000_db92c5c1a0904209a4ef2d857276ab4bUSEPRDEX1corpknscom_--