Feedback-ID: i76984098:Fastmail
Message-ID: <c8cd9d54-376f-42be-a394-52099dd3ca1f@aklaver.com>
Date: Fri, 23 Aug 2024 09:53:15 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: dead tuple difference between pgstattuple and pg_stat_user_tables
From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Matthew Tice <mjtice@gmail.com>
Cc: pgsql-general@lists.postgresql.org
References: <CA+taBv8ce8KtXLcD3sHWRuvPfb4scHU6oVSz8NwcUX=F=ECZBA@mail.gmail.com>
 <19c4824f-24b8-4561-81c3-a2f4a7949803@aklaver.com>
 <CA+taBv9QcWyPZDcXh03spn9MJkUrGD5UnH=4i3Zt_5mp8awnfA@mail.gmail.com>
 <5f0601fd-7ab9-4b40-9356-43c463223bed@aklaver.com>
Content-Language: en-US
In-Reply-To: <5f0601fd-7ab9-4b40-9356-43c463223bed@aklaver.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/c8cd9d54-376f-42be-a394-52099dd3ca1f%40aklaver.com>
Precedence: bulk

On 8/23/24 09:51, Adrian Klaver wrote:
> On 8/23/24 09:33, Matthew Tice wrote:
>>
>>
>> On Fri, Aug 23, 2024 at 10:26 AM Adrian Klaver 
>> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>>     https://www.postgresql.org/docs/current/pgstattuple.html
>>     <https://www.postgresql.org/docs/current/pgstattuple.html>
>>
>>     pgstattuple_approx(regclass) returns record
>>
>>           pgstattuple_approx is a faster alternative to pgstattuple that
>>     returns approximate results.
>>
>>     Not sure how you get exact count out of that?
>>
>>
>> Maybe the wording is a little confusing to me. Under the section 
>> for pgstattuple_approx:
>> "pgstattuple_approx tries to avoid the full-table scan and returns 
>> exact dead tuple statistics along with an approximation of the number 
>> and size of live tuples and free space."
> 
> Yeah, see what you mean.
> 
> The part that bears more investigating for this case is:
> 
> "It does this by skipping pages that have only visible tuples according 
> to the visibility map (if a page has the corresponding VM bit set, then 
> it is assumed to contain no dead tuples).
> 
> Wondering if PostgreSQl-compatible covers this?

Meant to add:

What happens if you use pgstattuple instead?

-- 
Adrian Klaver
adrian.klaver@aklaver.com