Firebird 5 taking excessive amount of time to prepare a query with partial index

[todaysoftware]

The same query worked fine under Firebird 3 but FB 5.0.2 is taking a huge amount of time to prepare it, my guess is that the optimizer ends up going through some crazy number of possibilities. Basically the query is something like

SELECT SomeData FROM AUDIT WHERE
(STORENUMBER = 40 AND TILL = 2 AND TRANNO >= A1 AND TRANNO <= A2) OR
(STORENUMBER = 40 AND TILL = 3 AND TRANNO >= B1 AND TRANNO <= B2) OR
…

and so on for a bunch of till numbers, in this case it is about 20 of them so we end up with 20 OR conditions. If I adjust the query so that there is only 8 OR lines it is instant, as I start to restore the additional lines it starts to slow down with noticeable pauses, 9 lines = 1 second or so, 10 lines = 2 seconds, 11 lines = 5 seconds, 12 lines = 15, 13 lines = 37 seconds and it just keeps getting worse, seems to be approx. doubling each time. So the 20 line query is going to take an hour (I gave up after several minutes of waiting…). Note that this is the prepare phase, not the execution or retrieval phase, i.e. I have used SET PLANONLY and it still takes forever to prepare and explain the plan. The plan it generates when using just a few of the lines is as follows:

Select Expression
    -> Filter
        -> Table "AUDIT" Access By ID
            -> Bitmap Or
                -> Bitmap Or
                    -> Bitmap Or
                        -> Bitmap
                            -> Index "AUDIT_TRXID" Range Scan (lower bound: 3/4, upper bound: 3/4)
                        -> Bitmap
                            -> Index "AUDIT_TRXID" Range Scan (lower bound: 3/4, upper bound: 3/4)
                    -> Bitmap
                        -> Index "AUDIT_TRXID" Range Scan (lower bound: 3/4, upper bound: 3/4)
                -> Bitmap
                    -> Index "AUDIT_TRXID" Range Scan (lower bound: 3/4, upper bound: 3/4)

There may be a different way to structure the query and/or different join/plan strategies to try so I will update once I have tried some of these

Update 1: The problem appears to be related to a partial index on that table as I dropped the partial index and back to working instantly again. But the field on that partial index was not included in the select statement at all, so why it would effect what the optimizer was doing is a question?

Indexes:

AUDIT_TRXID (STORENUMBER, TILL, TRANNO, LINENO)
AUDIT LINETYPEORDER (LINETYPE) WHERE LINETYPE=2 OR LINETYPE=4 OR LINETYPE=5 OR 
LINETYPE=30 OR LINETYPE=31 OR LINETYPE=32 OR LINETYPE=33 OR LINETYPE=35 OR LINETYPE=36 OR 
LINETYPE=37 OR LINETYPE=38 OR LINETYPE=39 OR LINETYPE=40 OR LINETYPE=41 OR LINETYPE=42 OR 
LINETYPE=43 OR LINETYPE=44 OR LINETYPE=45 OR LINETYPE=46 OR LINETYPE=47 OR LINETYPE=50

Update 2: Re-adding the partial index with only a few OR conditions was good, once you started to get 15 or so OR conditions it started to slow down and getting quickly worse with each additional one that was added

[todaysoftware]

I have created some scripts that show the problem (which can hopefully be adapted to add into the QA system)

• 1-create-table.sql (creates the table and indexes) ~~* 2-create-data.sql (populates some data)~~
• 3-test-prepare.sql The test-prepare SQL sets PLANONLY so it is just preparing the query (i.e. not fetching the data) has the last 10 lines of the query commented out, takes ~1 second

=========================
2025-08-17 11:19:35.0170


PLAN (AUDIT INDEX (AUDIT_TRXID, AUDIT_TRXID, AUDIT_TRXID, AUDIT_TRXID, AUDIT_TRXID, AUDIT_TRXID, AUDIT_TRXID, AUDIT_TRXID, AUDIT_TRXID, AUDIT_TRXID))

=========================
2025-08-17 11:19:36.8140

Removing the comments from additional lines shows the slow down, comment out one extra line, ~5 seconds, 2 extra lines ~15 seconds (below)

=========================
2025-08-17 11:22:10.7320


PLAN (AUDIT INDEX (AUDIT_TRXID, AUDIT_TRXID, AUDIT_TRXID, AUDIT_TRXID, AUDIT_TRXID, AUDIT_TRXID, AUDIT_TRXID, AUDIT_TRXID, AUDIT_TRXID, AUDIT_TRXID, AUDIT_TRXID, AUDIT_TRXID))

=========================
2025-08-17 11:22:25.3910

The the time taken spirals out of control for each additional line added back into the query.

Removing the partial index (or changing it to no longer be partial) results in it taking basically no time to prepare any of queries

scripts.zip

Update: After a little more testing this afternoon it seems even without any data the problem is there so just trying to prepare the query on an empty table exhibits the problem, tomorrow I will try and remove all unnecessary fields/indexes etc from the scripts and post updated SQL's