Subject: Re: Slow query on a one-tuple table
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: =?UTF-8?Q?Lu=c3=ads_Roberto_Weck?= <luisroberto@siscobra.com.br>,
 Michael Lewis <mlewis@entrata.com>, pgsql-performance@lists.postgresql.org
References: 
 <CA+t6e1kacbVHq69aihEVeBzH_nMY5eK7bZ-UXL_FuUquEgF_gg@mail.gmail.com>
 <24571.1568910502@sss.pgh.pa.us>
 <774d6080-d6d7-e9e7-7351-9a412c7d5956@siscobra.com.br>
 <CAHOFxGqMpUxn54jMLoU4-HcUata2yfLgjEbRHdJG3k0BBDPbog@mail.gmail.com>
 <ab6ea98e-6266-7665-40b7-151c5736c08d@siscobra.com.br>
 <BN6PR1701MB1890E31E15D486A85FEC8510DA890@BN6PR1701MB1890.namprd17.prod.outlook.com>
 <bf03aedc-9201-f7c9-ef25-e795d4e1626a@siscobra.com.br>
 <BN6PR1701MB1890A207FBF02BBC5E3C9F01DA890@BN6PR1701MB1890.namprd17.prod.outlook.com>
 <18ae8108-acaf-e9cc-96a9-ab20f4147f5a@siscobra.com.br>
 <35df2bd8-4c7c-7ace-6bdc-d6d97a101a14@siscobra.com.br>
 <1980a708-ed4a-6fc6-2792-33c07b07d704@siscobra.com.br>
 <CAHOFxGosug4xfpieTqFdcFqwF-mK3hn8Tr9boN=jLFDU3y-JNQ@mail.gmail.com>
 <f1468794-1681-3f1e-2f9c-b491cb87ed5d@siscobra.com.br>
 <18657.1568933831@sss.pgh.pa.us>
From: MichaelDBA <MichaelDBA@sqlexec.com>
Message-ID: <2d20142b-16b4-32e9-cf63-81d813d4718c@sqlexec.com>
Date: Fri, 20 Sep 2019 09:09:35 -0400
User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:52.0) Gecko/20100101
 PostboxApp/6.1.18
MIME-Version: 1.0
In-Reply-To: <18657.1568933831@sss.pgh.pa.us>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Precedence: bulk

Hi all,

I sometimes set autovacuum_vacuum_scale factor = 0 but only when I also 
set autovacuum_vacuum_threshold to some non-zero number to force vacuums 
after a certain number of rows are updated.  It takes the math out of it 
by setting the threshold explicitly.

But in this case he has also set autovacuum_vacuum_threshold to only 
25!  So I think you have to fix your settings by increasing one or both 
accordingly.

Regards,
Michael Vitale


Tom Lane wrote on 9/19/2019 6:57 PM:
> =?UTF-8?Q?Lu=c3=ads_Roberto_Weck?= <luisroberto@siscobra.com.br> writes:
>> As fas as autovacuum options, this is what I'm using:
>> autovacuum_vacuum_scale_factor=0,
> Ugh ... maybe I'm misremembering, but I *think* that has the effect
> of disabling autovac completely.  You don't want zero.
>
> Check in pg_stat_all_tables.last_autovacuum to see if anything
> is happening.  If the dates seem reasonably current, then I'm wrong.
>
> 			regards, tom lane
>
>