pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Matthew T. O'Connor <matthew@zeut.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-hackers@postgresql.org, Otto Blomqvist <o.blomqvist@secomintl.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: lazy_update_relstats considered harmful (was Re: [PERFORM]
Date: Fri, 25 Mar 2005 16:12:27 -0500
Message-ID: <42447EBB.20905@zeut.net> (raw)
In-Reply-To: <12121.1111782169@sss.pgh.pa.us>
References: <d1v0a7$iau$1@news.hub.org>
	<42430FA0.9080204@zeut.net>
	<002b01c530c5$ad5a1620$e602a8c0@blomqvist>
	<42435408.30303@zeut.net>
	<00f201c53168$96903480$e602a8c0@blomqvist>
	<42446A66.6010504@zeut.net>
	<11991.1111780527@sss.pgh.pa.us>
	<12121.1111782169@sss.pgh.pa.us>

Tom Lane wrote:

>I wrote:
>  
>
>>One thing that is possibly relevant here is that in 8.0 a plain VACUUM
>>doesn't set reltuples to the exactly correct number, but to an
>>interpolated value that reflects our estimate of the "steady state"
>>average between vacuums.  I wonder if that code is wrong, or if it's
>>operating as designed but is confusing autovac.
>>    
>>
>
>Now that I think it over, I'm thinking that I must have been suffering
>severe brain fade the day I wrote lazy_update_relstats() (see
>vacuumlazy.c).  The numbers that that routine is averaging are the pre-
>and post-vacuum physical tuple counts.  But the difference between them
>consists of known-dead tuples, and we shouldn't be factoring dead tuples
>into reltuples.  The planner has always considered reltuples to count
>only live tuples, and I think this is correct on two grounds:
>
>1. The numbers of tuples estimated to be returned by scans certainly
>shouldn't count dead ones.
>
>2. Dead tuples don't have that much influence on scan costs either, at
>least not once they are marked as known-dead.  Certainly they shouldn't
>be charged at full freight.
>
>It's possible that there'd be some value in adding a column to pg_class
>to record dead tuple count, but given what we have now, the calculation
>in lazy_update_relstats is totally wrong.
>
>The idea I was trying to capture is that the tuple density is at a
>minimum right after VACUUM, and will increase as free space is filled
>in until the next VACUUM, so that recording the exact tuple count
>underestimates the number of tuples that will be seen on-the-average.
>But I'm not sure that idea really holds water.  The only way that a
>table can be at "steady state" over a long period is if the number of
>live tuples remains roughly constant (ie, inserts balance deletes).
>What actually increases and decreases over a VACUUM cycle is the density
>of *dead* tuples ... but per the above arguments this isn't something
>we should adjust reltuples for.
>
>So I'm thinking lazy_update_relstats should be ripped out and we should
>go back to recording just the actual stats.
>
>Sound reasonable?  Or was I right the first time and suffering brain
>fade today?
>




view thread (4+ messages)  latest in thread

Message-ID: <42447EBB.20905@zeut.net>
Permalink:  ../42447EBB.20905@zeut.net/
Also on:    postgresql.org/message-id/42447EBB.20905@zeut.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: matthew@zeut.net, tgl@sss.pgh.pa.us, o.blomqvist@secomintl.com
  Subject: Re: lazy_update_relstats considered harmful (was Re: [PERFORM]
  In-Reply-To: <42447EBB.20905@zeut.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox