From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jay Levitt <jay.levitt@gmail.com>
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: Thu, 10 Nov 2011 10:22:48 -0500
Message-ID: <8071.1320938568@sss.pgh.pa.us> (raw)
In-Reply-To: <4EBBD717.80507@gmail.com>
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>
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?
Don't hold your breath waiting for that to change. To do what you're
wishing for, we'd have to treat the GROUP BY subquery as if it were an
inner indexscan, and push a join condition into it. That's not even
possible today. It might be possible after I get done with the
parameterized-path stuff I've been speculating about for a couple of
years now; but I suspect that even if it is possible, we won't do it
for subqueries because of the planner-performance hit we'd take from
repeatedly replanning the same subquery.
I'd suggest rephrasing the query to do the join underneath the GROUP BY.
regards, tom lane
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: tgl@sss.pgh.pa.us, jay.levitt@gmail.com, Kevin.Grittner@wicourts.gov, mmoncure@gmail.com
Subject: Re: Subquery in a JOIN not getting restricted?
In-Reply-To: <8071.1320938568@sss.pgh.pa.us>
* 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