X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 7FE58D1C9BE for ; Mon, 12 Apr 2004 18:02:48 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 81172-01 for ; Mon, 12 Apr 2004 18:02:48 -0300 (ADT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 3C7DBD1BAF8 for ; Mon, 12 Apr 2004 18:02:46 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i3CL2l77013763; Mon, 12 Apr 2004 17:02:47 -0400 (EDT) To: jdunn@autorevenue.com Cc: "'Postgresql Performance'" Subject: Re: index v. seqscan for certain values In-reply-to: <001d01c420c1$0ed23290$4f01a8c0@jeremydunn> References: <001d01c420c1$0ed23290$4f01a8c0@jeremydunn> Comments: In-reply-to "Jeremy Dunn" message dated "Mon, 12 Apr 2004 15:05:02 -0400" Date: Mon, 12 Apr 2004 17:02:47 -0400 Message-ID: <13762.1081803767@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/142 X-Sequence-Number: 6442 "Jeremy Dunn" 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