Bug #88381 Predicate cannot be pushed down "past" window function
Submitted: 7 Nov 2017 9:10 Modified: 12 Aug 2020 1:01
Reporter: Lukas Eder Email Updates:
Status: Closed Impact on me:
None 
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
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 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] MySQL Verification Team
Hi!

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.
[12 Aug 2020 1:12] Jon Stephens
This is fixed in MySQL 8.0.22 by WL#8084.

See same for docs info.

Closed.
[12 Aug 2020 13:01] MySQL Verification Team
Thank you, Jon.