Bug #39358
closed
Missing content_facet_id indexes on join tables cause sequential scans at scale
Description
Two Katello join tables are missing indexes on content_facet_id, causing near-100% sequential scan rates during registration, content view publishing, and host applicability calculation.
Affected tables:
- katello_content_view_environment_content_facets: 93% seq scan rate, 553M tuples read
- katello_content_facet_repositories: 87% seq scan rate, 339M tuples read (existing composite index on (repository_id, content_facet_id) has wrong leading column for content_facet_id lookups)
Found via pg_stat_user_tables during registration performance testing at 1368 concurrent registrations with SQL debug logging enabled.
Fix: Add content_facet_id index to both tables using CREATE INDEX CONCURRENTLY to avoid table locks on large deployments.
Updated by The Foreman Bot about 2 months ago
- Status changed from New to Ready For Testing
Updated by The Foreman Bot about 2 months ago
- Fixed in Releases Katello 5.0.0 added
Updated by Anonymous about 2 months ago
- Status changed from Ready For Testing to Closed
Applied in changeset katello|6027570dac84529390192e2918885e0d3f453e5a.
Updated by Quinn James about 2 months ago
- Category set to Performance
- Target version set to Katello 4.21.0
- Triaged changed from No to Yes
Updated by The Foreman Bot about 1 month ago
- Fixed in Releases Katello 4.21.0 added