Issue Details (XML | Word | Printable)

Key: CORE-5511
Type: Bug Bug
Status: Open Open
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Paweł Przybyła
Votes: 0
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Incorrect PLAN in Firebird 3

Created: 28/Mar/17 11:49 AM   Updated: 02/May/18 10:51 AM
Component/s: Engine
Affects Version/s: 3.0.2
Fix Version/s: None

File Attachments: 1. Zip Archive BACKUP.zip (6.97 MB)
2. File PLAN_TEST.exe (3.94 MB)

Environment: Windows 7, Windows 10, Windows 2012 Server
Issue Links:
Relate
 

QA Status: No test


 Description  « Hide
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)))

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Paweł Przybyła added a comment - 28/Mar/17 11:50 AM
Firebird 3.0 database for test case

ANDERSON BARRETTA added a comment - 03/Jul/17 01:01 PM
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)

Dmitry Yemanov added a comment - 20/Nov/17 11:09 AM
Pavel's issue is an obvious bug and fixed together with CORE-5659.

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

Paweł Przybyła added a comment - 02/May/18 10:51 AM
Thank you Dmitry.
You can close this issue.
I confirme that in version 3.0.3 plans are correct.