Issue Details (XML | Word | Printable)

Key: CORE-5310
Type: Bug Bug
Status: Open Open
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Claudio Luiz Vechini
Votes: 8
Watchers: 9
Operations

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

Incorrect PLAN using in Firebird 3 making it slow

Created: 14/Jul/16 01:57 PM   Updated: 04/Jan/17 07:48 PM
Component/s: Engine
Affects Version/s: 3.0.1
Fix Version/s: None

File Attachments: 1. Text File TESTE-FB25.TXT (2 kB)
2. Text File TESTE-FB30.TXT (2 kB)

Environment: Windows 32/64

QA Status: No test


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

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Sean Leyne added a comment - 14/Jul/16 02:58 PM
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.

Claudio Luiz Vechini added a comment - 19/Jul/16 02:38 PM
Same computer, details attached.

Dmitry Yemanov added a comment - 04/Jan/17 07:32 PM
Moved test case by Paquito Ines to a separate ticket CORE-5435.

Dmitry Yemanov added a comment - 04/Jan/17 07:48 PM
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.