Re: left outer join terrible slow compared to inner join

From: "Clay Luther" <claycle(at)cisco(dot)com>
To: "Clay Luther" <claycle(at)cisco(dot)com>, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-general(at)postgresql(dot)org>, "Bill Forsythe" <bforsyth(at)cisco(dot)com>, "Duane Guthrie" <dguthrie(at)cisco(dot)com>, "Alan Treece" <atreece(at)cisco(dot)com>
Subject: Re: left outer join terrible slow compared to inner join
Date: 2003-09-09 19:28:58
Message-ID: F67EB38120F7BB4BB972C786095802070E33E8@ipcbu-exchange.amer.unity.cisco.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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
> >
>

Browse pgsql-general by date

  From Date Subject
Next Message Holger Marzen 2003-09-09 19:29:58 Find overlapping time intervals, how?
Previous Message Bruce Momjian 2003-09-09 19:18:09 Re: why does count take so long?