From: Tom Lane <tgl@sss.pgh.pa.us>
To: Daniel Lopez <ridruejo@atm9.com.dtu.dk>
Cc: pgsql-sql@hub.org
Subject: Re: [SQL] Subselect performance
Date: Tue, 21 Sep 1999 10:15:32 -0400
Message-ID: <21555.937923332@sss.pgh.pa.us> (raw)
In-Reply-To: <199909201924.VAA10959@atm9.com.dtu.dk>
Daniel Lopez <ridruejo@atm9.com.dtu.dk> writes:
> $list = select d from c
> select b from a where b in ( $list )
> is 5 seconds
> select b from a where b in (select d from c)
> is 3 minutes!! (although it should be at least as fast as a)!
Not necessarily. Your first example is depending on the fact that
the "list" (number of values selected from c) is short. Try it with
10000 or 100000 values from c, if you want to see the backend crash ;-)
The second case works OK even if the sub-select result is large, because
it re-executes the sub-select for each row from a (essentially,
rescanning c to see if b is matched). However, this means the runtime
is proportional to the product of the number of rows in a and c. Ugh.
Try rewriting your query as a join:
select a.b from a, c where a.b = c.d
(you might want "select distinct" here, if b can match many rows from d).
If the system can't figure out anything better than a nested-loop join,
then it'll probably end up taking about the same amount of time, but
this form at least allows the possibility of a smarter join method.
I believe we have a TODO list item to perform this sort of
transformation automatically when the sub-select is of a form that
allows it. We need to get the left/outer join stuff working first
in order to have an exact match of the behavior.
regards, tom lane
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: tgl@sss.pgh.pa.us, ridruejo@atm9.com.dtu.dk, pgsql-sql@hub.org
Subject: Re: [SQL] Subselect performance
In-Reply-To: <21555.937923332@sss.pgh.pa.us>
References: <199909201924.VAA10959@atm9.com.dtu.dk> <21555.937923332@sss.pgh.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