Optimizations for queries using IN-lists with tuples

Detail name	Value
Changelog Number	22813
Type	Improvement
Status	Resolved
Fix Versions	Exasol 2025.1.0
Resolution Date	2025-07-08

Background

Exasol supports IN-list filter syntax involving several columns at once, like

SELECT * FROM T WHERE (<column list>) IN ((<tuple 1>),(<tuple 2>),...,(<tuple N>))

Improvement

Several optimizations have been added to our system which improve the runtime of such queries using filters with IN-lists with tuples, e.g.

SELECT * FROM T WHERE (A,B) IN ((1,2),(3,4))

Details

The following optimizations have been added:

• Prefilter based on the columns of the tuple before applying the filter on the tuple. In the example above that would mean:

SELECT * from T WHERE A IN (1,3) AND B IN (2,4) AND (A,B) IN ((1,2), (3,4))

• Transformation of queries having filters using OR and AND clauses into queries using in-lists, where appropriate, as in the following example:

SELECT * FROM T WHERE (A=1 AND B=2) OR (A=3 AND B=4)  ==> SELECT * FROM T WHERE (A,B) IN ((1,2),(3,4))

For now, this optimization is applied only if the number of predicates (columns) in the resulting tuple is two.