Actually, I was about to post some problems we have with large left outer joins as well we've discovered in a porting
projectfrom NT/SQL Server -> Linux/Postgres.
We have a particular query that is rather large, left outer joining across several tables. Under SQL Server, with
identicaldata 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
quitea shock to us (we'd not seen such a performance deficit between the two dbs until this) and could, in fact, force
usaway 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 slow compared 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?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>