Re: Recursive optimization of IN subqueries

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Dennis Haney <davh(at)diku(dot)dk>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Recursive optimization of IN subqueries
Date: 2004-01-23 17:52:29
Message-ID: 27924.1074880349@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

Dennis Haney <davh(at)diku(dot)dk> writes:
> I saw it as though convert_IN_to_join rewrote the query from

> select a.* from tenk1 a where a.unique1 in
> (select c.thousand from tenk1 c where c.hundred = 99);

> to

> select a.* from tenk1 a, tenk1 c where a.unique1 = c.thousand AND
> c.hundred = 99;

> But after looking at it, I've reached the conclusion that the rewrite is
> to this instead:

> select a.* from tenk1 a, (select d.thousand from tenk1 d where
> d.hundred = 99) as c where a.unique1 = c.thousand;

Right. We do that, and then subsequently pull_up_subqueries transforms
it to the other representation. The reason for this two-step approach
is that the intermediate form is still a useful improvement if the
subquery can't be pulled up for some reason (e.g., it's got grouping).

> except the subselect is added as a range table entry instead of a
> subselect in the from-list (not that I understand this particular part,
> do you mind explaining?).

Same thing. Every entry in the from-list will have both an RTE and an
entry in the join tree. This representation is partly historical
(before we had outer joins, there was only the range table and no join
tree at all), but it is convenient for many purposes.

regards, tom lane

PS: this is a bit off-topic for pgsql-general, please pursue it on
-hackers if you have more questions.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Marco Lazzeri 2004-01-23 18:01:41 If table A value IS NULL then table B
Previous Message Dennis Haney 2004-01-23 17:09:06 Re: Recursive optimization of IN subqueries

Browse pgsql-hackers by date

  From Date Subject
Next Message Marc G. Fournier 2004-01-23 18:04:13 Re: [HACKERS] PostgreSQL installation CD based on
Previous Message Robert Bernier 2004-01-23 17:46:18 Re: [HACKERS] PostgreSQL installation CD based on