pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: D'Arcy J.M. Cain <darcy@druid.net>
Cc: Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
Cc: Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: Select max(foo) and select count(*) optimization
Date: Tue, 06 Jan 2004 18:19:55 -0500
Message-ID: <2698.1073431195@sss.pgh.pa.us> (raw)
In-Reply-To: <200401061757.05278.darcy@druid.net>
References: <BC1F1F20.71370%siracusa@mindspring.com>
	<200401060718.08444.darcy@druid.net>
	<200401061750.09530.shridhar_daithankar@persistent.co.in>
	<200401061757.05278.darcy@druid.net>

"D'Arcy J.M. Cain" <darcy@druid.net> writes:
> In any case, if I have to vacuum a 20,000,000 row table to get an accurate 
> count then I may as well run count(*) on it.
> (*): Actually I only analyze but I understand that that should be sufficient.

ANALYZE without VACUUM will deliver a not-very-accurate estimate, since it
only looks at a sample of the table's pages and doesn't grovel through
every one.  Any of the VACUUM variants, on the other hand, will set
pg_class.reltuples reasonably accurately (as the number of rows actually
seen and left undeleted by the VACUUM pass).

There are pathological cases where ANALYZE's estimate of the overall row
count can be horribly bad --- mainly, when the early pages of the table
are empty or nearly so, but there are well-filled pages out near the
end.  I have a TODO item to try to make ANALYZE less prone to getting
fooled that way...

			regards, tom lane



view thread (19+ messages)

Message-ID: <2698.1073431195@sss.pgh.pa.us>
Permalink:  ../2698.1073431195@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/2698.1073431195@sss.pgh.pa.us

 · 

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: tgl@sss.pgh.pa.us, darcy@druid.net, shridhar_daithankar@persistent.co.in
  Subject: Re: Select max(foo) and select count(*) optimization
  In-Reply-To: <2698.1073431195@sss.pgh.pa.us>

* 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