Ben <midfield@xxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
