Bug #88381 Predicate cannot be pushed down "past" window function
Submitted: 7 Nov 2017 9:10 Modified: 8 Feb 2018 13:39
Reporter: Lukas Eder Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:8.0.2 OS:Any
Assigned to: CPU Architecture:Any
Tags: Window functions

[7 Nov 2017 9:10] Lukas Eder
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:

    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:


Suggested fix:
https://youtu.be/uNpcKtxGtTs?t=54s :-)
[8 Nov 2017 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.
[8 Feb 2018 13:39] Sinisa Milivojevic

Thank you for your report as it is a significant one.

Since our developer has confirmed that this is a very desirable feature, I am verifying this report as the verified feature request.

Thank you.
[28 May 2018 11:43] Dag Wanvik
Posted by developer:
This relies on a well known deficiency, cf WL#8084 Condition pushdown for materialized derived tables.