New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Incorrect PLAN in Firebird 3 [CORE5511] #5780
Comments
Commented by: Paweł Przybyła (pprzybyla_abakus.net.pl) Firebird 3.0 database for test case |
Modified by: Paweł Przybyła (pprzybyla_abakus.net.pl)Attachment: PLAN_TEST.exe [ 13081 ] |
Modified by: @dyemanovassignee: Dmitry Yemanov [ dimitr ] |
Modified by: Paweł Przybyła (pprzybyla_abakus.net.pl)security: Developers [ 10012 ] => |
Commented by: ANDERSON BARRETTA (sistemas_prevedello.com.br) I have the same problem Even i recompute all statistics... i test fb 3.02 and fb 3.03 CREATE TABLE CLIENTES ( -------------------------- ALTER TABLE VENDAS ADD CONSTRAINT PK_VENDAS PRIMARY KEY (COD_VENDA); ------------------------------- table CLIENTES: 2.383 records simple query: SELECT wrong plan: PLAN JOIN (CL NATURAL, V INDEX (VENDAS_IDX_CLI, VENDAS_IDX_DATA)) i attach a backup... (file backup.zip) |
Modified by: ANDERSON BARRETTA (sistemas_prevedello.com.br)Attachment: BACKUP.zip [ 13151 ] |
Commented by: Paweł Przybyła (pprzybyla_abakus.net.pl) Thank you Dmitry. |
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)))
The text was updated successfully, but these errors were encountered: