Skip site navigation (1) Skip section navigation (2)

Re: Recursive optimization of IN subqueries

From: "Simon Riggs" <simon(at)2ndquadrant(dot)com>
To: "'Tom Lane'" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "'Dennis Haney'" <davh(at)diku(dot)dk>
Cc: <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Recursive optimization of IN subqueries
Date: 2004-01-27 14:13:28
Message-ID: 002501c3e4df$be91c510$5e00030a@LaptopDellXP (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers
> Tom Lane writes
> 
> In the second place, what the code is doing is dependent on an
> understanding
> of the semantics of IN; I'm not sure it's applicable to, say,
> 	WHERE outervar > ANY (SELECT innervar FROM ...)
> and it's definitely not applicable to
> 	WHERE outervar > ALL (SELECT innervar FROM ...)
> In particular, the optimization paths that involve unique-ifying the
> subselect output and then using it as the outer side of a join would
> definitely not work for these sorts of things.
> 

I'm not sure if I've understood you correctly in the section above. Are
you saying that these types of queries don't have a meaningful or
defined response? Or just that they wouldn't be very well optimized as a
result of the unique-ifying code changes? Or have I just mis-read the
thread...

My understanding is that in ANSI SQL99, the expression 
	expression > ALL (subquery) 

- is TRUE when expression is greater than every value in the set
of values returned by subquery. 
- is TRUE if subquery returns no values.

The expression 
	expression > ANY (subquery) 

- is TRUE when expression is greater than at least one value of
the set of values returned by subquery.
- is FALSE if subsquery returns no values.

(As supported by Oracle 9iv2 and Teradata v2r5.0.)

Best regards, Simon


In response to

Responses

pgsql-hackers by date

Next:From: Dave CramerDate: 2004-01-27 14:21:55
Subject: building plperl on 7.4.1
Previous:From: Simon RiggsDate: 2004-01-27 14:12:27
Subject: Re: Recursive optimization of IN subqueries

pgsql-general by date

Next:From: Dennis HaneyDate: 2004-01-27 14:32:54
Subject: Re: Recursive optimization of IN subqueries
Previous:From: Simon RiggsDate: 2004-01-27 14:12:27
Subject: Re: Recursive optimization of IN subqueries

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group