Condition Pushdown into Derived Table Optimization

If a query uses a derived table (or a view), the first action that the query optimizer will attempt is to apply the derived-table-merge-optimization and merge the derived table into its parent select. However, that optimization is only applicable when the select inside the derived table has a join as the top-level operation. If it has a GROUP-BY, DISTINCT, or uses window functions, then derived-table-merge-optimization is not applicable.

In that case, the Condition Pushdown optimization is applicable.

Introduction to Condition Pushdown

Consider an example

CREATE VIEW OCT_TOTALS AS
SELECT
  customer_id,
  SUM(amount) AS TOTAL_AMT
FROM orders
WHERE  order_date BETWEEN '2017-10-01' AND '2017-10-31'
GROUP BY customer_id;

SELECT * FROM OCT_TOTALS WHERE customer_id=1

The naive way to execute the above is to

1. Compute the OCT_TOTALS contents (for all customers).

2. The, select the line with customer_id=1

This is obviously inefficient, if there are 1000 customers, then one will be doing up to 1000 times more work than necessary.

However, the optimizer can take the condition customer_id=1 and push it down into the OCT_TOTALS view.

(TODO: elaborate here)

Condition Pushdown Properties

• Condition Pushdown has been available since MariaDB 10.2.

• The Jira task for it was MDEV-9197.

• The optimization is enabled by default. One can disable it by setting @@optimizer_switch flag condition_pushdown_for_derived to OFF.

See Also

• Condition Pushdown through Window Functions (since MariaDB 10.3)

• Condition Pushdown into IN Subqueries (since MariaDB 10.4)

_{This page is licensed: CC BY-SA / Gnu FDL}