Sub-optimal fuzzy index lookup for empty strings in compound indices [CORE4099]

[firebird-automations]

Submitted by: @dyemanov

recreate table tab (col1 varchar(10), col2 varchar(10));
create index itab on tab (col1, col2);

execute block
as
declare cnt int = 1000;
begin
while (cnt > 0) do
begin
insert into tab (col1, col2) values (:cnt, :cnt);
cnt = cnt - 1;
end
end;

select * from tab where col1 = '' and col2 starting with trim('');
-- 1000 index reads to found zero rows

TRIM is used just to fool the optimizer so that both index segments were looked up, STARTING WITH :PARAM could be used instead. The point here is to use an empty string in all conditions (equality for leading segments and STARTING for the trailing segment).

[firebird-automations]

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]