pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: jdunn@autorevenue.com
Cc: 'Postgresql Performance' <pgsql-performance@postgresql.org>
Subject: Re: index v. seqscan for certain values
Date: Mon, 12 Apr 2004 17:02:47 -0400
Message-ID: <13762.1081803767@sss.pgh.pa.us> (raw)
In-Reply-To: <001d01c420c1$0ed23290$4f01a8c0@jeremydunn>
References: <001d01c420c1$0ed23290$4f01a8c0@jeremydunn>

"Jeremy Dunn" <jdunn@autorevenue.com> writes:
> Agreed.  However, given that count(*) is a question that can be answered
> _solely_ using the index (without reference to the actual data blocks),

As Bruno noted, that is not the case in Postgres; we must visit the
table rows anyway.

> When I just tried it again with a value of 300, analyze, then run the
> query, I get a *worse* result for an estimate.  I don't understand this.

That's annoying.  How repeatable are these results --- if you do ANALYZE
over again several times, how much does the row count estimate change
each time?  (It should change somewhat, since ANALYZE is taking a random
sample, but one would like to think not a whole lot.)  Is the variance
more or less at the higher stats target?  Take a look at a few different
CID values to get a sense of the accuracy, don't look at just one ...

(Actually, you might find it more profitable to look at the pg_stats
entry for the CID column rather than reverse-engineering the stats via
ANALYZE.  Look at how well the most-common-values list and associated
frequency numbers track reality.)

Also, can you think of any reason for the distribution of CID values
to be nonuniform within the table?  For instance, do rows get inserted
in order of increasing CID, or is there any clustering of rows with the
same CID?

			regards, tom lane



view thread (13+ messages)  latest in thread

Message-ID: <13762.1081803767@sss.pgh.pa.us>
Permalink:  ../13762.1081803767@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/13762.1081803767@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, jdunn@autorevenue.com
  Subject: Re: index v. seqscan for certain values
  In-Reply-To: <13762.1081803767@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