Fixes #20023: Add GiST index on Prefix.prefix for net contains ops #1486

New Issue

MrUnknownDE · 2026-04-06T02:20:15+02:00

MrUnknownDE commented

2026-04-06 02:20:15 +02:00

Originally created by @jnovinger on 8/8/2025

Fixes: #20023

Resolves performance issue where prefix deletion with 2000+ children took 5-10 minutes due to sequential scans in hierarchy depth/children calculations. Adding PostgreSQL GiST index with inet_ops enables efficient network containment operators (>>, <<, <<=) in annotate_hierarchy() queries.

Performance impact:

30-60x speedup: 5-10 minutes → 10 seconds for large prefix deletions
Real-world validation: 4s migration time on 1.24M prefix dataset
Storage cost: 47MB index (11% of table storage, 38 bytes per prefix)

Works in conjunction with existing B-tree indexes on vrf_id for optimal query performance. Benefits all network containment operations including hierarchy navigation, aggregate views, and available IP/prefix calculations.

*Originally created by @jnovinger on 8/8/2025* ### Fixes: #20023 Resolves performance issue where prefix deletion with 2000+ children took 5-10 minutes due to sequential scans in hierarchy depth/children calculations. Adding PostgreSQL GiST index with inet_ops enables efficient network containment operators (>>, <<, <<=) in annotate_hierarchy() queries. Performance impact: - 30-60x speedup: 5-10 minutes → 10 seconds for large prefix deletions - Real-world validation: 4s migration time on 1.24M prefix dataset - Storage cost: 47MB index (11% of table storage, 38 bytes per prefix) Works in conjunction with existing B-tree indexes on vrf_id for optimal query performance. Benefits all network containment operations including hierarchy navigation, aggregate views, and available IP/prefix calculations.

MrUnknownDE closed this issue

2026-04-06 02:20:15 +02:00

Sign in to join this conversation.

Branches Tags

main

21455-sql-indexes-audit

21357-register-model-actions

feature

20924-plugin-ui-components

21766-improve-test-coverage-for-sortable-table-columns

21157-export-template-public-models

21025-pre-render-config-contexts

21364-swagger

feature-ip-prefix-link

20911-dropdown-3

fix_module_substitution

21160-filterset

20911-dropdown-2

20044-elevation-stuck-lightmode

7604-filter-modifiers-v3

20660-script-load

19724-graphql

14884-script

19720-macaddress-interface-generic-relation

fix-19669-api-image-download

7604-filter-modifiers

19275-fixes-interface-bulk-edit

fix-17794-get_field_value_return_list

11507-show-aggregate-and-rir-on-api

9583-add_column_specific_search_field_to_tables

No Label

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: github/netbox#1486