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.19 rows=1 loops=1)
-> Materialize (cost=21900.98..21900.98 rows=12347 width=238) (actual
time=13071.53..13111.92 rows=51394 loops=1)
-> Merge Join (cost=21627.92..21900.98 rows=12347 width=238)
(actual time=11724.45..12908.46 rows=51394 loops=1)
-> Sort (cost=16815.61..16815.61 rows=6640 width=68)
(actual time=4283.02..4307.07 rows=26049 loops=1)
-> Seq Scan on o_produkt (cost=0.00..16394.06
rows=6640 width=68) (actual time=0.06..1126.96 rows=26049 loops=1)
-> Sort (cost=4812.31..4812.31 rows=40851 width=170)
(actual time=7441.36..7481.73 rows=51521 loops=1)
-> Subquery Scan pz (cost=0.00..1683.51 rows=40851
width=170) (actual time=0.14..1161.81 rows=40896 loops=1)
-> Seq Scan on o_kat_prod (cost=0.00..1683.51
rows=40851 width=170) (actual time=0.13..419.07 rows=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_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;
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.17 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.29..0.65 rows=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.24 rows=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 [EMAIL PROTECTED]
Beam me up, Scotty. There is no intelligent live down in Redmond.
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
