|
[
Permalink
| « Hide
]
Dmitry Yemanov added a comment - 09/Aug/10 02:36 PM
What is the plan in both v2.1 and v2.5?
Plan in Version 2.5RC3:
Plan: PLAN JOIN (A NATURAL, B INDEX (FK_BAUF_BSTAMMDATEN_ID)) PLAN (BAUF NATURAL) ------ Leistungsinformation ------ Prepare time = 0ms Ausführungszeit = 796ms Avg fetch time = 796,00 ms Current memory = 36.400.256 Max memory = 36.577.456 Memory buffers = 4.096 Reads from disk to cache = 3.388 Writes from cache to disk = 0 Fetches from cache = 237.912 Plan in Version 2.1.3 Plan: PLAN JOIN (A INDEX (BSTAMMDATEN_MASKENKEY), B INDEX (FK_BAUF_BSTAMMDATEN_ID)) PLAN (BAUF INDEX (RDB$PRIMARY175)) Adapted plan: PLAN JOIN (A INDEX (BSTAMMDATEN_MASKENKEY), B INDEX (FK_BAUF_BSTAMMDATEN_ID)) PLAN (BAUF INDEX (PK_BAUF)) ------ Leistungsinformation ------ Prepare time = 16ms Ausführungszeit = 16ms Avg fetch time = 16,00 ms Current memory = 38.256.792 Max memory = 60.488.720 Memory buffers = 4.096 Reads from disk to cache = 0 Writes from cache to disk = 0 Fetches from cache = 27 It seems being a regression introduced while fixing
It should be fixed in the following (tomorrow's) snapshot. Please test it and report back.
Sorry, it works better but not optimal.
The Result now is: 1 indexed read and 0 non indexed read in table bstammdaten and 1 indexed read and 73000 non indexed reads on table bauf Plan: Plan: PLAN JOIN (A INDEX (BSTAMMDATEN_MASKENKEY), B INDEX (FK_BAUF_BSTAMMDATEN_ID)) PLAN (BAUF NATURAL) ------ Leistungsinformation ------ Prepare time = 16ms Ausführungszeit = 468ms Avg fetch time = 468,00 ms Current memory = 35.750.984 Max memory = 35.928.184 Memory buffers = 4.096 Reads from disk to cache = 2.697 Writes from cache to disk = 0 Fetches from cache = 152.654 Query ------------------------------------------------ select * from bauf where id = (select max(b.id) from bstammdaten a left outer join bauf b on b.bstammdaten_id_maskenkey = a.id where a.maskenkey='53') Plan ------------------------------------------------ PLAN JOIN (A INDEX (BSTAMMDATEN_MASKENKEY), B INDEX (FK_BAUF_BSTAMMDATEN_ID)) PLAN (BAUF NATURAL) Query Time ------------------------------------------------ Prepare : 16,00 ms Execute : 390,00 ms Avg fetch time: 390,00 ms Memory ------------------------------------------------ Current: 35.718.352 Max : 35.928.184 Buffers: 4.096 Operations ------------------------------------------------ Read : 0 Writes : 0 Fetches: 152.654 Enchanced Info: +--------------------------+-----------+-----------+-------------+---------+---------+---------+ | Table Name | Records | Indexed | Non-Indexed | Updates | Deletes | Inserts | Total | reads | reads | | | | +--------------------------+-----------+-----------+-------------+---------+---------+---------+ | BAUF | 0 | 1 | 73625 | 0 | 0 | 0 | | BSTAMMDATEN | 0 | 1 | 0 | 0 | 0 | 0 | +--------------------------+-----------+-----------+-------------+---------+---------+---------+ I'm afraid a database is required then. Only these two tables are enough, just make sure the issue is reproducible in the reduced example. If you cannot show it to the public (and thus attach to the tracker), please send it (or the download link) to me at: firebird2 <at> yandex <dot> ru.
Now I am four weeks in holyday. After that I will continue work on this problem.
I'm marking this ticket as closed as I'm unable to reproduce the issue after the fix. It can be re-opened once a reproducible test case is provided.
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||