pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Simon Riggs <simon@2ndquadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Matthew T. O'Connor <matthew@zeut.net>
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 22:20:23 +0000
Message-ID: <1111789223.11750.839.camel@localhost.localdomain> (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>

On Fri, 2005-03-25 at 15:22 -0500, Tom Lane wrote:
> 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.

Yes, minor additional CPU time, but the main issue is when the dead
tuples force additional I/O.

> 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.

Yes, thats the way. We can record the (averaged?) dead tuple count, but
also record the actual row count in reltuples.

We definitely need to record the physical and logical tuple counts,
since each of them have different contributions to run-times.

For comparing seq scan v index, we need to look at the physical tuples
count * avg row size, whereas when we calculate number of rows returned
we should look at fractions of the logical row count.

> 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?

Well, I think the original idea had some validity, but clearly
lazy_update_relstats isn't the way to do it even though we thought so at
the time.

Best Regards, Simon Riggs





view thread (4+ messages)  latest in thread

Message-ID: <1111789223.11750.839.camel@localhost.localdomain>
Permalink:  ../1111789223.11750.839.camel@localhost.localdomain/
Also on:    postgresql.org/message-id/1111789223.11750.839.camel@localhost.localdomain

 · 

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: simon@2ndquadrant.com, tgl@sss.pgh.pa.us, matthew@zeut.net, o.blomqvist@secomintl.com
  Subject: Re: lazy_update_relstats considered harmful (was Re: [PERFORM]
  In-Reply-To: <1111789223.11750.839.camel@localhost.localdomain>

* 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