explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WmpN

Settings
# exclusive inclusive rows x rows loops node
1. 515.243 44,767.312 ↑ 1.0 1 1

Aggregate (cost=2,098,866.63..2,098,866.64 rows=1 width=8) (actual time=44,767.312..44,767.312 rows=1 loops=1)

  • Output: count(*)
2. 16,357.874 44,252.069 ↑ 1.7 2,027,468 1

Hash Left Join (cost=571,827.10..2,090,093.01 rows=3,509,450 width=0) (actual time=8,713.375..44,252.069 rows=2,027,468 loops=1)

  • Hash Cond: (((ri.department_title)::text = ct.value) AND (a.assessment_result_id = adr.assessment_result_id))
  • Join Filter: (((services.reporting_configuration -> 'completed_assessments_adhoc_report'::text) ->> 'show_department_score'::text))::boolean
  • Rows Removed by Join Filter: 1,051,002
3. 1,581.951 23,089.952 ↑ 1.7 2,026,598 1

Hash Left Join (cost=315,107.80..1,631,154.82 rows=3,509,450 width=868) (actual time=3,891.133..23,089.952 rows=2,026,598 loops=1)

  • Output: a.assessment_result_id, ri.department_title, services.reporting_configuration
  • Hash Cond: ((cap.id = corrective_action_plan_items.corrective_action_plan_id) AND (ri.observation_id = corrective_action_plan_items.observation_id))
4. 1,082.648 18,270.977 ↑ 1.7 2,026,598 1

Hash Join (cost=81,699.00..1,345,104.26 rows=3,509,450 width=876) (actual time=649.757..18,270.977 rows=2,026,598 loops=1)

  • Output: a.assessment_result_id, ri.observation_id, ri.department_title, services.reporting_configuration, cap.id
  • Inner Unique: true
  • Hash Cond: (ri.form_version_id = form_versions.id)
5. 9,453.870 17,181.252 ↑ 1.7 2,026,598 1

Hash Join (cost=81,165.57..1,335,355.64 rows=3,509,450 width=880) (actual time=642.656..17,181.252 rows=2,026,598 loops=1)

  • Output: a.assessment_result_id, ri.form_version_id, ri.observation_id, ri.department_title, services.reporting_configuration, cap.id
  • Hash Cond: (ri.assessment_result_id = a.assessment_result_id)
6. 7,084.891 7,084.891 ↓ 1.0 25,014,116 1

Seq Scan on public.adhoc_report_raw_items ri (cost=0.00..1,125,360.14 rows=24,996,114 width=30) (actual time=0.004..7,084.891 rows=25,014,116 loops=1)

  • Output: ri.id, ri.assessment_id, ri.assessment_result_id, ri.form_version_id, ri.form_id, ri.department_title, ri.category_title, ri.line_item_code, ri.
7. 70.071 642.491 ↑ 1.0 117,387 1

Hash (cost=79,671.88..79,671.88 rows=119,495 width=858) (actual time=642.490..642.491 rows=117,387 loops=1)

  • Output: a.assessment_result_id, services.reporting_configuration, cap.id
  • Buckets: 131,072 Batches: 1 Memory Usage: 104,394kB
8. 63.287 572.420 ↑ 1.0 117,387 1

Hash Join (cost=62,231.42..79,671.88 rows=119,495 width=858) (actual time=168.389..572.420 rows=117,387 loops=1)

  • Output: a.assessment_result_id, services.reporting_configuration, cap.id
  • Inner Unique: true
  • Hash Cond: (a.service_id = services.id)
9. 341.025 506.989 ↑ 1.0 117,387 1

Hash Right Join (cost=61,681.89..78,807.92 rows=119,495 width=12) (actual time=166.238..506.989 rows=117,387 loops=1)

  • Output: a.assessment_result_id, a.service_id, cap.id
  • Hash Cond: (cap.assessment_result_id = a.assessment_result_id)
  • -> Seq Scan on public.corrective_action_plans cap (cost=0.00..15070.48 rows=398,827 width=8) (actual time=0.010..161.990 rows=399,014 loops=
  • Output: cap.id, cap.assessment_result_id, cap.status, cap.due_date, cap.expiration_date, cap."time", cap.submitted_at, cap.in_revision
  • Filter: ((NOT cap.is_draft) AND (NOT cap.is_outdated))
  • Rows Removed by Filter: 39,534
10. 165.964 165.964 ↑ 1.0 117,387 1

Hash (cost=60,188.20..60,188.20 rows=119,495 width=8) (actual time=165.964..165.964 rows=117,387 loops=1)

  • Output: a.assessment_result_id, a.service_id
  • Buckets: 131,072 Batches: 1 Memory Usage: 5,610kB
  • -> Bitmap Heap Scan on public.completed_assessments_report_raw_items a (cost=2,270.51..60188.20 rows=119,495 width=8) (actual time=13.
  • Output: a.assessment_result_id, a.service_id
  • Recheck Cond: (a.account_id = 199)
  • Heap Blocks: exact=34,181
  • -> Bitmap Index Scan on index_completed_assessments_report_raw_items_on_account_id (cost=0.00..2240.64 rows=119,495 width=0) (a
  • Index Cond: (a.account_id = 199)
11. 1.007 2.144 ↑ 1.0 1,757 1

Hash (cost=527.57..527.57 rows=1,757 width=854) (actual time=2.143..2.144 rows=1,757 loops=1)

  • Output: services.id, services.reporting_configuration
  • Buckets: 2,048 Batches: 1 Memory Usage: 1,537kB
12. 1.137 1.137 ↑ 1.0 1,757 1

Seq Scan on public.services (cost=0.00..527.57 rows=1,757 width=854) (actual time=0.004..1.137 rows=1,757 loops=1)

  • Output: services.id, services.reporting_configuration
13. 3.696 7.077 ↑ 1.0 13,569 1

Hash (cost=363.82..363.82 rows=13,569 width=4) (actual time=7.077..7.077 rows=13,569 loops=1)

  • Output: form_versions.id
  • Buckets: 16,384 Batches: 1 Memory Usage: 606kB
14. 3.381 3.381 ↑ 1.0 13,569 1

Index Only Scan using form_versions_pkey on public.form_versions (cost=0.29..363.82 rows=13,569 width=4) (actual time=0.013..3.381 rows=13,569 loops=1)

  • Output: form_versions.id
  • Heap Fetches: 0
15. 1,773.596 3,237.024 ↓ 1.0 4,483,801 1

Hash (cost=166,206.52..166,206.52 rows=4,480,152 width=8) (actual time=3,237.024..3,237.024 rows=4,483,801 loops=1)

  • Output: corrective_action_plan_items.observation_id, corrective_action_plan_items.corrective_action_plan_id
  • Buckets: 8,388,608 Batches: 1 Memory Usage: 240,685kB
16. 1,463.428 1,463.428 ↓ 1.0 4,483,801 1

Seq Scan on public.corrective_action_plan_items (cost=0.00..166,206.52 rows=4,480,152 width=8) (actual time=0.006..1,463.428 rows=4,483,801 loops=1)

  • Output: corrective_action_plan_items.observation_id, corrective_action_plan_items.corrective_action_plan_id
17. 1,480.098 4,804.243 ↑ 1.1 3,184,306 1

Hash (cost=204,804.01..204,804.01 rows=3,461,019 width=28) (actual time=4,804.243..4,804.243 rows=3,184,306 loops=1)

  • Output: adr.assessment_result_id, ct.value
  • Buckets: 4,194,304 Batches: 1 Memory Usage: 191,993kB
18. 1,722.938 3,324.145 ↑ 1.1 3,184,630 1

Hash Join (cost=16,094.09..204,804.01 rows=3,461,019 width=28) (actual time=351.919..3,324.145 rows=3,184,630 loops=1)

  • Output: adr.assessment_result_id, ct.value
  • Hash Cond: (adr.department_id = c.id)
19. 1,251.085 1,251.085 ↑ 1.0 3,184,630 1

Seq Scan on public.assessment_department_results adr (cost=0.00..142,045.91 rows=3,214,345 width=8) (actual time=0.009..1,251.085 rows=3,184,630 loops=1)

  • Output: adr.id, adr.assessment_id, adr.score, adr.is_excluded, adr.max_score, adr.deducted_points, adr.department_id, adr.bonus_points, adr.is_omitted, adr.as
  • Filter: (NOT adr.is_omitted)
  • Rows Removed by Filter: 565,314
20. 108.272 350.122 ↑ 1.1 293,045 1

Hash (cost=12,149.92..12,149.92 rows=315,534 width=28) (actual time=350.122..350.122 rows=293,045 loops=1)

  • Output: c.id, ct.value
  • Buckets: 524,288 Batches: 1 Memory Usage: 20,069kB
21. 156.467 241.850 ↑ 1.1 293,045 1

Hash Join (cost=113.90..12,149.92 rows=315,534 width=28) (actual time=2.054..241.850 rows=293,045 loops=1)

  • Output: c.id, ct.value
  • Hash Cond: (c.title = ct.translation_id)
22. 83.353 83.353 ↑ 1.0 293,045 1

Seq Scan on public.categories c (cost=0.00..7,415.45 rows=293,045 width=20) (actual time=0.004..83.353 rows=293,045 loops=1)

  • Output: c.id, c.title
23. 1.022 2.030 ↑ 1.0 2,863 1

Hash (cost=78.11..78.11 rows=2,863 width=40) (actual time=2.030..2.030 rows=2,863 loops=1)

  • Output: ct.translation_id, ct.value
  • Buckets: 4,096 Batches: 1 Memory Usage: 234kB
24. 1.008 1.008 ↑ 1.0 2,863 1

Seq Scan on public.category_translations ct (cost=0.00..78.11 rows=2,863 width=40) (actual time=0.005..1.008 rows=2,863 loops=1)

  • Output: ct.translation_id, ct.value
  • Filter: ((ct.language)::text = 'en'::text)
  • Rows Removed by Filter: 346
Planning time : 2.897 ms
Execution time : 44,771.540 ms