pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Bruce Momjian <maillist@candle.pha.pa.us>
To: Stuart Rison <rison@biochemistry.ucl.ac.uk>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Daniel Lopez <ridruejo@atm9.com.dtu.dk>
Cc: pgsql-sql@hub.org
Subject: Re: [SQL] Subselect performance
Date: Tue, 21 Sep 1999 15:18:24 -0400 (EDT)
Message-ID: <199909211918.PAA14232@candle.pha.pa.us> (raw)
In-Reply-To: <Pine.LNX.4.10.9909211645170.23146-100000@bsmlx17>

> Oh, OK, that's very possible.  I was always under the impression (very
> possibly missguided) that the reason it took a long time to do a "in
> (select...)" was that the sub-select was actually executed for every row
> in 'a' so that you ended up doing:
> 
> 1x sequential scan of a
> ax select on c
> 
> whereas if you did the sub-select ide[endently and cut-and-pasted the
> obtained set into the "in (...)" you were in point of fact just doing:
> 
> 1X sequential scan of a (each of them with loads of OR statements).
> 
> therefore saving "ax select" time.
> 
> Bruce, I appologise if I've completely missunderstood what's going on and
> that your e-mail was all about correcting me.  I don't have a good grasp
> of seq-scan vs. (nested-)joins vs. hash joins vs. mergejoins etc.
> (although any pointers on where to get a crash course in these would be
> greatly appreciated).  

See src/backend/optimizer/README for more info on join types.

>  
> > This is a major performance problem, one that is known, and one that
> > should be fixed, but I am sounding like a broken record.
> 
> yeah, again appologise if this has been discussed to death in the past and
> I missed it all (or it went over my head ;) )
> 
> > The solution is to allow the subquery results to be mergejoined(sorted),
> > or hashjoined with the outer query.
> 

It is not your fault.  We frequently get reports of this type, and the
behavior of the subquery is very non-intuitive.  You would think that a
subquery and a join would have the same performance, but because of the
limitation of subqueries as being nested loop joined, this is not the
case, and subqueries are slower.  We tell people to rewrite their query
as EXISTS, but by the time we tell them that, they have already spent
much time trying to figure out why the query is so slow, and I am sure
many people don't even know about the EXISTS workaround.

I hate to have a feature that is not 100% optimized, but this is what we
have with subqueries at this time.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026



view thread (8+ messages)  latest in thread

Message-ID: <199909211918.PAA14232@candle.pha.pa.us>
Permalink:  ../199909211918.PAA14232@candle.pha.pa.us/
Also on:    postgresql.org/message-id/199909211918.PAA14232@candle.pha.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: maillist@candle.pha.pa.us, rison@biochemistry.ucl.ac.uk, tgl@sss.pgh.pa.us, ridruejo@atm9.com.dtu.dk, pgsql-sql@hub.org
  Subject: Re: [SQL] Subselect performance
  In-Reply-To: <199909211918.PAA14232@candle.pha.pa.us>
  References: <Pine.LNX.4.10.9909211645170.23146-100000@bsmlx17> <199909211918.PAA14232@candle.pha.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox