Skip to content

[Proposal] Push Down _conjunct of not in and not eq expr to Storage Engine  #5206

@HappenLee

Description

@HappenLee

Is your feature request related to a problem? Please describe.

case:

Now if we query like

select * from test where a not in ('a', 'b', 'c');

a is not null ('a', 'b', 'c')need push down to Storage Engine.

Test

CREATE TABLE `customer3` (
  `C_CUSTKEY` int(11) NULL COMMENT "",
  `C_NAME` varchar(64) NULL COMMENT "",
  `C_ADDRESS` varchar(64) NULL COMMENT "",
  `C_CITY` varchar(64) NULL COMMENT "",
  `C_NATION` varchar(64) NULL COMMENT "",
  `C_REGION` varchar(64) NULL COMMENT "",
  `C_PHONE` varchar(64) NULL COMMENT "",
  `C_MKTSEGMENT` varchar(64) NULL COMMENT ""
) ENGINE=OLAP
DUPLICATE KEY(`C_CUSTKEY`, `C_NAME`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`C_NATION`) BUCKETS 1
PROPERTIES (
"replication_num" = "1",
"in_memory" = "false",
"storage_format" = "V2"
);

execute query

select * from customer3 where C_NATION not in ("JAPAN", "CHINA") order by C_CUSTKEY limit 10, 15; 

not in will filter about 1000W data

the execute result as list:

origin push down not in
2.3s 0.69s

This optimization can greatly improve the query performance of Doris in some scenarios

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions