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 044EA9DC81C for ; Tue, 20 Dec 2005 14:56:20 -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 33632-02 for ; Tue, 20 Dec 2005 14:56:19 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (unknown [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 7E36E9DC819 for ; Tue, 20 Dec 2005 14:56:17 -0400 (AST) Received: by noel.decibel.org (Postfix, from userid 1001) id 65F6C3982E; Tue, 20 Dec 2005 12:56:18 -0600 (CST) Date: Tue, 20 Dec 2005 12:56:18 -0600 From: "Jim C. Nasby" To: Jaime Casanova Cc: Mark Kirkwood , "Craig A. James" , pgsql-performance@postgresql.org Subject: Re: Overriding the optimizer Message-ID: <20051220185618.GE28771@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> <43A231AA.9070708@paradise.net.nz> <20051216224512.GX53809@pervasive.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: X-Operating-System: FreeBSD 6.0-RELEASE amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.035 required=5 tests=[AWL=0.035] X-Spam-Score: 0.035 X-Spam-Level: X-Archive-Number: 200512/504 X-Sequence-Number: 16325 On Sat, Dec 17, 2005 at 07:31:40AM -0500, Jaime Casanova wrote: > > > Yeah it would - an implementation I have seen that I like is where the > > > developer can supply the *entire* execution plan with a query. This is > > > complex enough to make casual use unlikely :-), but provides the ability > > > to try out other plans, and also fix that vital query that must run > > > today..... > > > > Being able to specify an exact plan would also provide for query plan > > stability; something that is critically important in certain > > applications. If you have to meet a specific response time requirement > > for a query, you can't afford to have the optimizer suddenly decide that > > some other plan might be faster when in fact it's much slower. > > Plan stability doesn't mean time response stability... > The plan that today is almost instantaneous tomorrow can take hours... Sure, if your underlying data changes that much, but that's often not going to happen in production systems (especially OLTP where this is most important). Of course if you have a proposal for ensuring that a query always finishes in X amount of time, rather than always using the same plan, I'd love to hear it. ;) -- 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