Skip to content
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

Select statement with more non indexed reads in version 2.5RC3 as in version 2.1.3 [CORE3103] #3481

Closed
firebird-automations opened this issue Aug 9, 2010 · 19 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Klaus-Dieter Pernak (festus01)

Is related to CORE2822
Relate to CORE3283

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(http://b.id) from bstammdaten a
left outer join bauf b on b.bstammdaten_id_maskenkey = http://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

Commits: 5f1d41a 3c11e2d

====== Test Details ======

Reduced number of rows for checking: master table = 5000, detail = ~9000

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

What is the plan in both v2.1 and v2.5?

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

security: Developers [ 10012 ] =>

@firebird-automations
Copy link
Collaborator Author

Commented by: Klaus-Dieter Pernak (festus01)

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => In Progress [ 3 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is related to CORE2822 [ CORE2822 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

It seems being a regression introduced while fixing CORE2822.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: In Progress [ 3 ] => Open [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

It should be fixed in the following (tomorrow's) snapshot. Please test it and report back.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 2.1.4 [ 10361 ]

Fix Version: 2.5.0 [ 10221 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Klaus-Dieter Pernak (festus01)

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(http://b.id) from bstammdaten a
left outer join bauf b on b.bstammdaten_id_maskenkey = http://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 |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: Klaus-Dieter Pernak (festus01)

Now I am four weeks in holyday. After that I will continue work on this problem.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 2.1.4 [ 10361 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue relate to CORE3283 [ CORE3283 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Closed [ 6 ] => Closed [ 6 ]

QA Status: Done successfully

Test Details: Reduced number of rows for checking: master table = 5000, detail = ~9000

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants