Re: Subquery in a JOIN not getting restricted?

Tom Lane wrote:
> Jay Levitt<jay(dot)levitt(at)gmail(dot)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