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 335949DCA43 for ; Fri, 16 Dec 2005 00:23:31 -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 73053-04-3 for ; Fri, 16 Dec 2005 00:23:30 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.204]) by postgresql.org (Postfix) with ESMTP id 0B9899DCA35 for ; Fri, 16 Dec 2005 00:23:28 -0400 (AST) Received: by wproxy.gmail.com with SMTP id 57so516362wri for ; Thu, 15 Dec 2005 20:23:28 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=YmzghltmzmpBawgLAemOsmbGnKY6on4xPitkeZtAGvxqpLWRL3rW4hQYvLVCrklk3aW4iMcMpCFTNiCElErUY6Utt+wVCGfVuva2AD5vQ9AcK11+MwVfJKTZxFQXS0GPle1zY9dMJqJYcni5FeKildUC9RyVZzOLYaQ+GSCB3Ps= Received: by 10.54.140.8 with SMTP id n8mr3136378wrd; Thu, 15 Dec 2005 20:23:28 -0800 (PST) Received: by 10.54.98.6 with HTTP; Thu, 15 Dec 2005 20:23:27 -0800 (PST) Message-ID: Date: Thu, 15 Dec 2005 23:23:27 -0500 From: Jaime Casanova To: "Craig A. James" Subject: Re: Overriding the optimizer Cc: pgsql-performance@postgresql.org In-Reply-To: <43A23B3E.5050809@modgraph-usa.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <200512141603.52583.blargity@gmail.com> <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> <43A23B3E.5050809@modgraph-usa.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.092 required=5 tests=[AWL=0.092] X-Spam-Score: 0.092 X-Spam-Level: X-Archive-Number: 200512/382 X-Sequence-Number: 16203 On 12/15/05, Craig A. James 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 abilit= y > > to try out other plans, and also fix that vital query that must run > > today..... > > So, to move on to the concrete... > > I'm not familiar with the innards of Postgres except in a theoretical way= . > Maybe this is a totally naive or dumb question, but I have to ask: How > hard would it be to essentially turn off the optimizer? > > 1. Evaluate WHERE clauses left-to-right. > > select ... from FOO where A and B and C; > > This would just apply the criteria left-to-right, first A, then B, then C= . > If an index was available it would use it, but only in left-to-right orde= r, > i.e. if A had no index but B did, then too bad, you should have written "= B > and A and C". > pg < 8.1 when you use multi-column indexes do exactly this... but i don't know why everyone wants this... > > 2. Evaluate joins left-to-right. > > select ... from FOO join BAR on (...) join BAZ on (...) where ... > > This would join FOO to BAR, then join the result to BAZ. The only > optimization would be to apply relevant "where" conditions to each join > before processing the next join. > using explicit INNER JOIN syntax and parenthesis > > 3. Don't flatten sub-selects > > select ... from (select ... from FOO where ...) as X where ...; > select ... from (select ... from FOO where ... offset 0) as X where ...; > This would do the inner select then use the result in the outer select, a= nd > wouldn't attempt to flatten the query. > > Thanks, > Craig > what else? -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;)