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
Comments
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. |
Commented by: Claudio Luiz Vechini (clvechini) Same computer, details attached. |
Modified by: Claudio Luiz Vechini (clvechini)Attachment: TESTE-FB25.TXT [ 12990 ] Attachment: TESTE-FB30.TXT [ 12991 ] |
Modified by: Paquito Ines (paquito)Attachment: Databases.zip [ 13050 ] |
Modified by: @dyemanovassignee: Dmitry Yemanov [ dimitr ] |
Modified by: @dyemanovAttachment: Databases.zip [ 13050 ] => |
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, 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. |
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
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;
The text was updated successfully, but these errors were encountered: