Message-ID: <4EC3C36A.3020502@gmail.com>
Date: Wed, 16 Nov 2011 09:06:34 -0500
From: Jay Levitt <jay.levitt@gmail.com>
User-Agent: Postbox 3.0.1 (Macintosh/20111102)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: Kevin Grittner <Kevin.Grittner@wicourts.gov>,
	Merlin Moncure <mmoncure@gmail.com>, pgsql-performance@postgresql.org
Subject: Re: Subquery in a JOIN not getting restricted?
References: <4EB84CB4.2010102@gmail.com>
	<4EB7FEFD0200002500042B1C@gw.wicourts.gov>
	<4EB80CAA0200002500042B3B@gw.wicourts.gov>
	<4EB86784.7080209@gmail.com> <4EB88C5A.2020704@gmail.com>
	<4EBA44B70200002500042BC8@gw.wicourts.gov>
	<CAHyXU0zWLRquK9xu4SbTrxyOTA2kbT1b3r4_H4KdEzE-hvVgoA@mail.gmail.com>
	<4EBA8C100200002500042C23@gw.wicourts.gov>
	<4EBBD717.80507@gmail.com> <8071.1320938568@sss.pgh.pa.us>
In-Reply-To: <8071.1320938568@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Tom Lane wrote:
> Jay Levitt<jay.levitt@gmail.com>  writes:
>> If the query was more like
>
>> select questions.id
>> from questions
>> join (
>>     select sum(u.id)
>>     from users as u
>>     group by u.id
>> ) as s
>> on s.id = questions.user_id
>> where questions.id = 1;
>
>> would you no longer be surprised that it scanned all user rows?
>
> I'd suggest rephrasing the query to do the join underneath the GROUP BY.

Well, my real goal is to have that inner query in a set-returning function 
that gives a computed table of other users relative to the current user, and 
then be able to JOIN that with other things and ORDER BY it:

select questions.id
from questions
join (select * from relevance(current_user)) as r
on r.id = questions.user_id
where questions.id = 1;

I assume there's no way for that function (in SQL or PL/pgSQL) to reach to 
the upper node and say "do that join again here", or force the join order 
from down below? I can't imagine how there could be, but never hurts to ask. 
  Right now, our workaround is to pass the joined target user as a function 
parameter and do the JOIN in the function, but that means we have to put the 
function in the select list, else we hit the lack of LATERAL support:

  -- This would need LATERAL

select questions.id
from questions
join (
   select * from relevance(current_user, questions.user_id)) as r
)
on r.id = questions.user_id
where questions.id = 1;

  -- This works but has lots of row-at-a-time overhead

select questions.id, (
   select * from relevance(current_user, questions.user_id)
) as r
from questions
where questions.id = 1;

Again, just checking if there's a solution I'm missing.  I know the 
optimizer is only asymptotically approaching optimal!

Jay