X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 3289632A1CB for ; Mon, 11 Oct 2004 22:26:17 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 74558-06 for ; Mon, 11 Oct 2004 21:26:06 +0000 (GMT) Received: from pw6tiger.de (pw6tiger.de [217.160.140.116]) by svr1.postgresql.org (Postfix) with SMTP id BC05F32A1A4 for ; Mon, 11 Oct 2004 22:26:06 +0100 (BST) Received: (qmail 9227 invoked by uid 505); 11 Oct 2004 21:26:05 -0000 Received: from vygen@gmx.de by planwerk6 by uid 89 with qmail-scanner-1.14 (virus scan: Clear:. Processed in 0.263111 secs); 11 Oct 2004 21:26:05 -0000 Received: from p508cc1e2.dip0.t-ipconnect.de (HELO ?192.168.1.102?) (vygen@planwerk6.de@80.140.193.226) by pw6tiger.de with SMTP; 11 Oct 2004 21:26:05 -0000 From: Janning Vygen To: Francisco Reyes Subject: Re: why my query is not using index?? Date: Mon, 11 Oct 2004 23:26:02 +0200 User-Agent: KMail/1.7 Cc: HyunSung Jang , pgsql-performance@postgresql.org References: <41639F3C.4050103@siche.net> <200410111425.02671.vygen@gmx.de> <20041011163756.M97379@zoraida.natserv.net> In-Reply-To: <20041011163756.M97379@zoraida.natserv.net> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200410112326.02486.vygen@gmx.de> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/143 X-Sequence-Number: 8619 Am Montag, 11. Oktober 2004 22:49 schrieb Francisco Reyes: > On Mon, 11 Oct 2004, Janning Vygen wrote: > > postgres uses a seq scan if its faster. In your case postgres seems to > > know that most of your rows have a date < 2004-01-01 and so doesn't need > > to consult the index if it has to read every page anyway. seq scan can be > > faster on small tables. try (in psql) "SET enable_seqscan TO off;" > > before running your query and see how postgres plans it without using seq > > scan. > > I was about to post and saw this message. > I have a query that was using sequential scans. Upon turning seqscan to > off it changed to using the index. What does that mean? enable_seqscan off means that postgres is not allowed to use seqscan. default is on and postgres decides for each table lookup which method is faster: seq scan or index scan. thats what the planner does: deciding which access method might be the fastest. > The tables are under 5k records so I wonder if that is why the optimizer > is option, on it's default state, to do sequential scans. if you have small tables, postgres is using seqscan to reduce disk lookups. postgresql reads disk pages in 8k blocks. if your whole table is under 8k there is no reason for postgres to load an index from another disk page because it has to load the whole disk anyway. not sure, but i think postgres also analyzes the table to see which values are in there. if you have a huge table with a column of integers and postgres knows that 99% are of value 1 and you are looking for a row with a value of 1, why should it use an index just to see that it has to load the whole table to find a matching row. And that's why you can't make performance tests with small tables. you need test data which is as close as possible to real data. > I was also wondering if there is a relation between the sequential scans > and the fact that my entire query is a series of left joins: no. janning