Re: equivalent queries lead to different query plans for self-joins with group by?

Ben <midfield(at)gmail(dot)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