Update prefix usage query to improve index usage#3413
Merged
lunkwill42 merged 1 commit intomasterfrom Aug 6, 2025
Merged
Conversation
On some installations with huge ARP tables, we recommend adding local gist indexes or partial indexes for queries against open arp records (`end_time = 'infinity'`) to improve the lookup speeds. In order to ensure these indexes can be made as btree indexes, their conditional is usually configured as applying to records where `end_time >= 'infinity'` (as `=` matches will often map to a hash index rather than a btree index, `>=` is better). However, PostgreSQL's planner cannot detect and properly utilize these indexes if a query includes a where clause that limits it to `end_time = 'infinity'`. These queries should explicitly be for `end_time >= 'infinity'` to ensure the indexes can be used. This query was found to cause severe timeout issues on a customer installation, because of repeated queries to the prefix usage API endpoint that would never complete, causing more and more requests to stack on and eat up all available PostgreSQL connections.
|
Test results 12 files 12 suites 12m 3s ⏱️ Results for commit b5360b6. |
johannaengland
approved these changes
Aug 5, 2025
Codecov Report✅ All modified and coverable lines are covered by tests. Additional details and impacted files@@ Coverage Diff @@
## master #3413 +/- ##
==============================
==============================
☔ View full report in Codecov by Sentry. 🚀 New features to boost your workflow:
|
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
2 participants
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.
Scope and purpose
On some installations with huge ARP tables, we recommend adding local gist indexes or partial indexes for queries against open arp records (
end_time = 'infinity') to improve the lookup speeds.In order to ensure these indexes can be made as btree indexes, their conditional is usually configured as applying to records where
end_time >= 'infinity'(as=matches will often map to a hash index rather than a btree index,>=is better).However, PostgreSQL's planner cannot detect and properly utilize these indexes if a query includes a where clause that limits it to
end_time = 'infinity'. These queries should explicitly be forend_time >= 'infinity'to ensure the indexes can be used.This query was found to cause severe timeout issues on a customer installation, because of repeated queries to the prefix usage API endpoint that would never complete, causing more and more requests to stack on and eat up all available PostgreSQL connections.
Contributor Checklist
Every pull request should have this checklist filled out, no matter how small it is.
More information about contributing to NAV can be found in the
Hacker's guide to NAV.
[ ] Added/amended tests for new/changed code[ ] Added/changed documentation<major>.<minor>.x). For a new feature or other additions, it should be based onmaster.[ ] If applicable: Created new issues if this PR does not fix the issue completely/there is further work to be done[ ] If this results in changes in the UI: Added screenshots of the before and after[ ] If this adds a new Python source code file: Added the boilerplate header to that file