From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ben <midfield@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: equivalent queries lead to different query plans for self-joins with group by?
Date: Thu, 11 Nov 2010 17:37:49 -0500
Message-ID: <23147.1289515069@sss.pgh.pa.us> (raw)
In-Reply-To: <5D061DAC-31E2-4C47-AE3B-7799118C57D3@gmail.com>
References: <5D061DAC-31E2-4C47-AE3B-7799118C57D3@gmail.com>
Ben <midfield@gmail.com> writes:
> us_quotedb=# explain select ts, id, val - aval from foo join (select ts, avg(val) as aval from foo group by ts) as a using (ts) where ts > '2010-11-11' and ts < '2010-11-13';
> on the other hand, if i specify "which" timestamp i'm restricting, it appears to do the right thing:
> us_quotedb=# explain select ts, id, val - aval from foo join (select ts, avg(val) as aval from foo group by ts) as a using (ts) where a.ts > '2010-11-11' and a.ts < '2010-11-13';
Well, arguably it's not doing the right thing either way --- you'd sort
of like the inequalities to get pushed down into both of the join
inputs, not just one of them. PG doesn't make that deduction though;
it can make such inferences for equalities, but inequalities are not
optimized as much.
The case where you have "using (ts) where ts > ..." is, I believe,
interpreted as though you'd specified the left-hand join input, ie
"using (ts) where foo.ts > ...". So the range condition is pushed
into the foo scan, which doesn't help the avg() subquery. When you
specify the restriction against "a", it's pushed into the subquery
where it's more useful.
You could try "where foo.ts > ... and a.ts > ..." but not sure if it's
really worth the trouble here, at least not if those rowcount estimates
are anywhere near accurate. If you were doing the join across quite a
lot of rows, having the constraints in both subqueries would be useful.
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, midfield@gmail.com
Subject: Re: equivalent queries lead to different query plans for self-joins with group by?
In-Reply-To: <23147.1289515069@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