Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
Description
derived_with_keys optimization works by generating "draft" keys for the derived temporary table and feeding them into the optimizer.
The "draft" keys can duplicate each other, as well as duplicate keys generated by other optimizations, e.g. IN-list-to-subquery optimization.
This task is about not generating draft keys that are duplicate of each other. Generate just one key and use it multiple times.
An example where one can see multiple identical keys are generated:
CREATE TABLE t1 (l_orderkey int); |
INSERT INTO t1 VALUES (1),(2); |
CREATE TABLE t2 (o_orderkey int); |
INSERT INTO t2 VALUES (3),(4); |
SET IN_PREDICATE_CONVERSION_THRESHOLD= 2; |
SET OPTIMIZER_SWITCH= 'derived_with_keys=on'; |
SELECT * FROM t1 JOIN t2 ON (l_orderkey = o_orderkey) WHERE l_orderkey IN (1, 2, 3); |
Attachments
Issue Links
- relates to
-
MDEV-26591 Is MariaDB really caching or reusing “WITH” intermediary results?
- Open
-
MDEV-30017 MariaDB optimizer significantly slower with CTE than competitive product
- Open