Tom Mercha <mercha_t@hotmail.com> writes:
> As we know, a query goes through number of stages before it is executed.
> One of these stages is query optimization (QO).
> There are various parameters to try and influence optimizer decisions
> and costs. But I wanted to measure the effect of such a stage by turning
> it off completely and I can't find such a parameter which explicitly
> does that. Then I could execute a query to get the effect of "QO active
> and "QO inactive" and compare.
There is no such parameter because the code can't support that. For
efficiency reasons, query optimization is bound up pretty tightly with
essential plan-preparation activities. As an example, you can't turn
off constant-folding because eval_const_expressions also takes care of
some non-optional activities like filling in default parameter values
in function calls.
However, there are some knobs you can twiddle, as others have already
pointed out. Two I'd particularly draw your attention to are
join_collapse_limit and from_collapse_limit --- if you set both to 1,
that'll effectively disable searching for a good join order, causing
the join order to match the syntactic structure of the FROM clause.
For instance "FROM a,b,c" will always be done by joining a to b first
then joining to c. The code will still consider all possible ways
to do each of those joins, though you can shut off consideration of
some possibilities with parameters like enable_hashjoin.
regards, tom lane
