explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jh9P

Settings
# exclusive inclusive rows x rows loops node
1. 17.538 52,119.266 ↓ 39,384.0 39,384 1

Sort (cost=3,557,645.88..3,557,645.88 rows=1 width=35) (actual time=52,116.417..52,119.266 rows=39,384 loops=1)

  • Sort Key: (COALESCE((SubPlan 2), CASE training_center_bundle_due_date.due_date_type WHEN 'HIRE_DATE_DERIVED'::text THEN ((((caregiver_agency_assoc_1.""hireDate"" + training_center_bundle_due_date.compliance_days_after_hire_date_anniversary) + (((training_center_bundle_due_date.year - (date_part('year'::text, ((caregiver_agency_assoc_1.""hireDate"" + training_center_bundle_due_date.compliance_days_after_hire_date_anniversary))::timestamp without time zone))::integer))::double precision * '1 year'::interval)))::date + (training_center_bundle_due_date.completion_days_after_hire_date_anniversary - training_center_bundle_due_date.compliance_days_after_hire_date_anniversary)) WHEN 'DATES'::text THEN training_center_bundle_due_date.completion_date ELSE NULL::date END)) DESC
  • Sort Method: quicksort Memory: 4,613kB
2. 15,923.989 52,101.728 ↓ 39,384.0 39,384 1

Merge Join (cost=9,570.32..3,557,645.87 rows=1 width=35) (actual time=561.964..52,101.728 rows=39,384 loops=1)

  • Merge Cond: (caregiver.id = caregiver_1.id)
  • Join Filter: ((training_center_bundle_due_date_1.bundle_id = training_center_bundle_due_date_2.bundle_id) AND (training_center_bundle_due_date.id = training_center_bundle_due_date_2.id))
  • Rows Removed by Join Filter: 34,145,928
3. 22,297.953 32,399.970 ↑ 1.0 228,349,969 1

Nested Loop (cost=0.42..2,957,146.47 rows=235,899,212 width=12) (actual time=0.046..32,399.970 rows=228,349,969 loops=1)

4. 105.091 105.091 ↓ 1.0 263,077 1

Index Only Scan using caregiver_pkey on caregiver (cost=0.42..8,364.09 rows=262,694 width=4) (actual time=0.036..105.091 rows=263,077 loops=1)

  • Heap Fetches: 30,126
5. 9,996.789 9,996.926 ↑ 1.0 868 263,077

Materialize (cost=0.00..44.47 rows=898 width=8) (actual time=0.000..0.038 rows=868 loops=263,077)

6. 0.137 0.137 ↑ 1.0 868 1

Seq Scan on training_center_bundle_due_date training_center_bundle_due_date_2 (cost=0.00..39.98 rows=898 width=8) (actual time=0.006..0.137 rows=868 loops=1)

7. 1,499.006 3,359.057 ↓ 5,697,407.5 34,184,445 1

Materialize (cost=9,569.89..10,623.07 rows=6 width=70) (actual time=560.207..3,359.057 rows=34,184,445 loops=1)

8. 38.178 1,860.051 ↓ 6,564.0 39,384 1

Nested Loop (cost=9,569.89..10,623.06 rows=6 width=70) (actual time=560.205..1,860.051 rows=39,384 loops=1)

9. 30.444 1,782.489 ↓ 6,564.0 39,384 1

Nested Loop (cost=9,569.62..10,621.23 rows=6 width=66) (actual time=560.192..1,782.489 rows=39,384 loops=1)

  • Join Filter: CASE WHEN (caregiver_agency_assoc.""hireDate"" IS NOT NULL) THEN (caregiver_agency_assoc.""hireDate"" <= COALESCE((SubPlan 11), CASE training_center_bundle_due_date.due_date_type WHEN 'HIRE_DATE_DERIVED'::text THEN (((caregiver_agency_assoc_1.""hireDate"" + training_center_bundle_due_date.compliance_days_after_hire_date_anniversary) + (((training_center_bundle_due_date.year - (date_part('year'::text, ((caregiver_agency_assoc_1.""hireDate"" + training_center_bundle_due_date.compliance_days_after_hire_date_anniversary))::timestamp without time zone))::integer))::double precision * '1 year'::interval)))::date WHEN 'DATES'::text THEN training_center_bundle_due_date.compliance_date ELSE NULL::date END)) ELSE true END
  • Rows Removed by Join Filter: 111
10. 222.800 1,642.646 ↓ 3,291.2 39,495 1

Nested Loop (cost=9,569.20..10,585.06 rows=12 width=78) (actual time=560.172..1,642.646 rows=39,495 loops=1)

  • Join Filter: ((COALESCE((SubPlan 12), CASE training_center_bundle_due_date.due_date_type WHEN 'HIRE_DATE_DERIVED'::text THEN ((((caregiver_agency_assoc_1.""hireDate"" + training_center_bundle_due_date.compliance_days_after_hire_date_anniversary) + (((training_center_bundle_due_date.year - (date_part('year'::text, ((caregiver_agency_assoc_1.""hireDate"" + training_center_bundle_due_date.compliance_days_after_hire_date_anniversary))::timestamp without time zone))::integer))::double precision * '1 year'::interval)))::date + (training_center_bundle_due_date.completion_days_after_hire_date_anniversary - training_center_bundle_due_date.compliance_days_after_hire_date_anniversary)) WHEN 'DATES'::text THEN training_center_bundle_due_date.completion_date ELSE NULL::date END) >= '2020-01-01'::date) AND (COALESCE((SubPlan 13), CASE training_center_bundle_due_date.due_date_type WHEN 'HIRE_DATE_DERIVED'::text THEN ((((caregiver_agency_assoc_1.""hireDate"" + training_center_bundle_due_date.compliance_days_after_hire_date_anniversary) + (((training_center_bundle_due_date.year - (date_part('year'::text, ((caregiver_agency_assoc_1.""hireDate"" + training_center_bundle_due_date.compliance_days_after_hire_date_anniversary))::timestamp without time zone))::integer))::double precision * '1 year'::interval)))::date + (training_center_bundle_due_date.completion_days_after_hire_date_anniversary - training_center_bundle_due_date.compliance_days_after_hire_date_anniversary)) WHEN 'DATES'::text THEN training_center_bundle_due_date.completion_date ELSE NULL::date END) <= '2020-12-31'::date))
  • Rows Removed by Join Filter: 79,002
11. 24.026 1,103.853 ↓ 1,087.1 118,497 1

Nested Loop (cost=9,568.92..9,992.63 rows=109 width=36) (actual time=560.135..1,103.853 rows=118,497 loops=1)

12. 37.481 842.833 ↓ 1,087.1 118,497 1

Nested Loop (cost=9,568.50..9,941.20 rows=109 width=28) (actual time=560.128..842.833 rows=118,497 loops=1)

  • Join Filter: CASE WHEN (training_center_bundle_due_date_1.due_date_type = 'HIRE_DATE_DERIVED'::text) THEN (caregiver_agency_assoc_2.""hireDate"" IS NOT NULL) ELSE true END
  • Rows Removed by Join Filter: 26,652
13. 29.791 708.586 ↓ 432.0 48,383 1

Nested Loop (cost=9,568.23..9,864.08 rows=112 width=24) (actual time=560.115..708.586 rows=48,383 loops=1)

14. 13.272 582.029 ↓ 432.0 48,383 1

Unique (cost=9,567.80..9,569.48 rows=112 width=25) (actual time=560.090..582.029 rows=48,383 loops=1)

15. 28.705 568.757 ↓ 432.0 48,383 1

Sort (cost=9,567.80..9,568.08 rows=112 width=25) (actual time=560.088..568.757 rows=48,383 loops=1)

  • Sort Key: caregiver_1.id, caregiver_agency_assoc_3.id, training_center_bundle_1.id, training_center_bundle_1.active, training_center_bundle_1.published_at
  • Sort Method: quicksort Memory: 5,316kB
16. 3.549 540.052 ↓ 432.0 48,383 1

Append (cost=1,036.42..9,563.99 rows=112 width=25) (actual time=1.366..540.052 rows=48,383 loops=1)

17. 21.272 536.418 ↓ 435.9 48,383 1

Gather (cost=1,036.42..9,511.54 rows=111 width=25) (actual time=1.365..536.418 rows=48,383 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
18. 36.632 515.146 ↓ 372.2 24,192 2 / 2

Nested Loop (cost=36.42..8,500.44 rows=65 width=25) (actual time=0.327..515.146 rows=24,192 loops=2)

  • Join Filter: (training_center_bundle_caregiver_certifications.caregiver_certification = caregiver_1.certification)
  • Rows Removed by Join Filter: 78,422
19. 37.513 273.288 ↓ 240.3 102,613 2 / 2

Nested Loop (cost=36.00..7,918.51 rows=427 width=32) (actual time=0.308..273.288 rows=102,613 loops=2)

20. 16.107 30.479 ↓ 36.1 102,648 2 / 2

Hash Join (cost=35.58..5,540.28 rows=2,843 width=24) (actual time=0.295..30.479 rows=102,648 loops=2)

  • Hash Cond: (caregiver_office_assoc.office_id = training_center_bundle_caregiver_certifications.office_id)
21. 14.245 14.245 ↑ 1.2 14,664 2 / 2

Parallel Seq Scan on caregiver_office_assoc (cost=0.00..5,367.47 rows=17,409 width=12) (actual time=0.125..14.245 rows=14,664 loops=2)

  • Filter: (agency_id = 57)
  • Rows Removed by Filter: 168,336
22. 0.006 0.127 ↑ 3.9 7 2 / 2

Hash (cost=35.24..35.24 rows=27 width=24) (actual time=0.127..0.127 rows=7 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
23. 0.005 0.121 ↑ 3.9 7 2 / 2

Nested Loop (cost=0.28..35.24 rows=27 width=24) (actual time=0.049..0.121 rows=7 loops=2)

24. 0.088 0.088 ↑ 1.2 4 2 / 2

Seq Scan on training_center_bundle training_center_bundle_1 (cost=0.00..14.16 rows=5 width=17) (actual time=0.029..0.088 rows=4 loops=2)

  • Filter: ((active IS TRUE) AND (published_at IS NOT NULL) AND (agency_id = 57))
  • Rows Removed by Filter: 480
25. 0.028 0.028 ↑ 3.0 2 8 / 2

Index Only Scan using training_center_bundle_caregi_bundle_id_office_id_caregiver_key on training_center_bundle_caregiver_certifications (cost=0.28..4.16 rows=6 width=11) (actual time=0.006..0.007 rows=2 loops=8)

  • Index Cond: (bundle_id = training_center_bundle_1.id)
  • Heap Fetches: 7
26. 205.296 205.296 ↑ 1.0 1 205,296 / 2

Index Scan using caregiver_agency_assoc_caregiver_agency_key on caregiver_agency_assoc caregiver_agency_assoc_3 (cost=0.42..0.84 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=205,296)

  • Index Cond: ((caregiver = caregiver_office_assoc.caregiver_id) AND (agency = 57))
  • Filter: (status <> 'JOIN_REQUEST'::text)
  • Rows Removed by Filter: 0
27. 205.226 205.226 ↑ 1.0 1 205,226 / 2

Index Scan using caregiver_pkey on caregiver caregiver_1 (cost=0.42..1.35 rows=1 width=7) (actual time=0.002..0.002 rows=1 loops=205,226)

  • Index Cond: (id = caregiver_office_assoc.caregiver_id)
28. 0.001 0.085 ↓ 0.0 0 1

Nested Loop (cost=0.83..51.33 rows=1 width=25) (actual time=0.084..0.085 rows=0 loops=1)

29. 0.003 0.084 ↓ 0.0 0 1

Nested Loop (cost=0.41..27.32 rows=10 width=21) (actual time=0.084..0.084 rows=0 loops=1)

30. 0.069 0.069 ↑ 1.2 4 1

Seq Scan on training_center_bundle training_center_bundle_2 (cost=0.00..14.16 rows=5 width=17) (actual time=0.023..0.069 rows=4 loops=1)

  • Filter: ((active IS TRUE) AND (published_at IS NOT NULL) AND (agency_id = 57))
  • Rows Removed by Filter: 480
31. 0.008 0.012 ↓ 0.0 0 4

Bitmap Heap Scan on training_center_caregiver_manual_bundle_addition (cost=0.41..2.58 rows=5 width=8) (actual time=0.003..0.003 rows=0 loops=4)

  • Recheck Cond: (bundle_id = training_center_bundle_2.id)
32. 0.004 0.004 ↓ 0.0 0 4

Bitmap Index Scan on training_center_caregiver_manual_bun_bundle_id_caregiver_id_key (cost=0.00..0.41 rows=5 width=0) (actual time=0.001..0.001 rows=0 loops=4)

  • Index Cond: (bundle_id = training_center_bundle_2.id)
33. 0.000 0.000 ↓ 0.0 0

Index Scan using caregiver_agency_assoc_caregiver_agency_key on caregiver_agency_assoc caregiver_agency_assoc_4 (cost=0.42..2.40 rows=1 width=12) (never executed)

  • Index Cond: ((caregiver = training_center_caregiver_manual_bundle_addition.caregiver_id) AND (agency = 57))
  • Filter: (status <> 'JOIN_REQUEST'::text)
34. 96.766 96.766 ↑ 1.0 1 48,383

Index Scan using caregiver_agency_assoc_pkey on caregiver_agency_assoc caregiver_agency_assoc_2 (cost=0.42..2.62 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=48,383)

  • Index Cond: (id = caregiver_agency_assoc_3.id)
35. 96.766 96.766 ↓ 1.5 3 48,383

Index Scan using tmp_index_654234234 on training_center_bundle_due_date training_center_bundle_due_date_1 (cost=0.28..0.66 rows=2 width=18) (actual time=0.001..0.002 rows=3 loops=48,383)

  • Index Cond: (bundle_id = training_center_bundle_1.id)
36. 236.994 236.994 ↑ 1.0 1 118,497

Index Scan using caregiver_agency_assoc_pkey on caregiver_agency_assoc caregiver_agency_assoc_1 (cost=0.42..0.47 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=118,497)

  • Index Cond: (id = caregiver_agency_assoc_2.id)
37. 118.497 118.497 ↑ 1.0 1 118,497

Index Scan using training_center_bundle_due_date_pkey on training_center_bundle_due_date (cost=0.28..0.35 rows=1 width=42) (actual time=0.001..0.001 rows=1 loops=118,497)

  • Index Cond: (id = training_center_bundle_due_date_1.id)
38.          

SubPlan (for Nested Loop)

39. 118.497 118.497 ↓ 0.0 0 118,497

Index Scan using training_center_caregiver_bun_bundle_due_date_id_caregiver__key on training_center_caregiver_bundle_due_date_override training_center_caregiver_bundle_due_date_override_4 (cost=0.28..2.50 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=118,497)

  • Index Cond: ((bundle_due_date_id = training_center_bundle_due_date.id) AND (caregiver_id = caregiver_1.id))
40. 78.999 78.999 ↓ 0.0 0 78,999

Index Scan using training_center_caregiver_bun_bundle_due_date_id_caregiver__key on training_center_caregiver_bundle_due_date_override training_center_caregiver_bundle_due_date_override_5 (cost=0.28..2.50 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=78,999)

  • Index Cond: ((bundle_due_date_id = training_center_bundle_due_date.id) AND (caregiver_id = caregiver_1.id))
41. 78.990 78.990 ↑ 1.0 1 39,495

Index Scan using caregiver_agency_assoc_pkey on caregiver_agency_assoc (cost=0.42..0.47 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=39,495)

  • Index Cond: (id = caregiver_agency_assoc_2.id)
42.          

SubPlan (for Nested Loop)

43. 30.409 30.409 ↓ 0.0 0 30,409

Index Scan using training_center_caregiver_bun_bundle_due_date_id_caregiver__key on training_center_caregiver_bundle_due_date_override training_center_caregiver_bundle_due_date_override_3 (cost=0.28..2.50 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=30,409)

  • Index Cond: ((bundle_due_date_id = training_center_bundle_due_date.id) AND (caregiver_id = caregiver_1.id))
44. 39.384 39.384 ↑ 1.0 1 39,384

Index Only Scan using training_center_bundle_pkey on training_center_bundle (cost=0.27..0.30 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=39,384)

  • Index Cond: (id = training_center_bundle_due_date_1.bundle_id)
  • Heap Fetches: 39,384
45.          

SubPlan (for Merge Join)

46. 39.384 39.384 ↓ 0.0 0 39,384

Index Scan using training_center_caregiver_bun_bundle_due_date_id_caregiver__key on training_center_caregiver_bundle_due_date_override (cost=0.28..2.50 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=39,384)

  • Index Cond: ((bundle_due_date_id = training_center_bundle_due_date_1.id) AND (caregiver_id = caregiver_1.id))
47. 39.384 39.384 ↓ 0.0 0 39,384

Index Scan using training_center_caregiver_bun_bundle_due_date_id_caregiver__key on training_center_caregiver_bundle_due_date_override training_center_caregiver_bundle_due_date_override_1 (cost=0.28..2.50 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=39,384)

  • Index Cond: ((bundle_due_date_id = training_center_bundle_due_date.id) AND (caregiver_id = caregiver_1.id))
48. 39.384 39.384 ↓ 0.0 0 39,384

Index Scan using training_center_caregiver_bun_bundle_due_date_id_caregiver__key on training_center_caregiver_bundle_due_date_override training_center_caregiver_bundle_due_date_override_2 (cost=0.28..2.50 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=39,384)

  • Index Cond: ((bundle_due_date_id = training_center_bundle_due_date.id) AND (caregiver_id = caregiver_1.id))
49. 78.768 78.768 ↓ 0.0 0 39,384

Index Only Scan using training_center_caregiver_vid_bundle_due_date_id_caregiver_key1 on training_center_caregiver_video_bundle_item_watch_total (cost=0.42..2.64 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=39,384)

  • Index Cond: ((bundle_due_date_id = training_center_bundle_due_date_2.id) AND (caregiver_id = caregiver.id))
  • Heap Fetches: 2,479
50. 71.882 71.882 ↓ 0.0 0 35,941

Index Only Scan using training_center_caregiver_pdf_bundle_due_date_id_caregiver__key on training_center_caregiver_pdf_bundle_item_watch_segment (cost=0.42..2.64 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=35,941)

  • Index Cond: ((bundle_due_date_id = training_center_bundle_due_date_2.id) AND (caregiver_id = caregiver.id))
  • Heap Fetches: 161
51. 71.142 71.142 ↓ 0.0 0 35,571

Index Only Scan using training_center_caregiver_tes_caregiver_id_bundle_due_date__key on training_center_caregiver_test_submission (cost=0.43..2.65 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=35,571)

  • Index Cond: ((caregiver_id = caregiver.id) AND (bundle_due_date_id = training_center_bundle_due_date_2.id))
  • Heap Fetches: 1
52. 39.384 39.384 ↓ 0.0 0 39,384

Index Only Scan using training_center_caregiver_bun_bundle_due_date_id_caregiver_key2 on training_center_caregiver_bundle_certificate (cost=0.29..2.51 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=39,384)

  • Index Cond: ((bundle_due_date_id = training_center_bundle_due_date_2.id) AND (caregiver_id = caregiver.id))
  • Heap Fetches: 572
53. 0.000 0.000 ↓ 0.0 0

Index Only Scan using training_center_caregiver_bun_bundle_due_date_id_caregiver_key2 on training_center_caregiver_bundle_certificate training_center_caregiver_bundle_certificate_1 (cost=0.29..2,911.85 rows=83,313 width=8) (never executed)

  • Heap Fetches: 0
54. 39.384 39.384 ↓ 0.0 0 39,384

Index Only Scan using training_center_caregiver_bun_bundle_due_date_id_caregiver_key1 on training_center_caregiver_bundle_exemption (cost=0.29..2.50 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=39,384)

  • Index Cond: ((bundle_due_date_id = training_center_bundle_due_date_2.id) AND (caregiver_id = caregiver.id))
  • Heap Fetches: 0
55. 0.000 0.000 ↓ 0.0 0

Index Only Scan using training_center_caregiver_bun_bundle_due_date_id_caregiver_key1 on training_center_caregiver_bundle_exemption training_center_caregiver_bundle_exemption_1 (cost=0.29..173.82 rows=8,489 width=8) (never executed)

  • Heap Fetches: 0
Planning time : 37.598 ms
Execution time : 52,122.167 ms