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

Louis-David Mitterrand <vindex(at)apartia(dot)org> writes:
> gesci5=# explain analyse select p1.titre, p1.nom, p1.prenom, p2.titre, p2.nom, p2.prenom from personne p1 join prospect p on (p.id_personne1 = p1.id_personne) join personne p2 on (p.id_personne2 = p2.id_personne) join contact cn on (p.dernier_contact = cn.id_contact) where lower(p2.nom) like 'marl%' order by date_contact desc;

> gesci5=# explain analyse select p1.titre, p1.nom, p1.prenom, p2.titre, p2.nom, p2.prenom from personne p1 join prospect p on (p.id_personne1 = p1.id_personne) join personne p2 on (p.id_personne2 = p2.id_personne) join contact cn on (p.dernier_contact = cn.id_contact) where lower(p1.nom) like 'marl%' order by date_contact desc;

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 ;-)

The EXPLAINs also remind me that we don't currently have any statistics
that can be applied for clauses like "lower(p2.nom) like 'marl%'".
We've talked in the past about having the system gather and use stats
on the values of functional indexes --- for example, if you have an
index on lower(p2.nom) then this would allow a rational estimate to be
made about the selectivity of "lower(p2.nom) like 'marl%'". But I
haven't had any time to pursue it myself. Anyway it doesn't appear
that that's causing a bad choice of plan in this case.

regards, tom lane