[MDEV-30312] Improvement: make derived_with_keys not generate identical draft keys Created: 2022-12-28  Updated: 2023-07-06

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Fix Version/s: None

Type: Task Priority: Major
Reporter: Sergei Petrunia Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-26591 Is MariaDB really caching or reusing ... Open
relates to MDEV-30017 MariaDB optimizer significantly slowe... Open

 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);



 Comments   
Comment by Hari Hara Naveen S [ 2022-12-30 ]

Is anyone working on this improvement right now? I interested in helping out.

I have tried to read into the documentation to better understand the enhancement requested. From my understanding every time we create a derived temporary table we seem to be building an index on it, and we wish to build the index only once and try to use it multiple times.

I can also start working on this improvement if someone could link a couple resources or guide me on how I can start implementing it.

Generated at Thu Feb 08 10:15:18 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.