explain.depesz.com

PostgreSQL's explain analyze made readable

Result: S222

Settings
# exclusive inclusive rows x rows loops node
1. 53.679 436,413.679 ↓ 75.9 192,386 1

Unique (cost=2,508,417.49..2,508,468.17 rows=2,534 width=60) (actual time=436,136.114..436,413.679 rows=192,386 loops=1)

2.          

CTE autoscan_enabled_projects

3. 12.903 12.903 ↓ 1.2 15,023 1

Seq Scan on internal_component_props (cost=0.00..3,785.44 rows=12,618 width=56) (actual time=0.031..12.903 rows=15,023 loops=1)

  • Filter: ((kee)::text = 'sonar.autoscan.enabled'::text)
  • Rows Removed by Filter: 84,138
4.          

CTE monorepo_enabled_projects

5. 0.014 0.014 ↓ 0.0 0 1

Index Scan using properties_key on properties (cost=0.42..272.01 rows=94 width=34) (actual time=0.014..0.014 rows=0 loops=1)

  • Index Cond: ((prop_key)::text = 'sonar.project.monorepo.enabled'::text)
6. 1,078.653 436,360.000 ↓ 75.9 192,386 1

Sort (cost=2,504,360.04..2,504,366.38 rows=2,534 width=60) (actual time=436,136.113..436,360.000 rows=192,386 loops=1)

  • Sort Key: p.project_uuid, p.organization_uuid, p.private, alm.alm_id, (CASE autoscan_enabled_projects.is_autoscan_enabled WHEN 'true'::text THEN true ELSE false END), ((p.created_at)::date), (CASE monorepo_enabled_projects.is_monorepo_enabled WHEN 'true'::text THEN true ELSE false END)
  • Sort Method: external merge Disk: 12,776kB
7. 132.886 435,281.347 ↓ 75.9 192,386 1

Hash Left Join (cost=53,732.47..2,504,216.78 rows=2,534 width=60) (actual time=149.571..435,281.347 rows=192,386 loops=1)

  • Hash Cond: (p.id = monorepo_enabled_projects.resource_id)
8. 252.130 435,148.446 ↓ 75.9 192,386 1

Nested Loop Left Join (cost=53,729.42..2,504,178.87 rows=2,534 width=578) (actual time=149.513..435,148.446 rows=192,386 loops=1)

9. 238.270 430,663.824 ↓ 75.9 192,386 1

Hash Left Join (cost=53,729.00..2,486,232.58 rows=2,534 width=570) (actual time=149.487..430,663.824 rows=192,386 loops=1)

  • Hash Cond: ((p.project_uuid)::text = (autoscan_enabled_projects.component_uuid)::text)
10. 430,283.334 430,405.808 ↓ 75.9 192,386 1

Bitmap Heap Scan on projects p (cost=52,295.92..2,479,710.07 rows=2,534 width=54) (actual time=129.678..430,405.808 rows=192,386 loops=1)

  • Recheck Cond: ((qualifier)::text = 'TRK'::text)
  • Rows Removed by Index Recheck: 6,850,755
  • Filter: (enabled AND (main_branch_project_uuid IS NULL) AND ((scope)::text = 'PRJ'::text))
  • Rows Removed by Filter: 577,320
  • Heap Blocks: exact=14,449 lossy=585,866
11. 122.474 122.474 ↑ 1.1 772,160 1

Bitmap Index Scan on projects_qualifier (cost=0.00..52,295.28 rows=851,562 width=0) (actual time=122.474..122.474 rows=772,160 loops=1)

  • Index Cond: ((qualifier)::text = 'TRK'::text)
12. 2.776 19.746 ↓ 1.2 15,023 1

Hash (cost=252.36..252.36 rows=12,618 width=634) (actual time=19.746..19.746 rows=15,023 loops=1)

  • Buckets: 8,192 Batches: 4 Memory Usage: 280kB
13. 16.970 16.970 ↓ 1.2 15,023 1

CTE Scan on autoscan_enabled_projects (cost=0.00..252.36 rows=12,618 width=634) (actual time=0.035..16.970 rows=15,023 loops=1)

14. 4,232.492 4,232.492 ↑ 1.0 1 192,386

Index Scan using project_alm_bindings_project on project_alm_bindings alm (cost=0.42..7.08 rows=1 width=29) (actual time=0.022..0.022 rows=1 loops=192,386)

  • Index Cond: ((p.project_uuid)::text = (project_uuid)::text)
15. 0.000 0.015 ↓ 0.0 0 1

Hash (cost=1.88..1.88 rows=94 width=524) (actual time=0.015..0.015 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
16. 0.015 0.015 ↓ 0.0 0 1

CTE Scan on monorepo_enabled_projects (cost=0.00..1.88 rows=94 width=524) (actual time=0.015..0.015 rows=0 loops=1)