Am 02.01.2014 23:43, schrieb Tom Lane:
Jeff Janes <jeff.janes@xxxxxxxxx> writes:
On Thu, Jan 2, 2014 at 1:52 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
It's possible that in the specific case you exhibit here, pushing down
the clause wouldn't result in changes in the window function's output for
the selected rows, but the optimizer doesn't have enough knowledge about
window functions to determine that.
A restriction in the WHERE clause which corresponds to the PARTITION BY
should be pushable, no? I think it doesn't need to understand the internal
semantics of the window function itself, just of the PARTITION BY, which
should be doable, at least in principle.
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. In any case, it's not a "bug" that the
optimizer doesn't do this currently.
I agree, this is not a "bug" in v9.3.2 in terms of correctness.
But it's a limitation, because the query plan is by far not optimal. You
may consider this report as a feature request then.
The optimization I suggested is normally performed, when no window
function occurs in the statement.
It seems like the optimizer is already capable of doing a check if the
WHERE can be done first.
However, this check seems to be done too conservative: I guess, the
check is ignoring the PARTITION-BY-sets of attributes completely.
regards, tom lane
.
Best regards
Thomas
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
