left outer join terrible slow compared to inner join - Mailing list pgsql-general

Hi,

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

This is terrible slow compared to the inner join:
SELECT DISTINCT pz.l1_id, pz.l2_id, pz.l3_id, pz.l4_id
FROM ot_adresse AS a, ot_produkt AS p, ot_kat_prod AS pz
WHERE  p.a_id = a.id AND a.id = '105391105424941' AND a.m_id = '37'
AND p.p_id = pz.p_id;

These are the EXPLAIN ANALYZE output of both statements on
postgres 7.2.4:

EXPLAIN ANALYZE 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'; 
NOTICE:  QUERY PLAN:

Unique  (cost=22061.28..22061.30 rows=1 width=272) (actual time=13332.01..13332.97 rows=11 loops=1)
  ->  Sort  (cost=22061.28..22061.28 rows=2 width=272) (actual time=13332.00..13332.03 rows=46 loops=1)
        ->  Nested Loop  (cost=21627.92..22061.27 rows=2 width=272) (actual time=13303.51..13328.98 rows=46 loops=1)
              ->  Index Scan using o_adresse_id_uidx on o_adresse  (cost=0.00..5.96 rows=1 width=34) (actual
time=0.16..0.19rows=1 loops=1) 
              ->  Materialize  (cost=21900.98..21900.98 rows=12347 width=238) (actual time=13071.53..13111.92
rows=51394loops=1) 
                    ->  Merge Join  (cost=21627.92..21900.98 rows=12347 width=238) (actual time=11724.45..12908.46
rows=51394loops=1) 
                          ->  Sort  (cost=16815.61..16815.61 rows=6640 width=68) (actual time=4283.02..4307.07
rows=26049loops=1) 
                                ->  Seq Scan on o_produkt  (cost=0.00..16394.06 rows=6640 width=68) (actual
time=0.06..1126.96rows=26049 loops=1) 
                          ->  Sort  (cost=4812.31..4812.31 rows=40851 width=170) (actual time=7441.36..7481.73
rows=51521loops=1) 
                                ->  Subquery Scan pz  (cost=0.00..1683.51 rows=40851 width=170) (actual
time=0.14..1161.81rows=40896 loops=1) 
                                      ->  Seq Scan on o_kat_prod  (cost=0.00..1683.51 rows=40851 width=170) (actual
time=0.13..419.07rows=40896 loops=1) 
Total runtime: 13377.02 msec

EXPLAIN ANALYZE SELECT DISTINCT pz.l1_id, pz.l2_id, pz.l3_id, pz.l4_id FROM ot_adresse AS a, ot_produkt AS p,
ot_kat_prodAS pz WHERE  p.a_id = a.id AND a.id = '105391105424941' AND a.m_id = '37' AND p.p_id = pz.p_id; 
NOTICE:  QUERY PLAN:

Unique  (cost=41.29..41.31 rows=1 width=272) (actual time=6.67..7.64 rows=11 loops=1)
  ->  Sort  (cost=41.29..41.29 rows=2 width=272) (actual time=6.67..6.71 rows=46 loops=1)
        ->  Nested Loop  (cost=0.00..41.28 rows=2 width=272) (actual time=0.68..3.73 rows=46 loops=1)
              ->  Nested Loop  (cost=0.00..23.80 rows=1 width=102) (actual time=0.46..0.87 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.16..0.17rows=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.29..0.65rows=11 loops=1) 
              ->  Index Scan using o_kat_prod_p_id_idx on o_kat_prod  (cost=0.00..17.42 rows=5 width=170) (actual
time=0.16..0.24rows=4 loops=11) 
Total runtime: 7.96 msec

Do i've any chance to get the indexes used in the OUTER JOIN?

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