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@postgresql.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@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
> >
>