Skip to content
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 using in Firebird 3 making it slow [CORE5310] #5587

Open
firebird-automations opened this issue Jul 14, 2016 · 8 comments
Open

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Claudio Luiz Vechini (clvechini)

Attachments:
TESTE-FB25.TXT
TESTE-FB30.TXT

Votes: 8

This a SQL example in the table TESTE_DB, on the firebird 2.0 and 2.5 it´s
use the plan TESTE_DB_IDX2 that is the perfect way, however in the Firebird 3 (3.0.1.32556)
it use the plan TESTE_DB_IDX1 and like my original table have a lot of registers it be extremaly slow.

Segue um exemplo de uma SQL na tabela TESTE_DB, no firebird 2.0 e 2.5 ele
usa o plano TESTE_DB_IDX2 que é o ideal, mas no firebird 3 (3.0.1.32556) ele
usa o plano TESTE_DB_IDX1 e como minha tabela original tem muitos registro
ele fica extremamente lento.

- firebird 2.5

SELECT DOCUMENTO,SUM(PEDIDO_QUANTIDADE)
FROM TESTE_DB
WHERE (EMPRESA = '001') AND
(PEDIDO_NUMERO = 'TESTE') AND
(PEDIDO_ITEM = 1) AND
(PEDIDO_QUANTIDADE > 0)
GROUP BY 1
ORDER BY 1

Explain plan
-> Table "TESTE_DB" Access By ID
-> Index "TESTE_DB_IDX2"

- firebird 3 sem especificar o PLAN

- firebird 3 without to specify the PLAN

SELECT DOCUMENTO,SUM(PEDIDO_QUANTIDADE)
FROM TESTE_DB
WHERE (EMPRESA = '001') AND
(PEDIDO_NUMERO = 'TESTE') AND
(PEDIDO_ITEM = 1) AND
(PEDIDO_QUANTIDADE > 0)
GROUP BY 1
ORDER BY 1

Explain plan
Select Expression
-> Aggregate
-> Filter
-> Table "TESTE_DB" Access By ID
-> Index "TESTE_DB_IDX1" Range Scan (partial match: 1/3)

- firebird 3 especificando o PLAN

- firebird 3 specifying the PLAN

SELECT DOCUMENTO,SUM(PEDIDO_QUANTIDADE)
FROM TESTE_DB
WHERE (EMPRESA = '001') AND
(PEDIDO_NUMERO = 'TESTE') AND
(PEDIDO_ITEM = 1) AND
(PEDIDO_QUANTIDADE > 0)
GROUP BY 1

   PLAN \(TESTE\_DB INDEX \(TESTE\_DB\_IDX2\)\)

   ORDER BY 1

Explain plan
Select Expression
-> Aggregate
-> Sort (record length: 108, key length: 20)
-> Filter
-> Table "TESTE_DB" Access By ID
-> Bitmap
-> Index "TESTE_DB_IDX2" Range Scan (full match)

/**************************************************************************
****/
/**** Tables
****/
/**************************************************************************
****/

CREATE TABLE TESTE_DB (
EMPRESA VARCHAR(5),
DOCUMENTO VARCHAR(15),
ITEM INTEGER,
PEDIDO_NUMERO VARCHAR(12),
PEDIDO_ITEM INTEGER,
PEDIDO_QUANTIDADE DOUBLE PRECISION);

/**************************************************************************
****/
/**** Indices
****/
/**************************************************************************
****/

CREATE UNIQUE INDEX TESTE_DB_IDX1 ON TESTE_DB (EMPRESA, DOCUMENTO, ITEM);
CREATE INDEX TESTE_DB_IDX2 ON TESTE_DB (EMPRESA, PEDIDO_NUMERO,
PEDIDO_ITEM);

INSERT INTO TESTE_DB (EMPRESA, DOCUMENTO, ITEM, PEDIDO_NUMERO, PEDIDO_ITEM,
PEDIDO_QUANTIDADE)
VALUES ('001', '201502.0001', 1, 'TESTE', 1, 30);
INSERT INTO TESTE_DB (EMPRESA, DOCUMENTO, ITEM, PEDIDO_NUMERO, PEDIDO_ITEM,
PEDIDO_QUANTIDADE)
VALUES ('001', '201502.0002', 2, 'TESTE', 2, 30);
INSERT INTO TESTE_DB (EMPRESA, DOCUMENTO, ITEM, PEDIDO_NUMERO, PEDIDO_ITEM,
PEDIDO_QUANTIDADE)
VALUES ('001', '201502.0003', 3, 'TESTE', 3, 30);
INSERT INTO TESTE_DB (EMPRESA, DOCUMENTO, ITEM, PEDIDO_NUMERO, PEDIDO_ITEM,
PEDIDO_QUANTIDADE)
VALUES ('001', '201502.0004', 4, 'TESTE', 4, 30);
INSERT INTO TESTE_DB (EMPRESA, DOCUMENTO, ITEM, PEDIDO_NUMERO, PEDIDO_ITEM,
PEDIDO_QUANTIDADE)
VALUES ('001', '201502.0005', 5, 'TESTE', 5, 30);
INSERT INTO TESTE_DB (EMPRESA, DOCUMENTO, ITEM, PEDIDO_NUMERO, PEDIDO_ITEM,
PEDIDO_QUANTIDADE)
VALUES ('001', '201502.0006', 6, 'TESTE', 6, 30);
INSERT INTO TESTE_DB (EMPRESA, DOCUMENTO, ITEM, PEDIDO_NUMERO, PEDIDO_ITEM,
PEDIDO_QUANTIDADE)
VALUES ('001', '201502.0007', 7, 'TESTE', 7, 30);
INSERT INTO TESTE_DB (EMPRESA, DOCUMENTO, ITEM, PEDIDO_NUMERO, PEDIDO_ITEM,
PEDIDO_QUANTIDADE)
VALUES ('001', '201502.0008', 8, 'TESTE', 8, 30);
INSERT INTO TESTE_DB (EMPRESA, DOCUMENTO, ITEM, PEDIDO_NUMERO, PEDIDO_ITEM,
PEDIDO_QUANTIDADE)
VALUES ('001', '201502.0009', 9, 'TESTE', 9, 30);
INSERT INTO TESTE_DB (EMPRESA, DOCUMENTO, ITEM, PEDIDO_NUMERO, PEDIDO_ITEM,
PEDIDO_QUANTIDADE)
VALUES ('001', '201502.0010', 10, 'TESTE', 10, 30);
INSERT INTO TESTE_DB (EMPRESA, DOCUMENTO, ITEM, PEDIDO_NUMERO, PEDIDO_ITEM,
PEDIDO_QUANTIDADE)
VALUES ('001', '201502.0011', 11, 'TESTE', 11, 20);
INSERT INTO TESTE_DB (EMPRESA, DOCUMENTO, ITEM, PEDIDO_NUMERO, PEDIDO_ITEM,
PEDIDO_QUANTIDADE)
VALUES ('001', '201502.0012', 12, 'TESTE', 12, 20);
INSERT INTO TESTE_DB (EMPRESA, DOCUMENTO, ITEM, PEDIDO_NUMERO, PEDIDO_ITEM,
PEDIDO_QUANTIDADE)
VALUES ('001', '201502.0013', 13, 'TESTE', 13, 20);
INSERT INTO TESTE_DB (EMPRESA, DOCUMENTO, ITEM, PEDIDO_NUMERO, PEDIDO_ITEM,
PEDIDO_QUANTIDADE)
VALUES ('001', '201502.0014', 14, 'TESTE', 14, 20);
INSERT INTO TESTE_DB (EMPRESA, DOCUMENTO, ITEM, PEDIDO_NUMERO, PEDIDO_ITEM,
PEDIDO_QUANTIDADE)
VALUES ('001', '201502.0015', 15, 'TESTE', 15, 20);
INSERT INTO TESTE_DB (EMPRESA, DOCUMENTO, ITEM, PEDIDO_NUMERO, PEDIDO_ITEM,
PEDIDO_QUANTIDADE)
VALUES ('001', '201502.0016', 16, 'TESTE', 16, 20);
INSERT INTO TESTE_DB (EMPRESA, DOCUMENTO, ITEM, PEDIDO_NUMERO, PEDIDO_ITEM,
PEDIDO_QUANTIDADE)
VALUES ('001', '201502.0018', 18, 'TESTE', 18, 20);
INSERT INTO TESTE_DB (EMPRESA, DOCUMENTO, ITEM, PEDIDO_NUMERO, PEDIDO_ITEM,
PEDIDO_QUANTIDADE)
VALUES ('001', '201502.0019', 19, 'TESTE', 19, 20);
INSERT INTO TESTE_DB (EMPRESA, DOCUMENTO, ITEM, PEDIDO_NUMERO, PEDIDO_ITEM,
PEDIDO_QUANTIDADE)
VALUES ('001', '201502.0020', 20, 'TESTE', 20, 5);
INSERT INTO TESTE_DB (EMPRESA, DOCUMENTO, ITEM, PEDIDO_NUMERO, PEDIDO_ITEM,
PEDIDO_QUANTIDADE)
VALUES ('001', '201502.0021', 21, 'TESTE', 21, 5);
INSERT INTO TESTE_DB (EMPRESA, DOCUMENTO, ITEM, PEDIDO_NUMERO, PEDIDO_ITEM,
PEDIDO_QUANTIDADE)
VALUES ('001', '201502.0022', 22, 'TESTE', 22, 5);
INSERT INTO TESTE_DB (EMPRESA, DOCUMENTO, ITEM, PEDIDO_NUMERO, PEDIDO_ITEM,
PEDIDO_QUANTIDADE)
VALUES ('001', '201502.0023', 23, 'TESTE', 23, 5);
INSERT INTO TESTE_DB (EMPRESA, DOCUMENTO, ITEM, PEDIDO_NUMERO, PEDIDO_ITEM,
PEDIDO_QUANTIDADE)
VALUES ('001', '201502.0024', 24, 'TESTE', 24, 5);
INSERT INTO TESTE_DB (EMPRESA, DOCUMENTO, ITEM, PEDIDO_NUMERO, PEDIDO_ITEM,
PEDIDO_QUANTIDADE)
VALUES ('001', '201502.0025', 25, 'TESTE', 25, 5);
INSERT INTO TESTE_DB (EMPRESA, DOCUMENTO, ITEM, PEDIDO_NUMERO, PEDIDO_ITEM,
PEDIDO_QUANTIDADE)
VALUES ('001', '201502.0026', 26, 'TESTE', 26, 10);
INSERT INTO TESTE_DB (EMPRESA, DOCUMENTO, ITEM, PEDIDO_NUMERO, PEDIDO_ITEM,
PEDIDO_QUANTIDADE)
VALUES ('001', '201502.0028', 28, 'TESTE', 28, 10);
INSERT INTO TESTE_DB (EMPRESA, DOCUMENTO, ITEM, PEDIDO_NUMERO, PEDIDO_ITEM,
PEDIDO_QUANTIDADE)
VALUES ('001', '201502.0029', 29, 'TESTE', 29, 10);
INSERT INTO TESTE_DB (EMPRESA, DOCUMENTO, ITEM, PEDIDO_NUMERO, PEDIDO_ITEM,
PEDIDO_QUANTIDADE)
VALUES ('001', '201502.0032', 32, 'TESTE', 32, 10);
INSERT INTO TESTE_DB (EMPRESA, DOCUMENTO, ITEM, PEDIDO_NUMERO, PEDIDO_ITEM,
PEDIDO_QUANTIDADE)
VALUES ('001', '201502.0034', 34, 'TESTE', 34, 10);
INSERT INTO TESTE_DB (EMPRESA, DOCUMENTO, ITEM, PEDIDO_NUMERO, PEDIDO_ITEM,
PEDIDO_QUANTIDADE)
VALUES ('001', '201502.0035', 35, 'TESTE', 35, 10);

COMMIT WORK;

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

Please provide details on the query runtimes for v2.x and v3.x.

Also, please ensure that your test use an environment that is of a consistent "state" -- i.e. same OS, same FB engine 'bitness', post computer restart (clean OS file/page cache), freshly opened database (clean FB page cache) and same Firebird settings.

@firebird-automations
Copy link
Collaborator Author

Commented by: Claudio Luiz Vechini (clvechini)

Same computer, details attached.

@firebird-automations
Copy link
Collaborator Author

Modified by: Claudio Luiz Vechini (clvechini)

Attachment: TESTE-FB25.TXT [ 12990 ]

Attachment: TESTE-FB30.TXT [ 12991 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: Paquito Ines (paquito)

Attachment: Databases.zip [ 13050 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Attachment: Databases.zip [ 13050 ] =>

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Moved test case by Paquito Ines to a separate ticket CORE5435.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

This issue is more complex than just using "bad" index TESTE_DB_IDX1. The key difference is that the v2.5 cannot use index TESTE_DB_IDX1 for sorting but v3 can. And given that field EMPRESA is already used for TESTE_DB_IDX1, index TESTE_DB_IDX2 cannot be used anymore. In this case, another index is required:

CREATE INDEX TESTE_DB_IDX3 ON TESTE_DB (PEDIDO_NUMERO, PEDIDO_ITEM);

or just change order of columns inside TESTE_DB_IDX2:

CREATE INDEX TESTE_DB_IDX2 ON TESTE_DB (PEDIDO_NUMERO,
PEDIDO_ITEM, EMPRESA);

or change order of columns inside TESTE_DB_IDX1:

CREATE UNIQUE INDEX TESTE_DB_IDX1 ON TESTE_DB (DOCUMENTO, EMPRESA, ITEM);

Any of these tricks should work.

So far I don't see how to solve this issue inside the engine (without radically changing the optimizer cost patterns). Optimizer uses any available index for sorting unconditionally and cannot make any cost-based decisions between SORT and ORDER plans.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants