Re: Measuring the Query Optimizer Effect: Turning off the QO?

>>>>> "Tom" == Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

Tom> Two I'd particularly draw your attention to are
Tom> join_collapse_limit and from_collapse_limit --- if you set both to
Tom> 1, that'll effectively disable searching for a good join order,
Tom> causing the join order to match the syntactic structure of the
Tom> FROM clause. For instance "FROM a,b,c" will always be done by
Tom> joining a to b first

FROM a,b,c can always be planned in any join order. If you want to force
the join order you have to set join_collapse_limit=1 AND write it as
FROM a JOIN b ON ... JOIN c ON ...

For an example, try:

explain select * from onek o1, tenk1 t, onek o2
where o1.unique1=t.unique1 and t.unique1=o2.unique1
and o1.unique2<10 and o2.unique2<10;

which (at least for me) joins o1 and o2 together first even with the
collapse limits set to 1.

--
Andrew (irc:RhodiumToad)