Re: left outer join terrible slow compared to inner join

Thomas Beutin <tyrone@laokoon.IN-Berlin.DE> writes:
> i've a speed problem withe the following statement:
> SELECT DISTINCT pz.l1_id, pz.l2_id, pz.l3_id, pz.l4_id
> FROM ot_adresse AS a, ot_produkt AS p
> LEFT OUTER JOIN  ot_kat_prod AS pz ON ( p.p_id = pz.p_id )
> WHERE  p.a_id = a.id AND a.id = '105391105424941' AND a.m_id = '37';

In 7.2 (and 7.3), this syntax forces the planner to join ot_produkt to
ot_kat_prod first, which is terribly inefficient because the WHERE
constraints don't constrain that join at all.  You could work around
this by writing instead

 FROM (ot_adresse AS a CROSS JOIN ot_produkt AS p)
 LEFT OUTER JOIN  ot_kat_prod AS pz ON ( p.p_id = pz.p_id )
 WHERE  p.a_id = a.id AND a.id = '105391105424941' AND a.m_id = '37';

See http://www.postgresql.org/docs/7.3/static/explicit-joins.html
for discussion.  (Note: 7.4 will be less rigid about this issue.)

            regards, tom lane