From: Jay Levitt <jay.levitt@gmail.com>
To: 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: Thu, 10 Nov 2011 08:52:23 -0500
Message-ID: <4EBBD717.80507@gmail.com> (raw)
In-Reply-To: <4EBA8C100200002500042C23@gw.wicourts.gov>
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>
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,
*/
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, Kevin.Grittner@wicourts.gov, mmoncure@gmail.com
Subject: Re: Subquery in a JOIN not getting restricted?
In-Reply-To: <4EBBD717.80507@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