Skip to content

Avoid index scan for lower/upper bounds containing NULL keys#8447

Merged
dyemanov merged 1 commit intomasterfrom
work/no-index-lookup-for-null-keys
Feb 24, 2025
Merged

Avoid index scan for lower/upper bounds containing NULL keys#8447
dyemanov merged 1 commit intomasterfrom
work/no-index-lookup-for-null-keys

Conversation

@dyemanov
Copy link
Copy Markdown
Member

@dyemanov dyemanov commented Feb 22, 2025

This PR is a solution for #8278.

If the retrieval is flagged to ignore NULL keys, check whether any used segment of the lower/upper lookup keys contain NULL and if so just skip the index scan returning the empty bitmap.

Stats for the test case inside #8278:

before this PR:

Current memory = 20439712
Delta memory = 50352
Max memory = 20817952
Elapsed time = 36.030 sec
Cpu = 36.020 sec
Buffers = 2048
Reads = 1787
Writes = 4
Fetches = 7560432

after this PR:

Current memory = 18979680
Delta memory = 126736
Max memory = 19058304
Elapsed time = 0.018 sec
Cpu = 0.020 sec
Buffers = 2048
Reads = 1115
Writes = 0
Fetches = 18513

@dyemanov dyemanov requested a review from hvlad February 22, 2025 08:18
@dyemanov dyemanov self-assigned this Feb 22, 2025
@dyemanov dyemanov linked an issue Feb 24, 2025 that may be closed by this pull request
Avoid index lookup for a NULL key if the condition is known to always be FALSE in this case #8278
Closed
@dyemanov dyemanov merged commit 58633c8 into master Feb 24, 2025
@dyemanov dyemanov deleted the work/no-index-lookup-for-null-keys branch February 24, 2025 08:20
dyemanov added a commit that referenced this pull request Feb 24, 2025
@dyemanov
Avoid index scan for lower/upper bounds containing NULL keys (#8447)
22d23c1
@mrotteveel mrotteveel added the rlsnotes60: no Intentionally not added to the Firebird 6.0 release notes. label Mar 3, 2026
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

@hvlad hvlad hvlad approved these changes

Assignees

@dyemanov dyemanov

Labels

component: engine fix-version: 5.0.3 fix-version: 6.0 Alpha 1 qa: covered by another tests rlsnotes60: no Intentionally not added to the Firebird 6.0 release notes. type: improvement

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

Avoid index lookup for a NULL key if the condition is known to always be FALSE in this case

4 participants

@dyemanov @hvlad @mrotteveel @pavel-zotov