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

Firebird 3.0 database for test case

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)

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.

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.