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

> -----Original Message-----
> From: Clay Luther
> Sent: Thursday, August 28, 2003 1:26 PM
> To: 'Tom Lane'; Thomas Beutin
> Cc: pgsql-general(at)postgresql(dot)org
> 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: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
> >
>