Bug #88381 Predicate cannot be pushed down "past" window function
Submitted: 7 Nov 9:10
Reporter: Lukas Eder Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.2 OS:Microsoft Windows
Assigned to:
Tags: Window functions

[7 Nov 9:10] Lukas Eder
Description:
In the current implementation, window functions act as an "optimisation fence" for the predicate pushdown transformation, even if a predicate could "obviously" (I haven't done the math) be pushed "past" the window function, as it (partially) matches the function's PARTITION BY clause.

Without this optimisation, window functions in derived tables can turn into a dangerous beast.

How to repeat:
This query here can be taken as an example, running against the Sakila database:

SELECT
  customer_id,
  payment_date,
  amount,
  cumulative_amount
FROM (
  SELECT
    customer_id,
    payment_date,
    amount,
    SUM(amount) OVER (
      PARTITION BY customer_id
      ORDER BY payment_date, payment_id
    ) cumulative_amount
  FROM payment
) inlined
WHERE customer_id IN (1, 2, 3)
AND payment_date 
  BETWEEN DATE '2005-05-25'
  AND     DATE '2005-05-29'
ORDER BY customer_id, payment_date;

Most databases are capable of pushing down the "customer_id IN (1, 2, 3)" predicate into the derived table and "past" the window function, because it does not affect the function's calculations, being a predicate on the PARTITION BY clause. If there's an index on payment(customer_id), then applying this predicate push down transformation drastically increases performance.

All of the other databases that I've tested (DB2, Oracle, PostgreSQL, and SQL Server) implement this optimisation. Note that in principle, the range predicate could also be pushed down partially, but none of the databases I've tested do that. More details in this blog post:

https://blog.jooq.org/2017/11/06/how-to-avoid-excessive-sorts-in-window-functions/

Suggested fix:
https://youtu.be/uNpcKtxGtTs?t=54s :-)
[8 Nov 9:39] Dag Wanvik
Hi Lukas, your observation is correct, and we know about this limitation.
Obviously, it something we'd like to improve on.