Details
-
New Feature
-
Status: In Review (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
Q2/2026 Server Development
Description
Enabling derived keys optimization for derived.col = const pushed conditions.
Estimating records per key in derived key for the optimizer based on form and/or size of components of a derived table.
Consider a derived table of the form
SELECT ..., ROW_NUMBER () OVER (PARTITION BY c1,c2 order by ...) |
FROM t1, t2, t3 ... |
WHERE ... |
If the optimizer generates a key on this derived table because of a constraint being pushed into it, it currently will not consider key components of the form column = const
We lift this constraint and add code to TABLE::add_tmp_key to search for a window function ROW_NUMBER() in our item list. From the partition list c1, c2 we can in infer an estimate of the number of rows we expect to see for each key value. The optimizer can then use this number to determine a better table join order.