Submitted by: @pcisar

Relate to CORE2709

Test case:
/* prepare */
create table tmp_tbl1 (fld1 integer, fld2 integer, fld3 integer);
create index tmp_tbl1_fld123 on tmp_tbl1(fld1, fld2, fld3);
create index tmp_tbl1_fld2 on tmp_tbl1(fld2);
commit;
set term ^;
create or alter procedure tmp_sp1
as
declare variable I integer;
begin
i=0;
while (i<10000) do begin
i=i+1;
insert into tmp_tbl1 values (1, :i, 2);
end
end
^
set term ;^
commit;
execute procedure tmp_sp1;
commit;
SET STATISTICS INDEX TMP_TBL1_FLD123;
SET STATISTICS INDEX TMP_TBL1_FLD2;
commit;

/* test */
select count(*) from tmp_tbl1 where fld1=1 and fld2 is null;

/*
1.5:
PLAN (TMP_TBL1 INDEX (TMP_TBL1_FLD2,TMP_TBL1_FLD123))
COUNT

       0

Current memory = 53311040
Delta memory = 16164
Max memory = 53467732
Elapsed time= 0.01 sec
Buffers = 6000
Reads = 0
Writes 0
Fetches = 55

2.5:
PLAN (TMP_TBL1 INDEX (TMP_TBL1_FLD123)) <<<<< different plan
COUNT

       0

Current memory = 53597056
Delta memory = 21576
Max memory = 53936000
Elapsed time= 0.01 sec
Buffers = 6000
Reads = 0
Writes 0
Fetches = 20022 <<<<<<< causes significant performance degradation when executed many times under load
*/

Under v2.5 FLD2 IS NULL predicate is either not optimized using index or is handled wrongly. Note that there are no rows with NULL value in FLD2, so there shouldn't be so much fetches. The number of fetches indicate that only fld1=1 condition is handled via index (there is only 1 value in all 10k rows).

Commits: 3f93bb8