Incorrect PLAN in Firebird 3 [CORE5511]

[firebird-automations]

Submitted by: Paweł Przybyła (pprzybyla_abakus.net.pl)

Is related to CORE5659

Attachments: PLAN_TEST.exe BACKUP.zip

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 http://SEC.ID, SEC.ID_PROD FROM SIS_EQ_CREWS SEC JOIN BAZASIS B ON http://B.ID = SEC.BSIS_ID_SAM AND B.ID_PROD = SEC.BSIS_ID_PROD_SAM JOIN SIS_STATES SS ON SS.BSIS_ID = http://B.ID AND SS.BSIS_ID_PROD = B.ID_PROD JOIN JEDNOSTKI J ON ((http://J.ID = B.ID_JEDN_U AND J.ID_PROD = B.ID_PROD_JEDN_U) OR (http://J.ID = SS.TMP_USER_OU_ID AND J.ID_PROD = SS.TMP_USER_OU_ID_PROD)) WHERE ((http://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)))

[firebird-automations]

Commented by: Paweł Przybyła (pprzybyla_abakus.net.pl)

Firebird 3.0 database for test case

[firebird-automations]

Modified by: Paweł Przybyła (pprzybyla_abakus.net.pl)

Attachment: PLAN\_TEST\.exe \[ 13081 \]

[firebird-automations]

Modified by: @dyemanov

assignee: Dmitry Yemanov \[ dimitr \]

[firebird-automations]

Modified by: Paweł Przybyła (pprzybyla_abakus.net.pl)

security: Developers \[ 10012 \] =\>

[firebird-automations]

Commented by: ANDERSON BARRETTA (sistemas_prevedello.com.br)

I have the same problem Slow joins with simple queries...

Even i recompute all statistics...

i test fb 3.02 and fb 3.03

CREATE TABLE CLIENTES ( COD_CLIENTE DM_ID /* DM_ID = INTEGER NOT NULL */, NOME_CLIENTE DM_NOME /* DM_NOME = VARCHAR(60) */ ); ALTER TABLE CLIENTES ADD CONSTRAINT PK_CLIENTES PRIMARY KEY (COD_CLIENTE);

-------------------------- CREATE TABLE VENDAS ( COD_VENDA DM_ID /* DM_ID = INTEGER NOT NULL */, DATA_VENDA DM_DATA /* DM_DATA = DATE */, CODCLI_VENDA DM_ID /* DM_ID = INTEGER NOT NULL */ );

ALTER TABLE VENDAS ADD CONSTRAINT PK_VENDAS PRIMARY KEY (COD_VENDA); CREATE INDEX VENDAS_IDX_CLI ON VENDAS (CODCLI_VENDA); CREATE INDEX VENDAS_IDX_DATA ON VENDAS (DATA_VENDA);

-------------------------------

table CLIENTES: 2.383 records table VENDAS: 2.299.899 records. statistic index VENDAS_IDX_CLI : 0.0004196391091682017

simple query:

SELECT V.COD_VENDA, V.CODCLI_VENDA as CodCli, CL.NOME_CLIENTE FROM VENDAS V JOIN CLIENTES CL ON (V.CODCLI_VENDA = CL.COD_CLIENTE) where v.DATA_VENDA >= '21.06.2017'

wrong plan:

PLAN JOIN (CL NATURAL, V INDEX (VENDAS_IDX_CLI, VENDAS_IDX_DATA))

i attach a backup... (file backup.zip)

[firebird-automations]

Modified by: ANDERSON BARRETTA (sistemas_prevedello.com.br)

Attachment: BACKUP\.zip \[ 13151 \]

[firebird-automations]

Modified by: @dyemanov

Link: This issue is related to [CORE5659](https://github.com/FirebirdSQL/firebird/issues?q=CORE5659+in%3Atitle) \[ [CORE5659](https://github.com/FirebirdSQL/firebird/issues?q=CORE5659+in%3Atitle) \]

[firebird-automations]

Commented by: @dyemanov

Pavel's issue is an obvious bug and fixed together with CORE5659.

Anderson's example, however, is a completely different case, caused by different cost calculation. I'll look whether it could be improved or not.

[firebird-automations]

Commented by: Paweł Przybyła (pprzybyla_abakus.net.pl)

Thank you Dmitry. You can close this issue. I confirme that in version 3.0.3 plans are correct.