Re: [SQL] Subselect performance

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