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