Re: left outer join terrible slow compared to inner join

On Thu, Aug 28, 2003 at 11:42:00AM -0400, Tom Lane wrote:
> Thomas Beutin <tyrone(at)laokoon(dot)IN-Berlin(dot)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';
>
Thanks for the suggestion, but the result is close to the original outer
join without the explicit cross join but far away from the speed of the
inner join.

This uses the index o_produkt_a_id_idx on o_produkt, but the index
o_kat_prod_p_id_idx on o_kat_prod is still not used:

EXPLAIN ANALYZE SELECT DISTINCT pz.l1_id, pz.l2_id, pz.l3_id, pz.l4_id 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';
NOTICE: QUERY PLAN:

Unique (cost=2217.96..2217.98 rows=1 width=272) (actual time=6776.21..6777.17 rows=11 loops=1)
-> Sort (cost=2217.96..2217.96 rows=2 width=272) (actual time=6776.20..6776.24 rows=46 loops=1)
-> Nested Loop (cost=0.00..2217.95 rows=2 width=272) (actual time=721.82..6773.09 rows=46 loops=1)
-> Nested Loop (cost=0.00..23.80 rows=1 width=102) (actual time=0.69..1.74 rows=11 loops=1)
-> Index Scan using o_adresse_id_uidx on o_adresse (cost=0.00..5.96 rows=1 width=34) (actual time=0.29..0.31 rows=1 loops=1)
-> Index Scan using o_produkt_a_id_idx on o_produkt (cost=0.00..17.83 rows=1 width=68) (actual time=0.38..1.31 rows=11 loops=1)
-> Subquery Scan pz (cost=0.00..1683.51 rows=40851 width=170) (actual time=0.03..485.07 rows=40917 loops=11)
-> Seq Scan on o_kat_prod (cost=0.00..1683.51 rows=40851 width=170) (actual time=0.02..281.77 rows=40917 loops=11)
Total runtime: 6777.55 msec

Is there any chance to use an index on the joined table o_kat_prod?

Thanks for any hints!
-tb
--
Thomas Beutin tb(at)laokoon(dot)IN-Berlin(dot)DE
Beam me up, Scotty. There is no intelligent live down in Redmond.