Re: Overriding the optimizer

On 12/15/05, Craig A. James <cjames(at)modgraph-usa(dot)com> 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.....
>
> 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 order,
> 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, and
> wouldn't attempt to flatten the query.
>
> Thanks,
> Craig
>

what else?

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)