X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 8A8B311F6C5C for ; Sat, 1 Apr 2006 11:32:50 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 75357-01 for ; Sat, 1 Apr 2006 11:32:50 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 28F5111F66AC for ; Sat, 1 Apr 2006 11:32:47 -0400 (AST) Received: by noel.decibel.org (Postfix, from userid 1001) id 9FEFC56423; Sat, 1 Apr 2006 09:32:48 -0600 (CST) Received: (hashcash-sendmail, from uid 1001); Sat, 1 Apr 2006 09:32:47 -0600 Date: Sat, 1 Apr 2006 09:32:47 -0600 From: "Jim C. Nasby" To: chris smith Cc: Brendan Duddridge , PostgreSQL Performance Subject: Re: Query using SeqScan instead of IndexScan Message-ID: <20060401153247.GI49405@pervasive.com> References: <2F1712F1-E5E2-455E-A291-06F300A66549@clickspace.com> <20060331155911.GP49405@pervasive.com> <3c1395330603311723i368092f2jf0c2291ab033ae2a@mail.gmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <3c1395330603311723i368092f2jf0c2291ab033ae2a@mail.gmail.com> X-Operating-System: FreeBSD 6.0-RELEASE-p4 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Hashcash: 1:20:060401:dmagick@gmail.com::gU0tVAmheXwFhBm/:00000000000000000000 0000000000000000000000008j9Z X-Hashcash: 1:20:060401:brendan@clickspace.com::+/1/Q9Cd0xZ+jFu3:000000000000000 0000000000000000000000002q0h X-Hashcash: 1:20:060401:pgsql-performance@postgresql.org::vctwFvHBoLySTmqv:00000 0000000000000000000000000AaY X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.108 required=5 tests=[AWL=0.108] X-Spam-Score: 0.108 X-Spam-Level: X-Archive-Number: 200604/2 X-Sequence-Number: 18086 On Sat, Apr 01, 2006 at 11:23:37AM +1000, chris smith wrote: > On 4/1/06, Brendan Duddridge wrote: > > Hi Jim, > > > > I'm not quite sure what you mean by the correlation of category_id? > > It means how many distinct values does it have (at least that's my > understanding of it ;) ). Your understanding is wrong. :) What you're discussing is n_distinct. http://www.postgresql.org/docs/8.1/interactive/view-pg-stats.html correlation: "Statistical correlation between physical row ordering and logical ordering of the column values. This ranges from -1 to +1. When the value is near -1 or +1, an index scan on the column will be estimated to be cheaper than when it is near zero, due to reduction of random access to the disk. (This column is NULL if the column data type does not have a < operator.)" In other words, the following will have a correlation of 1: 1 2 3 ... 998 999 1000 And this is -1... 1000 999 ... 2 1 While this would have a very low correlation: 1 1000 2 999 ... The lower the correlation, the more expensive an index scan is, because it's more random. As I mentioned, I believe that the current index scan cost estimator is flawed though, because it will bias heavily against correlations that aren't close to 1 or -1. So, what does SELECT * FROM pg_stats WHERE tablename='table' AND attname='category_id'; show? -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461