Peter Wright <pete@flooble.net> writes:
> [various stuff snipped]
> You say, "WHERE is defined to filter rows before application of
> aggregates", but I'd _think_ that should be interpreted to apply only
> to aggregates in the _current_ query (ie. not in sub-queries).
Well, the subtext of this discussion is that Postgres, like every other
DBMS on the planet, will aggressively push query restrictions down as
far as it's allowed to by the semantic rules. Consider
CREATE VIEW v1 AS SELECT c1, sum(c2) FROM tab GROUP BY c1;
SELECT * FROM v1 WHERE c1 = 42;
A naive implementation would compute every row of the view v1
(ie, every sum of c2 over each existing value of c1) and then
throw away each result except the one for c1 = 42. This is
obviously not acceptable. So we have to transform the query to
SELECT c1, sum(c2) FROM tab WHERE c1 = 42 GROUP BY c1;
which gives the execution engine a fair shot at doing something
reasonable, ie, pulling only the rows of tab that have c1 = 42,
which we could expect would be done with the aid of an index on c1.
(The GROUP BY step is actually redundant in this formulation,
but the cost of doing it is probably negligible; certainly it's
not the major problem compared to computing all the useless
sums over c1 groups other than 42.)
Point here is that to get reasonably efficient behavior we have to be
able to push the WHERE c1 = 42 condition down inside the view's
GROUP BY clause; and therefore we have to understand the exact
semantic conditions under which that is an allowable transformation.
Your bug report is essentially pointing out an error in our rules
for thinking that this transformation is allowable.
regards, tom lane