Issue Details (XML | Word | Printable)

Key: CORE-3103
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Klaus-Dieter Pernak
Votes: 0
Watchers: 2
Operations

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

Select statement with more non indexed reads in version 2.5RC3 as in version 2.1.3

Created: 09/Aug/10 12:29 PM   Updated: 14/Feb/11 12:30 PM
Component/s: Engine
Affects Version/s: 2.5 RC3
Fix Version/s: 2.5.0

Time Tracking:
Not Specified

Environment: OS: Ubuntu 10.4 Server LTS
Issue Links:
Relate

Planning Status: Unspecified


 Description  « Hide
I have two tables "bauf" and "bstammdaten" with an foreign key in bauf to primary key in bstammdaten.
In table bstammdaten are 42000 and in bauf are 73000 records.

My select statement is:

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')

The performance analysis in firebird 2.1 are 2 indexed reads in bauf and 1 indexed reads in bstammdaten.

If I make the same statement on the server with version 2.5 the result is:

73000 non indexed reads in bauf and
42000 non indexed reads in bstammdaten

The database will be every day restored from version 2.1 to 2.5 so that the two versions hold the same data.

festus01

 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 09/Aug/10 02:36 PM
What is the plan in both v2.1 and v2.5?

Klaus-Dieter Pernak added a comment - 10/Aug/10 04:36 AM
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

Dmitry Yemanov added a comment - 10/Aug/10 11:20 AM
It seems being a regression introduced while fixing CORE-2822.

Dmitry Yemanov added a comment - 11/Aug/10 10:51 AM
It should be fixed in the following (tomorrow's) snapshot. Please test it and report back.

Klaus-Dieter Pernak added a comment - 12/Aug/10 11:42 AM - edited
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 |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+

Dmitry Yemanov added a comment - 12/Aug/10 01:46 PM
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.

Klaus-Dieter Pernak added a comment - 15/Aug/10 03:44 PM
Now I am four weeks in holyday. After that I will continue work on this problem.

Dmitry Yemanov added a comment - 02/Sep/10 01:07 PM
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.