Re: huge runtime difference between 2 almost identical queries (was: Re: Index Scans become Seq Scans after VACUUM ANALYSE)

On Wed, Apr 17, 2002 at 10:38:15AM -0400, Tom Lane wrote:
>
> But these aren't at *all* the same query --- the useful constraint is on
> p2 in the first case, and p1 in the second. Given the way you've
> written the join, the constraint on p2 can't be applied until after
> the p1/p join is formed --- see
> http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/explicit-joins.html
>
> I've always thought of the follow-the-join-structure rule as a stopgap
> measure until we think of something better; it's not intuitive that
> writing queries using INNER JOIN/ON isn't equivalent to writing FROM/WHERE.
> On the other hand it provides a useful "out" for those people who are
> joining umpteen tables and need to short-circuit the planner's search
> heuristics. If I take it out, I'll get beat up by the same camp that
> thinks they should be able to override the planner's ideas about whether
> to use an index ;-)

Hmm, since 7.1 released we have religiously converted all our joins to
the new syntax, thinking it more politically correct ;-). But now all
our beliefs are put into question. Back to old joins, in certain cases.

Here is the rule of thumb we deduct from your message: only use explicit
join syntax if a left|right|full join is involved OR if the
conditional(s) can go into the ON() clause, ELSE use the old join
syntax.

Is that more or less correct?

Preliminary tests converting the query I previously sent you to the old
syntax are indeed very impressive: now in both cases (comparaison on p1
or p2 take ~ 1ms).

THANKS A LOT FOR THE HEADS UP!

--
THESEE: Il fallait, en fuyant, ne pas abandonner
Le fer qui dans ses mains aide à te condamner ;
(Phèdre, J-B Racine, acte 4, scène 2)