Message-ID: <4EBBD717.80507@gmail.com>
Date: Thu, 10 Nov 2011 08:52:23 -0500
From: Jay Levitt <jay.levitt@gmail.com>
User-Agent: Postbox 3.0.1 (Macintosh/20111102)
MIME-Version: 1.0
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
CC: 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>
In-Reply-To: <4EBA8C100200002500042C23@gw.wicourts.gov>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Kevin Grittner wrote:
> Merlin Moncure<mmoncure@gmail.com>  wrote:
>
>> Well, this may not fit the OP's 'real' query
>
> Right, if I recall correctly, the OP said it was simplified down as
> far as it could be and still have the issue show.
>
>> but the inner subquery is probably better written as a semi-join
>> (WHERE EXISTS).

Kevin's right.  The real query involves several SQL and PL/pgsql functions 
(all now inlineable), custom aggregates, a union or two and a small coyote. 
  I could post it, but that feels like "Please write my code for me". 
Still, if you really want to...

Meanwhile, it's good for me to learn how the planner sees my queries and how 
I can best state them.  I assume this is me not understanding something 
about restrictions across group-by nodes.

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.E. is the 
"group by" a red herring, which usually wouldn't be present without an 
aggregate, and the real problem is that the planner can't restrict aggregates?

This comment in planagg.c may be relevant; I'm not doing min/max, but is it 
still true that GROUP BY always looks at all the rows, period?

void
preprocess_minmax_aggregates(PlannerInfo *root, List *tlist)
...
/* We don't handle GROUP BY or windowing, because our current
* implementations of grouping require looking at all the rows anyway,
*/