Re: 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:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Thursday, August 28, 2003 1:10 PM
> To: Thomas Beutin
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] left outer join terrible slow compared to inner
> join
>
>
> Thomas Beutin <tyrone(at)laokoon(dot)IN-Berlin(dot)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
>