Tom Lane wrote:
Jay Levitt<jay.levitt@xxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
