[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-performance
Subject:    Re: [PERFORM] Subquery in a JOIN not getting restricted?
From:       Jay Levitt <jay.levitt () gmail ! com>
Date:       2011-11-16 14:06:34
Message-ID: 4EC3C36A.3020502 () gmail ! com
[Download RAW message or body]

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

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
[prev in list] [next in list] [prev in thread] [next in thread]