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 1EAE69DCD79 for ; Fri, 16 Dec 2005 10:54:02 -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 41017-06 for ; Fri, 16 Dec 2005 10:54:05 -0400 (AST) X-Greylist: delayed 00:06:42.305935 by SQLgrey- Received: from v00051.home.net.pl (post.pl [212.85.96.51]) by postgresql.org (Postfix) with SMTP id 201D69DCD5D for ; Fri, 16 Dec 2005 10:53:58 -0400 (AST) Received: from localhost (HELO ?10.16.0.65?) (bogomips.post@home@127.0.0.1) by matrix01b.home.net.pl with SMTP; Fri, 16 Dec 2005 14:47:18 -0000 Subject: Re: Overriding the optimizer From: Tomasz Rybak To: pgsql-performance@postgresql.org In-Reply-To: <43A231AA.9070708@paradise.net.nz> References: <200512141603.52583.blargity@gmail.com> <7484.1134600423@sss.pgh.pa.us> <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> Content-Type: text/plain; charset=ISO-8859-2 Date: Fri, 16 Dec 2005 15:47:19 +0100 Message-Id: <1134744439.5124.7.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.2.3 Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200512/400 X-Sequence-Number: 16221 Dnia 16-12-2005, pią o godzinie 16:16 +1300, Mark Kirkwood napisał(a): > Craig A. James wrote: > > > > > What would be cool would be some way the developer could alter the plan, > > but they way of doing so would strongly encourage the developer to send > > the information to this mailing list. Postgres would essentially say, > > "Ok, you can do that, but we want to know why!" > > > > 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..... I think you could use SPI for that. There is function SPI_prepare, which prepares plan, and SPI_execute_plan, executing it. These functions are defined in src/backend/executor/spi.c. I think (someone please correct me if I'm wrong) you could prepare plan yourself, instead of taking it from SPI_prepare, and give it to SPI_execute_plan. SPI_prepare calls _SPI_prepare_plan, which parses query and calls pg_analyze_and_rewrite. In your version don't call this function, but provide PostgreSQL with your own plan (not-optimised according to PostrgeSQL, but meeting your criteria). -- Tomasz Rybak