Allow pushdown of WHERE quals into subqueries with window functions.
We can allow this even without any specific knowledge of the semantics
of the window function, so long as pushed-down quals will either accept
every row in a given window partition, or reject every such row. Because
window functions act only within a partition, such a case can't result
in changing the window functions' outputs for any surviving row.
Eliminating entire partitions in this way obviously can reduce the cost
of the window-function computations substantially.
The fly in the ointment is that it's hard to be entirely sure whether
this is true for an arbitrary qual condition. This patch allows pushdown
if (a) the qual references only partitioning columns, and (b) the qual
contains no volatile functions. We are at risk of incorrect results if
the qual can produce different answers for values that the partitioning
equality operator sees as equal. While it's not hard to invent cases
for which that can happen, it seems to seldom be a problem in practice,
since no one has complained about a similar assumption that we've had
for many years with respect to DISTINCT. The potential performance
gains seem to be worth the risk.
David Rowley, reviewed by Vik Fearing; some credit is due also to
Thomas Mayer who did considerable preliminary investigation.
Branch
------
master
Details
-------
http://git.postgresql.org/pg/commitdiff/d222585a9f7a18f2d793785c82be4c877b90c461
Modified Files
--------------
src/backend/optimizer/path/allpaths.c | 79 ++++++++++++++++++++++++++++-----
src/test/regress/expected/window.out | 41 +++++++++++++++++
src/test/regress/sql/window.sql | 20 +++++++++
3 files changed, 128 insertions(+), 12 deletions(-)