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
Compute the OCT_TOTALS contents (for all customers).
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.
Inside the OCT_\TOTALS, the added condition is put into its HAVING clause, so we end up with:
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
HAVING
customer_id=1
Then, parts of HAVING clause that refer to GROUP BY columns are moved into the WHERE clause:
SELECT
customer_id,
SUM(amount) AS TOTAL_AMT
FROM orders
WHERE
order_date BETWEEN '2017-10-01' AND '2017-10-31' AND
customer_id=1
GROUP BY customer_id
Once a restriction like customer_id=1
is in the WHERE, the query optimizer can use it to construct efficient table access paths.
Controlling the Optimization
The optimization is enabled by default. One can disable it by setting the optimizer_switch
flag condition_pushdown_for_derived
to OFF.
The pushdown from HAVING to WHERE part is controlled by condition_pushdown_from_having
flag in optimizer_switch
.
From MariaDB 12.1, it is possible to enable or disable the optimization with an optimizer hint, DERIVED_CONDITION_PUSHDOWN and NO_DERIVED_CONDITION_PUSHDOWN.
See Also
Condition Pushdown through Window Functions (since MariaDB 10.3)
The Jira task for the feature is MDEV-9197.
This page is licensed: CC BY-SA / Gnu FDL
Last updated
Was this helpful?