From: Jay Levitt <jay.levitt@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: Merlin Moncure <mmoncure@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Subquery in a JOIN not getting restricted?
Date: Wed, 16 Nov 2011 09:06:34 -0500
Message-ID: <4EC3C36A.3020502@gmail.com> (raw)
In-Reply-To: <8071.1320938568@sss.pgh.pa.us>
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jay.levitt@gmail.com, tgl@sss.pgh.pa.us, Kevin.Grittner@wicourts.gov, mmoncure@gmail.com
Subject: Re: Subquery in a JOIN not getting restricted?
In-Reply-To: <4EC3C36A.3020502@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox