Performance improvement of string indices

Detail name	Value
Changelog Number	400
Type	Improvement
Status	Open

Improvement

This change improves the performance of joins on string columns, as well as certain string filter operations on columns with an existing index.

Example

create table character_set as select chr(v) c from values between 1 and 100 as t(v);

-- Creates 100'000'000 rows.
create table t1(a char(2000));
insert into t1 (select s1.c || s2.c || s3.c || s4.c from character_set s1, character_set s2, character_set s3, character_set s4);

-- Creates 500'000'000 rows.
create table t2(b char(2000));
insert into t2 (select a from t1);
insert into t2 (select a from t1);
insert into t2 (select a from t1);
insert into t2 (select a from t1);
insert into t2 (select a from t1);

enforce global index on t2(b);

-- The performance of this join is considerably improved.
select count(*) from t1 join t2 on a = b;

-- Performance of the following filter also improves because the index is used.
select count(*) from t2 where b = rpad('abcd', 2001, ' ');

Potential drawbacks

Joining two tables distributed on CHAR columns of different lengths now results in a global join instead of a local join. This behavior differs from previous releases and may affect query performance in such cases.

Changed behavior

1. During the first database startup after updating to this version, all existing string indexes are automatically rebuilt, and all distributed tables that use one or more string columns as distribution keys are automatically redistributed. For large databases, this process can take a high amount of time. If you require an estimate of the migration time, please contact Exasol Support before updating. 2. Joining two tables distributed on CHAR columns of different lengths now results in a global join instead of a local join. This behavior differs from previous releases and may affect query performance in such cases.