## Problem `Integrations::PropagateService#create_integration_for_projects_without_integration` and `#create_integration_for_groups_without_integration` each pass the full `without_integration(integration)` scope. This includes a `NOT EXISTS` anti-join against `integrations`, plus a namespace-archived check for `projects`, directly into `propagate_integrations`, which wraps it in `each_batch(of: 10_000)`. Because the anti-join is baked into the outer scope, every batch-boundary lookup (`ORDER BY id OFFSET 10000 LIMIT 1`) has to re-evaluate the anti-join and namespace check against every candidate row until it accumulates 10,000 matches. This is the same anti-pattern diagnosed and fixed for two other propagation paths in #591182. This issue was discovered while gathering query-plan evidence for !258752 (#631288, part of &22859). It is not caused by !258752 and does not block it. It is a pre-existing issue in the same file. ### Where it happens ```ruby app/services/integrations/propagate_service.rb def create_integration_for_projects_without_integration propagate_integrations( Project.without_integration(integration), # full anti-join baked into the outer each_batch scope PropagateIntegrationProjectWorker ) end def create_integration_for_groups_without_integration propagate_integrations( Group.without_integration(integration), # same problem PropagateIntegrationGroupWorker ) end ``` These are the only two propagation paths in the service still doing what #591182 already fixed elsewhere - `propagate_integration_to_descendant_groups` - `propagate_integration_to_descendant_projects` This is likely because #591182 was scoped to large-group-tree propagation rather than instance-wide/cell-wide propagation. ### Evidence - `EXPLAIN ANALYZE` against gitlab-production-main (via postgres.ai, organization_id = 1, type Integrations::Jira) showed a single boundary-lookup query taking ~5.2s actual execution time (actual time=5230.449..5239.873). - A separate query estimated ~48.5 million projects currently match the filter for `organization_id = 1` (GitLab.com's Default organization, which still holds nearly every project given limited multi-org adoption). At 10,000 matching rows per batch, that's roughly 4,850 boundary-lookup queries to walk the relation for that organization alone. This is potentially multiple hours of cumulative boundary-discovery time before any integration rows are created or updated. - For projects, `organization_id = 1` isn't selective (it matches almost everything), so Postgres doesn't use the `organization_id`-covering index for this query. It falls back to a different index and applies `organization_id` as a residual filter. The problem isn't organization selectivity; it's that the anti-join is embedded in the outer each_batch scope at all. ### Evidence for groups (`Group.without_integration`) Same anti-pattern, different mechanism. Two `each_batch` boundary probes (`id >= <start> ORDER BY id OFFSET 10000 LIMIT 1`) against gitlab-production-main, `organization_id = 1`, `Integrations::Jira`: | Start id | Actual time | Buffers | Jira rows read from `integrations` | |---|---|---|---| | 2244 | 84 ms | hit=2248 read=169 | 4 | | 71835418 | 3,553 ms | hit=118513 read=6842 | 173,212 | - The `namespaces` side is cheap in both runs. `organization_id` is an `Index Cond` on `index_namespaces_on_organization_id_and_id_for_groups`, and about 10k index entries are read in about 90 ms. Unlike projects, the organization index is used. - The cost is on the `integrations` side. `index_integrations_on_unique_group_id_and_type_new` is `(group_id, type_new)`, so `type_new` can't be used to seek. The merge anti-join has to read every Jira row with a `group_id` below the batch's first group before it can match anything. The cost per probe therefore grows with the start id. The first run only looked cheap because few Jira group integrations have ids below 2244. - About 11.1M groups match `organization_id = 1` (row estimate from the plan), which is roughly 1,100 probes. Only two start ids were measured, so the cumulative boundary-discovery time has not been measured. - Batching over a plain `Group.in_organization(...)` scope, as proposed below, removes the `integrations` scan from the probe entirely. <details> <summary>SQL and plans</summary> ```sql SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."id" >= <start_id> AND "namespaces"."type" = 'Group' AND (NOT EXISTS ( SELECT 1 FROM "integrations" WHERE integrations.group_id = namespaces.id AND "integrations"."type_new" = 'Integrations::Jira' )) AND "namespaces"."organization_id" = 1 ORDER BY "namespaces"."id" ASC OFFSET 10000 LIMIT 1; ``` Start id 2244: ``` Limit (cost=429.32..429.37 rows=1 width=4) (actual time=84.356..84.357 rows=1 loops=1) Buffers: shared hit=2248 read=169 I/O Timings: read=80.606 write=0.000 -> Merge Anti Join (cost=1.12..475617.17 rows=11107244 width=4) (actual time=4.372..83.937 rows=10001 loops=1) Merge Cond: (namespaces.id = integrations.group_id) Buffers: shared hit=2248 read=169 I/O Timings: read=80.606 write=0.000 -> Index Only Scan using index_namespaces_on_organization_id_and_id_for_groups on public.namespaces (cost=0.56..422541.79 rows=11159735 width=4) (actual time=3.768..81.638 rows=10004 loops=1) Index Cond: ((namespaces.organization_id = 1) AND (namespaces.id >= 2244)) Heap Fetches: 40 Buffers: shared hit=2243 read=167 I/O Timings: read=79.444 write=0.000 -> Index Only Scan using index_integrations_on_unique_group_id_and_type_new on public.integrations (cost=0.56..166081.48 rows=3022233 width=8) (actual time=0.600..1.195 rows=4 loops=1) Index Cond: (integrations.type_new = 'Integrations::Jira'::text) Heap Fetches: 0 Buffers: shared hit=5 read=2 I/O Timings: read=1.162 write=0.000 ``` Start id 71835418: ``` Limit (cost=492.13..492.18 rows=1 width=4) (actual time=3553.362..3553.364 rows=1 loops=1) Buffers: shared hit=118513 read=6842 dirtied=6 WAL: records=6 fpi=6 bytes=39342 I/O Timings: read=3463.264 write=0.000 -> Merge Anti Join (cost=1.12..253129.92 rows=5155251 width=4) (actual time=3450.649..3552.949 rows=10001 loops=1) Merge Cond: (namespaces.id = integrations.group_id) Buffers: shared hit=118513 read=6842 dirtied=6 WAL: records=6 fpi=6 bytes=39342 I/O Timings: read=3463.264 write=0.000 -> Index Only Scan using index_namespaces_on_organization_id_and_id_for_groups on public.namespaces (cost=0.56..215286.12 rows=5179614 width=4) (actual time=3.234..90.462 rows=10616 loops=1) Index Cond: ((namespaces.organization_id = 1) AND (namespaces.id >= 71835418)) Heap Fetches: 157 Buffers: shared hit=4459 read=179 I/O Timings: read=87.583 write=0.000 -> Index Only Scan using index_integrations_on_unique_group_id_and_type_new on public.integrations (cost=0.56..166081.48 rows=3022233 width=8) (actual time=0.016..3449.940 rows=173212 loops=1) Index Cond: (integrations.type_new = 'Integrations::Jira'::text) Heap Fetches: 3512 Buffers: shared hit=114054 read=6663 dirtied=6 WAL: records=6 fpi=6 bytes=39342 I/O Timings: read=3375.681 write=0.000 ``` </details> ### Precedent #591182 fixed this exact anti-pattern for `propagate_integration_to_descendant_groups` and `propagate_integration_to_descendant_projects` in the same file. ### The fix Stop passing the filtered/anti-join scope into `each_batch`. Pass a cheap, plain, indexable scope instead, so `each_batch` only does simple id-range pagination. The `without_integration` filter is still applied, just once per batch, inside the worker. `PropagateIntegrationProjectWorker#perform` / `PropagateIntegrationGroupWorker#perform` already call `.without_integration(integration)` on their bounded id BETWEEN min..max slice, and `return if batch.empty?` cheaply skips no-op batches. Existing comment referencing this fix: ```ruby def propagate_integration_to_descendant_groups # Skip without_integration filter here - the worker re-applies it. # Keeping the batch scope simple avoids expensive OFFSET queries. # See https://gitlab.com/gitlab-org/gitlab/-/issues/591182 propagate_integrations(integration.group.descendants, PropagateIntegrationGroupWorker) end ``` Note: the original fix was rolled out behind a feature flag before cleanup (!230576 "FF cleanup for optimize_propagate_integration_projects", #589759 "FF rollout"), despite being a small code change — the team treated it as needing a careful, monitored rollout. ### Proposed fix ```ruby def create_integration_for_projects_without_integration propagate_integrations( Project.in_organization(integration.organization_id), # cheap, plain scope PropagateIntegrationProjectWorker ) end def create_integration_for_groups_without_integration propagate_integrations( Group.in_organization(integration.organization_id), PropagateIntegrationGroupWorker ) end ``` `each_batch` then paginates over a simple `WHERE organization_id = ?` scope. This is a single-table, uses `index_projects_on_organization_id_and_id` / `index_namespaces_on_organization_id_and_id_for_groups` directly, and would actually be selective for non-default organizations. The worker re-applies `.without_integration(integration)` per 10,000-row batch exactly as it already does today. ### Trade-off Some batches/jobs become no-ops for projects/groups that already have the integration, since the outer scope no longer excludes them up front. Those jobs exit early and cheaply via the existing `return if batch.empty?` check, which should be a much better trade than a multi-second anti-join walk per boundary probe. The actual job-volume impact, however, needs to be measured, not assumed. ### Risks / needs investigation 1. This method runs for every instance-level integration save, across every organization on GitLab.com, which is a high blast radius. This should likely get the same careful, feature-flagged rollout as the original #591182 fix, not a drop-in merge. 2. The change alters how many Sidekiq jobs get scheduled per propagation run (more total batches when most of an org's projects/groups already have the integration, since each_batch now walks by total org size rather than count of rows still missing the integration). This needs to be measured against real data. 3. Needs test coverage confirming propagation correctness/completeness is unaffected (no missed projects/groups, workers still idempotent) under the new batching behavior. ## Related - Discovered while working on !258752 and #631288, part of &22859 - Precedent / fix pattern to copy: #591182