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 A5B549DC84E for ; Fri, 16 Dec 2005 18:38:46 -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 59661-08 for ; Fri, 16 Dec 2005 18:38:48 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from flake.decibel.org (flake.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 75AD99DCBAE for ; Fri, 16 Dec 2005 18:38:44 -0400 (AST) Received: by flake.decibel.org (Postfix, from userid 1001) id CD0561528B; Fri, 16 Dec 2005 16:38:46 -0600 (CST) Date: Fri, 16 Dec 2005 16:38:46 -0600 From: "Jim C. Nasby" To: Kevin Brown , pgsql-performance@postgresql.org Subject: Re: Overriding the optimizer Message-ID: <20051216223846.GV53809@pervasive.com> References: <1134629567.14248.27.camel@firebolt> <200512150148.16125.blargity@gmail.com> <1134644526.14248.60.camel@firebolt> <43A1C40D.8040006@paradise.net.nz> <43A1F6DB.3080805@modgraph-usa.com> <43A226E7.7040508@paradise.net.nz> <43A22BE3.6020103@modgraph-usa.com> <20051216052025.GB691@filer> <43A25372.8090706@modgraph-usa.com> <20051216054855.GD691@filer> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20051216054855.GD691@filer> X-Operating-System: FreeBSD 4.11-RELEASE-p10 i386 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.9i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.009 required=5 tests=[AWL=0.009] X-Spam-Score: 0.009 X-Spam-Level: X-Archive-Number: 200512/410 X-Sequence-Number: 16231 On Thu, Dec 15, 2005 at 09:48:55PM -0800, Kevin Brown wrote: > Craig A. James wrote: > > Kevin Brown wrote: > > >>Hints are dangerous, and I consider them a last resort. > > > > > >If you consider them a last resort, then why do you consider them to > > >be a better alternative than a workaround such as turning off > > >enable_seqscan, when all the other tradeoffs are considered? > > > > If I understand enable_seqscan, it's an all-or-nothing affair. Turning it > > off turns it off for the whole database, right? The same is true of all > > of the planner-tuning parameters in the postgres conf file. > > Nope. What's in the conf file are the defaults. You can change them > on a per-connection basis, via the SET command. Thus, before doing > your problematic query: > > SET enable_seqscan = off; > > and then, after your query is done, > > SET enable_seqscan = on; You can also turn it off inside a transaction and have it only affect that transaction so that you can't accidentally forget to turn it back on (which could seriously hose things up if you're doing this in conjunction with a connection pool). -- 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