> Would it make sense to flatten out INNER JOINs only when the total
> number of tables involved is less than some parameter N? N
> around six or eight would probably keep the complex-query crowd
> happy, while not causing unintuitive behavior for simple queries.
> Anybody who really likes the current behavior could set N=1 to force
> the system to obey his join order.
I'd like to see the "reorder, or not to reorder" to happen as a settable
parameter, *not* as a side effect of choosing a particular
should-be-equivalent syntax for a query.
If that were exposed, then folks could have additional control over the
optimizer no matter what syntax they prefer to use. And in fact could
alter the behavior without having to completely rewrite their query.
One could also think about a threshold mechanism as you mention above,
but istm that allowing explicit control over reordering (fundamentally
different than, say, control over whether particular kinds of scans are
used) is the best first step. Not solely continuing to hide that control
behind heuristics involving query style and numbers of tables.
- Thomas