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-issue-importer opened this issue Aug 9, 2010 · 19 comments

Comments

@firebird-issue-importer
Copy link

@firebird-issue-importer firebird-issue-importer commented Aug 9, 2010

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 9, 2010

Commented by: @dyemanov

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 9, 2010

Modified by: @dyemanov

security: Developers [ 10012 ] =>

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 10, 2010

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 10, 2010

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 10, 2010

Modified by: @dyemanov

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 10, 2010

Modified by: @dyemanov

Link: This issue is related to CORE2822 [ CORE2822 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 10, 2010

Commented by: @dyemanov

It seems being a regression introduced while fixing CORE2822.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 11, 2010

Modified by: @dyemanov

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 11, 2010

Commented by: @dyemanov

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 11, 2010

Modified by: @dyemanov

Fix Version: 2.1.4 [ 10361 ]

Fix Version: 2.5.0 [ 10221 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 12, 2010

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 12, 2010

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Aug 15, 2010

Commented by: Klaus-Dieter Pernak (festus01)

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 2, 2010

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Sep 2, 2010

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 2, 2010

Modified by: @dyemanov

Fix Version: 2.1.4 [ 10361 ] =>

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 19, 2010

Modified by: @dyemanov

Link: This issue relate to CORE3283 [ CORE3283 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Feb 14, 2011

Modified by: @pcisar

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented May 27, 2015

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
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants