Firebird performance issue - unnecessary index reads

[EPluribusUnum]

Hi *!

SELECT FIRST 1 1 FROM fk_tetel WHERE szla = '361'
--PLAN (FK_TETEL INDEX (FK_TETEL_FKISZ_ID_SZLA_FK))

returns no row
No "Enchanced Info" (IBExpert) because no read at all.
This is the expected behaviour

SELECT FIRST 1 1 FROM fk_tetel WHERE szla = '3611'
--PLAN (FK_TETEL INDEX (FK_TETEL_FKISZ_ID_SZLA_FK))--same plan

also returns no row, BUT "Enchanced Info" reports 6227 index red on FK_TETEL
Where does the index reads come from and why? It should be 0 Index reads also in this case.
(Firebird 3.0.10, x64, Windows)

I uploaded the database to FTP and will the login details in DM on request.

Thank you!

[EPluribusUnum]

This extra reads happens for many different values, not just the reported example value.
Also this condition in our case could be inside nested loops so the extra reads quickly blows up and hits us very hard adding many millons unnecessary record reads.

[hvlad]

Send it to me, please

[EPluribusUnum]

@hvlad , details sent.

[hvlad]

Reproduced, thanks.

The problem happens when:

• search key length is multiply of 4
• there is records with key field values length greater than search key length
• the index is multi-segment
• the index match is partial, i.e. some last segments is not used.

The issue is not a regression, it should be present in previous Firebird versions.
The simplest workaround (until proper solution not found) is to use single-segment index.

[EPluribusUnum]

Thanks. Hoping for a fast fix 🙏 . I don't think we will put extra (duplicate) indicies to tables beacause of this. (The multi segment index in this case is came from a foreign key, can't drop it)

[hvlad]

FYI: I have a fix for this case but it doesn't handle descending indices so far. Working on it.

[EPluribusUnum]

@hvlad , the latest snapshot (03.31) does not contain this fix? I'll run my tests and also yours, and still makes extra reads, and sometimes neve return recors when it should be.

[hvlad]

I've created pull request #7521 and awaiting for comments from other devs.

[hvlad]

The pull request is merged, please check next snapshot build.

[EPluribusUnum]

@hvlad , checked, it OK. Thank you!

[hvlad]

Re-opened for forward porting