Re: left outer join terrible slow compared to inner join
I had an opportunity to test this massive left outer join this with 7.4b2 today.
It took <3 seconds on an untuned, new install...which is MUCH better. In fact, it performed as well as Oracle 9i.
cwl
Show quoted text
-----Original Message-----
From: Clay Luther
Sent: Thursday, August 28, 2003 1:26 PM
To: 'Tom Lane'; Thomas Beutin
Cc: pgsql-general@postgresql.org
Subject: RE: [GENERAL] left outer join terrible slow compared to inner
joinActually, I was about to post some problems we have with
large left outer joins as well we've discovered in a porting
project from NT/SQL Server -> Linux/Postgres.We have a particular query that is rather large, left outer
joining across several tables. Under SQL Server, with
identical data and schema, this particular query takes 2 seconds.Under PostgreSQL, this same query takes 90 seconds -- that's
right, 90 seconds. 45x longer than SQL Server. This was
quite a shock to us (we'd not seen such a performance deficit
between the two dbs until this) and could, in fact, force us
away from Postgres.I'd be happy to forward the explain to anyone who'd care to
look at it...cwl
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, August 28, 2003 1:10 PM
To: Thomas Beutin
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] left outer join terrible slowcompared to inner
join
Thomas Beutin <tyrone@laokoon.IN-Berlin.DE> writes:
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.
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:
-> 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)Hmm, I don't understand why ot_kat_prod is being treated as
a subquery
here. It isn't a view or something is it?
regards, tom lane
---------------------------(end of
broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?