Excessive compile time for queries with lots of LEFT JOINs

Details

Detail name Value
Changelog Number 15453
Type Bug
Status Resolved
Affected Versions Exasol 7.0.0, Exasol 7.1.0, Exasol 8.0.0, Exasol 8.1.0
Fix Versions Exasol 7.1.14, Exasol 8.6.0
Resolution Date 2022-09-21

Description

If the following conditions are fulfilled, a query may take too long to compile or run into an out of memory error:

  1. The query has multiple filter conditions in the WHERE clause that are combined with AND
  2. At least one of these conditions contains a subselect
  3. The subselect contains more than ~18 left joins (also indirectly using views). Each additional join can double the execution time.

Preparation 

CREATE SCHEMA IF NOT EXISTS s;
OPEN SCHEMA s;
create or replace table t0 (c0 int);
create or replace table t1 (c1 int);
create or replace table t2 (c2 int);
create or replace table t3 (c3 int);
create or replace table t4 (c4 int);
create or replace table t5 (c5 int);
create or replace table t6 (c6 int);
create or replace table t7 (c7 int);
create or replace table t8 (c8 int);
create or replace table t9 (c9 int);
create or replace table t10 (c10 int);
create or replace table t11 (c11 int);
create or replace table t12 (c12 int);
create or replace table t13 (c13 int);
create or replace table t14 (c14 int);
create or replace table t15 (c15 int);
create or replace table t16 (c16 int);
create or replace table t17 (c17 int);
create or replace table t18 (c18 int);
create or replace table t19 (c19 int);

create or replace view join_view as
    SELECT 'c1' as c1, 'c2' as c2
    FROM 
        t0
LEFT JOIN t1 AS t1 ON (t0.c0 = t1.c1)
LEFT JOIN t2 AS t2 ON (t1.c1 = t2.c2)
LEFT JOIN t3 AS t3 ON (t2.c2 = t3.c3)
LEFT JOIN t4 AS t4 ON (t3.c3 = t4.c4)
LEFT JOIN t5 AS t5 ON (t4.c4 = t5.c5)
LEFT JOIN t6 AS t6 ON (t5.c5 = t6.c6)
LEFT JOIN t7 AS t7 ON (t6.c6 = t7.c7)
LEFT JOIN t8 AS t8 ON (t7.c7 = t8.c8)
LEFT JOIN t9 AS t9 ON (t8.c8 = t9.c9)
LEFT JOIN t10 AS t10 ON (t9.c9 = t10.c10)
LEFT JOIN t11 AS t11 ON (t10.c10 = t11.c11)
LEFT JOIN t12 AS t12 ON (t11.c11 = t12.c12)
LEFT JOIN t13 AS t13 ON (t12.c12 = t13.c13)
LEFT JOIN t14 AS t14 ON (t13.c13 = t14.c14)
LEFT JOIN t15 AS t15 ON (t14.c14 = t15.c15)
LEFT JOIN t16 AS t16 ON (t15.c15 = t16.c16)
LEFT JOIN t17 AS t17 ON (t16.c16 = t17.c17)
LEFT JOIN t18 AS t18 ON (t17.c17 = t18.c18)
LEFT JOIN t19 AS t19 ON (t18.c18 = t19.c19);

Example
The below query takes around 6 seconds to compile

select * 
FROM s.join_view
WHERE c1 = 'c1'
    AND c2 > 'c1'
    AND c2
     IN (SELECT c2 FROM s.join_view );

 

Workaround

The AND condition must be moved to a standalone WHERE clause. To do this, move the query and all other WHERE/AND conditions into a subselect (materialized with ORDER BY FALSE). Move the problematic AND condition into a seperate WHERE clause on the outer query. For example: 


select * from
(
select * 
FROM s.join_view
WHERE c1 = 'c1'
    AND c2 > 'c1'
order by false -- This enforces materialization of the subselect and prevents the outer condition from being pushed inside
)
where c2
     IN (SELECT c2 FROM s.join_view )

Fix

The compile time of such queries is reduced.