Today I found a problem with Firebird 3.0.10 which forced me to rollback all my installs to 3.0.8. 😢

The original database had 4M rows in the affected table. It took me some hours but I managed to create a smaller database with a reproducible case attached.

Sorry for the large script. I tried to make it smaller but the problem did not occurs without data. The index selectivity appears to be a factor.

Some key facts about the database structure:

• Tables T1 and T2 have a subtype relationship (T2 IS_A T1).
• Tables T1 and T3 have a 1:N relationship (T3 HAS_N T1).
• All tables have composite primary keys

The following query reproduces the problem:

SELECT
  t1.id_x,
  t1.id_t1,
  t1.total,
  t3.invoice_no,
  t3.created_at
FROM
  t1
--/*
  JOIN t2
    ON t2.id_t2 = t1.id_t1 AND
       t2.id_x = t1.id_x
--*/
  JOIN t3
    ON t3.id_t3 = t1.id_t3 AND
       t3.id_x = t1.id_x
WHERE
  t3.invoice_no = 1683998

Firebird 3.0.8 plan:

PLAN JOIN (T3 INDEX (XAK2T3), T1 INDEX (R_542), T2 INDEX (XPKT2))

Firebird 3.0.10 plan:

PLAN JOIN (T3 INDEX (XAK2T3), T2 NATURAL, T1 INDEX (XPKT1))

That T2 NATURAL is the problem. In this sample database it returns quickly but in the original database (4M rows on T2) it took more than 1 minute.

Removing the JOIN with T2 table the plan is the same in both Firebird versions:

PLAN JOIN (T3 INDEX (XAK2T3), T1 INDEX (R_542))

I hope this sample may help. I'm available for more tests or any other information needed.

Attached files:

TEST-AN.zip Sample database.
create-database-an.zip Script to build the sample database.