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 > -----Original Message----- > From: Clay Luther > Sent: Thursday, August 28, 2003 1:26 PM > To: 'Tom Lane'; Thomas Beutin > Cc: [EMAIL PROTECTED] > Subject: RE: [GENERAL] left outer join terrible slow compared to inner > join > > > Actually, 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:[EMAIL PROTECTED] > > Sent: Thursday, August 28, 2003 1:10 PM > > To: Thomas Beutin > > Cc: [EMAIL PROTECTED] > > Subject: Re: [GENERAL] left outer join terrible slow > compared to inner > > join > > > > > > Thomas Beutin <[EMAIL PROTECTED]> 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? > > > > http://www.postgresql.org/docs/faqs/FAQ.html > > > ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
