Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id C8ABF9FB1EF for ; Sat, 27 Jan 2007 07:34:36 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 77064-01 for ; Sat, 27 Jan 2007 07:34:33 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from exchange.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by postgresql.org (Postfix) with ESMTP id AB9919FA4CA for ; Sat, 27 Jan 2007 07:34:33 -0400 (AST) Received: from 10.10.1.93 ([10.10.1.93]) by exchange.g2switchworks.com ([10.10.1.2]) with Microsoft Exchange Server HTTP-DAV ; Sat, 27 Jan 2007 11:34:25 +0000 Received: from localhost.localdomain by mail.g2switchworks.com; 27 Jan 2007 05:33:28 -0600 Subject: Re: Seqscan/Indexscan still a known issue? From: Scott Marlowe To: Russell Smith Cc: Guido Neitzer , PostgreSQL Performance In-Reply-To: <45BB2CFA.50609@pws.com.au> References: <45BAB682.6060709@mochima.com> <45BB00BB.4070009@pws.com.au> <58F562A7-2925-4E37-BEA7-3D26748D408A@event-s.net> <45BB2CFA.50609@pws.com.au> Content-Type: text/plain Content-Transfer-Encoding: 7bit Date: Sat, 27 Jan 2007 05:33:28 -0600 Message-Id: <1169897608.11009.2.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.2.2 (2.2.2-5) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/383 X-Sequence-Number: 22984 On Sat, 2007-01-27 at 21:44 +1100, Russell Smith wrote: > Guido Neitzer wrote: > > On 27.01.2007, at 00:35, Russell Smith wrote: > > > >> Guess 1 would be that your primary key is int8, but can't be certain > >> that is what's causing the problem. > > > > Why could that be a problem? > Before 8.0, the planner would not choose an index scan if the types were > different int8_col = const, int8_col = 4. > 4 in this example is cast to int4. int8 != int4. So the planner will > not choose an index scan. But, in 7.4 setting enable_seqscan off would not make it use that index. For the OP, the problem is likely either that the stats for the column are off, effective_cache_size is set too low, and / or random_page_cost is too high. there are other possibilities as well. FYI, I upgraded the server we use at work to scan a statistical db of our production performance, and the queries we run there, which take anywhere from a few seconds to 20-30 minutes, run much faster. About an hour after the upgrade I had a user ask what I'd done to the db to make it so much faster. The upgrade was 7.4 to 8.1 btw... still testing 8.2, and it looks very good.