Unnecessary index scan happens when the same index is mapped to both WHERE and ORDER BY clauses [CORE1550] #1967
Assignees
Comments
Modified by: @pcisarWorkflow: jira [ 13358 ] => Firebird [ 14268 ] |
Modified by: @dyemanovFix Version: 3.0 Alpha 1 [ 10331 ] |
Modified by: @dyemanovstatus: Open [ 1 ] => In Progress [ 3 ] |
Modified by: @dyemanovpriority: Major [ 3 ] => Minor [ 4 ] |
Modified by: @dyemanovstatus: In Progress [ 3 ] => Open [ 1 ] |
|
Commented by: @pcisar Test created. |
Modified by: @pcisarstatus: Resolved [ 5 ] => Closed [ 6 ] |
firebird-automations
added
affect-version: 2.1 Beta 2
affect-version: 2.0.3
affect-version: 2.0.2
affect-version: 2.1 Beta 1
affect-version: 2.1 Alpha 1
affect-version: 2.0.1
affect-version: 2.0.0
fix-version: 3.0 Alpha 1
resolution: fixed
priority: minor
component: engine
type: bug
qa: done successfully
labels
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Submitted by: @dyemanov
Is related to QA535
Votes: 1
Test case:
select *
from rdb$relations
where rdb$relation_id < 10
order by rdb$relation_id
PLAN (RDB$RELATIONS ORDER RDB$INDEX_1 INDEX (RDB$INDEX_1))
But it should be:
PLAN (RDB$RELATIONS ORDER RDB$INDEX_1)
Because the WHERE condition should just set the upper threshold key and thus limit the navigational scan. A prior regular index scan is completely redundant in this case.
Generally speaking, the same index should never appear in both ORDER and INDEX parts of the same plan item. This particular issue is a regression introduced in v2.0, however other aspects of this bug are visible in all FB versions.
Commits: 263b396 a5cde2b 74fbaf5 c65e55c FirebirdSQL/fbt-repository@ff96c7e FirebirdSQL/fbt-repository@920cc2b FirebirdSQL/fbt-repository@bbbf0b8 FirebirdSQL/fbt-repository@d4c51e1 FirebirdSQL/fbt-repository@b2d0198
The text was updated successfully, but these errors were encountered: