Bad acess plan on firebird 3.0
------------------------------
Key: CORE-5659
URL: http://tracker.firebirdsql.org/browse/CORE-5659
Project: Firebird Core
Issue Type: Bug
Affects Versions: 3.0.2
Environment: Windows 10 x64
Reporter: wesley blanco
Hi.
We are doing a migration process from firebird 2.5 to 3.0, and we found some
points of slowness that did not exist.
We realize that it is only at a specific point, a type of join, that does not
take any index in firebird 3 and the table is of almost 1mi of records.
We were able to create a script by extracting as few of these tables as
possible from one of these points. I've attached the script.
I upload script on my DropBox:
https://www.dropbox.com/s/x37rez36ot4jzd6/script.zip?dl=0
steps to reproduce:
1 - create database in version 2.5 and another in 3.0.
2 - run the attached script (isql.exe -> in 'C:\script.sql') on each of these
databases.
3 - run select on each of the databases:
SELECT A.ID_PEDIDO_ITEM,
C.DESCRICAO
FROM COM_PEDIDO B
JOIN COM_PEDIDO_ITEM A ON A.ID_PEDIDO = B.ID_PEDIDO
AND ( NOT(A.ID_PRODUTO =1 AND
A.ID_PEDIDO_ITEM_PAI IS NOT NULL))
JOIN EST_PRODUTO C ON C.ID_PRODUTO = A.ID_PRODUTO
WHERE B.DTH_PEDIDO BETWEEN cast('10.12.16 05:00:00' as timestamp) AND
cast('10.12.16 20:00:00' as timestamp);
Total records: 107;
FB2.5.7 result:
Query
------------------------------------------------
SELECT A.ID_PEDIDO_ITEM,
C.DESCRICAO
FROM COM_PEDIDO B
JOIN COM_PEDIDO_ITEM A ON A.ID_PEDIDO = B.ID_PEDIDO
AND ( NOT(A.ID_PRODUTO =1 AND
A.ID_PEDIDO_ITEM_PAI IS NOT NULL))
JOIN EST_PRODUTO C ON C.ID_PRODUTO = A.ID_PRODUTO
WHERE B.DTH_PEDIDO BETWEEN cast('10.12.16 05:00:00' as timestamp) AND
cast('10.12.16 20:00:00' as timestamp)
Plan
------------------------------------------------
PLAN JOIN (B INDEX (COM_PEDIDO_IDX1), A INDEX (FK_COM_PEDIDO_ITEM_PEDIDO), C
INDEX (RDB$PRIMARY1))
Adapted Plan
------------------------------------------------
PLAN JOIN (B INDEX (COM_PEDIDO_IDX1), A INDEX (FK_COM_PEDIDO_ITEM_PEDIDO), C
INDEX (INTEG_6))
Query Time
------------------------------------------------
Prepare : 16,00 ms
Execute : 31,00 ms
Avg fetch time: 0,29 ms
Memory
------------------------------------------------
Current: 9.300.768
Max : 9.371.088
Buffers: 2.048
Operations
------------------------------------------------
Read : 58
Writes : 0
Fetches: 1.122
Marks : 0
Enchanced Info:
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
| Table Name | Records | Indexed | Non-Indexed | Updates
| Deletes | Inserts | Backouts | Purges | Expunges |
| | Total | reads | reads |
| | | | | |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
|COM_PEDIDO | 0 | 58 | 0 | 0
| 0 | 0 | 0 | 0 | 0 |
|COM_PEDIDO_ITEM | 0 | 113 | 0 | 0
| 0 | 0 | 0 | 0 | 0 |
|EST_PRODUTO | 0 | 107 | 0 | 0
| 0 | 0 | 0 | 0 | 0 |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
Firebird 3.0.2 and 3.0.3.32829 result;
Query
------------------------------------------------
SELECT A.ID_PEDIDO_ITEM,
C.DESCRICAO
FROM COM_PEDIDO B
JOIN COM_PEDIDO_ITEM A ON A.ID_PEDIDO = B.ID_PEDIDO
AND ( NOT(A.ID_PRODUTO =1 AND
A.ID_PEDIDO_ITEM_PAI IS NOT NULL))
JOIN EST_PRODUTO C ON C.ID_PRODUTO = A.ID_PRODUTO
WHERE B.DTH_PEDIDO BETWEEN cast('10.12.16 05:00:00' as timestamp) AND
cast('10.12.16 20:00:00' as timestamp)
Plan
------------------------------------------------
PLAN JOIN (A NATURAL, C INDEX (RDB$PRIMARY1), B INDEX (PK_COM_PEDIDO))
Adapted Plan
------------------------------------------------
PLAN JOIN (A NATURAL, C INDEX (INTEG_6), B INDEX (PK_COM_PEDIDO))
Query Time
------------------------------------------------
Prepare : 31,00 ms
Execute : 8.766,00 ms
Avg fetch time: 81,93 ms
Memory
------------------------------------------------
Current: 10.233.800
Max : 10.506.880
Buffers: 2.048
Operations
------------------------------------------------
Read : 23.389
Writes : 1
Fetches: 9.867.285
Marks : 0
Enchanced Info:
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
| Table Name | Records | Indexed | Non-Indexed | Updates
| Deletes | Inserts | Backouts | Purges | Expunges |
| | Total | reads | reads |
| | | | | |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
|COM_PEDIDO | 0 | 977872 | 0 | 0
| 0 | 0 | 0 | 0 | 0 |
|COM_PEDIDO_ITEM | 0 | 0 | 998548 | 0
| 0 | 0 | 0 | 0 | 0 |
|EST_PRODUTO | 0 | 977872 | 0 | 0
| 0 | 0 | 0 | 0 | 0 |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
As you can see, only COM_PEDIDO_ITEM NATURAL was used and the index
COM_PEDIDO_IDX1 (COM_PEDIDO.DTH_PEDIDO) was not used.
For me to force the access plan on my system is a bit difficult, as I have this
situation at various points in the system making migration difficult.
what justifies this slower behavior compared to 2.5?
Thanks!!
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
