From: Stuart Rison <rison@biochemistry.ucl.ac.uk>
To: 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:58:08 +0100 (BST)
Message-ID: <Pine.LNX.4.10.9909211548510.23132-100000@bsmlx17> (raw)
In-Reply-To: <21555.937923332@sss.pgh.pa.us>
On Tue, 21 Sep 1999, Tom Lane wrote:
> 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 ;-)
I've encoutered this sort of issue myself where I just wanted the
sub-select to be performed once. Granted it would not work if you wanted
to select 10000 or 100000 but what if you have a very larged table a and a
very small table c (using the example above). As you pointed out,
currently you're looking at 'a x c' runtime... Ugh indeed; whereas just
executing the subselect once and cut and pasting that you have an order of
'a' runtime...
So would it be possible to somehow have a switch, option, function,
something that might tell then backend to execute the sub-select only
once? I know that for concurrent access databases that might mean a
dangerous loss of integrity (because the data in table c may change
between each execution of the subselect-- yes? no?) but with that caveat
in mind it would be a very useful switch!!
cheers,
S.
Stuart C. G. Rison
Department of Biochemistry and Molecular Biology
6th floor, Darwin Building, University College London (UCL)
Gower Street, London, WC1E 6BT, United Kingdom
Tel. 0207 504 2303, Fax. 0207 380 7033
e-mail: rison@biochem.ucl.ac.uk
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: 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: <Pine.LNX.4.10.9909211548510.23132-100000@bsmlx17>
References: <21555.937923332@sss.pgh.pa.us> <Pine.LNX.4.10.9909211548510.23132-100000@bsmlx17>
* 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