X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id EAAC5D1C9ED for ; Sat, 2 Aug 2003 15:59:57 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 35626-05 for ; Sat, 2 Aug 2003 12:58:46 -0300 (ADT) Received: from flake.decibel.org (unknown [66.143.173.58]) by svr1.postgresql.org (Postfix) with SMTP id 665EBD1C9EA for ; Sat, 2 Aug 2003 12:59:55 -0300 (ADT) Received: (qmail 9205 invoked by uid 1001); 2 Aug 2003 15:59:54 -0000 Date: Sat, 2 Aug 2003 10:59:54 -0500 From: "Jim C. Nasby" To: pgsql-performance@postgresql.org Subject: Re: Odd explain estimate Message-ID: <20030802155954.GK55392@nasby.net> References: <20030731195145.GD55392@nasby.net> <20030731205921.GF16230@libertyrms.info> <20030731225959.GH55392@nasby.net> <20030801121612.GD18200@libertyrms.info> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20030801121612.GD18200@libertyrms.info> User-Agent: Mutt/1.4.1i X-Operating-System: FreeBSD 4.8-RELEASE i386 X-Distributed: Join the Effort! http://www.distributed.net X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/22 X-Sequence-Number: 2839 On Fri, Aug 01, 2003 at 08:16:12AM -0400, Andrew Sullivan wrote: > On Thu, Jul 31, 2003 at 05:59:59PM -0500, Jim C. Nasby wrote: > > > > Well, if I don't do this it wants to seqscan a table that occupies 350k > > pages, instead of pulling a couple thousand rows. I started running it > > with the seqscan and it's already taken way longer than it does if I > > disable seqscan. > > That was indeed the question. > > If it uses a seqscan when it ought not to do, then there's something > wrong with the statistics, or you haven't vacuum analysed correctly, > or your table needs vacuum full (is it really 350k pages, or is that > mostly dead space?), &c. -- all the usual bad-seqscan candidates. > > enable_seqscan=off is probably not a good strategy for any moderately > complicated query. If the planner were perfect, of course, you'd > never need it at all. Set statistics on the ID colum to 1000, vacuum analyze, and it's good to go now. Thanks for your help! -- Jim C. Nasby, Database Consultant jim@nasby.net Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"