Re: Index Scans become Seq Scans after VACUUM ANALYSE

Thomas Lockhart <thomas(at)fourpalms(dot)org> writes:
> It is particularly true for "join syntax" as used in outer joins because
> *that bypasses the optimizer entirely*!!! I'd like to see that changed,
> since the choice of syntax should have no effect on performance.

For an OUTER join, we do have to join in the specified order, don't we?
(A left join B) left join C doesn't necessarily produce the same set
of rows as A left join (B left join C).

The fact that INNER JOIN syntax is currently treated the same way is
partly an artifact of implementation convenience, but also partly a
response to trouble reports we were receiving about the amount of
planning time spent on many-way joins. If we cause the planner to
treat A INNER JOIN B the same way it treats "FROM A,B WHERE", I think
we'll be back in the soup with the folks using dozen-table joins.

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.

(There already is a comparable heuristic used for deciding whether
to pull up subqueries, but its control parameter isn't separately
exposed at the moment.)

regards, tom lane