I upgrade Firebird 2.5.6 to Firebird 3.0.2.
I notice that some parts of our software slows down.
After few test I find out that Firebird 3 creates wrong plans for some statements.
I prepare test DB and generate test data (attachement). After that run it on FB 2.5 and FB 3.0 and gets diffrent plans.
SQL for test case:
SELECT SEC.ID, SEC.ID_PROD
FROM SIS_EQ_CREWS SEC
JOIN BAZASIS B ON B.ID = SEC.BSIS_ID_SAM AND B.ID_PROD = SEC.BSIS_ID_PROD_SAM
JOIN SIS_STATES SS ON SS.BSIS_ID = B.ID AND SS.BSIS_ID_PROD = B.ID_PROD
JOIN JEDNOSTKI J ON ((J.ID = B.ID_JEDN_U AND J.ID_PROD = B.ID_PROD_JEDN_U) OR
(J.ID = SS.TMP_USER_OU_ID AND J.ID_PROD = SS.TMP_USER_OU_ID_PROD))
WHERE ((J.ID = 4086) AND (J.ID_PROD = 123))
AND (SEC.REC_OWNER = GEN_ID(ID_PROD,0)) AND (SEC.F_IN_USE='Y')
AND (SEC.TRANSFER_ID IS NULL) AND (SEC.F_DELETED = 'N')
PLANS I get:
--FB 3.0.2 incorrect PLAN:
PLAN JOIN (J INDEX (PK_JEDNOSTKI), B NATURAL, SS INDEX (IDX_SIS_STATES_1), SEC INDEX (ID_SAM_IDX))
--FB 2.5.6 correct PLAN:
PLAN JOIN (J INDEX (PK_JEDNOSTKI), JOIN (SEC INDEX (SIS_EQ_CREWS_F_DEL), SS INDEX (IDX_SIS_STATES_1), B INDEX (PK_BAZASIS)))