Re: window function induces full table scan

I wrote:
> If the restriction clause must give the same answer for any two rows of
> the same partition, then yeah, we could in principle push it down without
> knowing anything about the specific window function. It'd be a less than
> trivial test to make, I think.

On reflection, really this concern is isomorphic to whether or not it is
safe to push quals down into a SELECT DISTINCT. In principle, we should
only do that for quals that cannot distinguish values that are seen as
equal by the equality operator used for DISTINCT. For instance, the
float8 equality operator treats IEEE minus zero and plus zero as "equal",
but it's not hard to contrive a WHERE clause that can tell the difference.
Pushing such a clause down into a SELECT DISTINCT can change the results;
but we do it anyway, and have done so since the nineties, and I don't
recall hearing complaints about this.

If we wanted to be really principled about it, I think we'd have to
restrict pushdown to quals that tested subquery outputs with operators
that are members of the relevant equality operator's btree opclass.
Which would cause a lot of howls of anguish, while making things better
for a set of users that seems to be about empty.

So maybe it'd be all right to push down quals that only reference subquery
outputs that are listed in the PARTITION clauses of all window functions
in the subquery. I think that'd be a reasonably straightforward extension
of the existing tests in allpaths.c.

regards, tom lane