explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ogto

Settings
# exclusive inclusive rows x rows loops node
1. 131.590 13,144.878 ↓ 39,384.0 39,384 1

WindowAgg (cost=89,003.14..89,023.73 rows=1 width=43) (actual time=12,666.877..13,144.878 rows=39,384 loops=1)

2. 20.718 12,669.904 ↓ 39,384.0 39,384 1

Sort (cost=89,003.14..89,003.15 rows=1 width=66) (actual time=12,666.755..12,669.904 rows=39,384 loops=1)

  • Sort Key: training_center_bundle_2.id, caregiver_1.id, (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: 7,075kB
3. 255.962 12,649.186 ↓ 39,384.0 39,384 1

Nested Loop (cost=9,570.59..89,003.13 rows=1 width=66) (actual time=4,997.337..12,649.186 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 AND (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,113
4. 25.043 11,831.711 ↓ 118,497.0 118,497 1

Nested Loop (cost=9,570.17..88,992.51 rows=1 width=82) (actual time=4,997.259..11,831.711 rows=118,497 loops=1)

5. 81.748 11,569.674 ↓ 118,497.0 118,497 1

Nested Loop (cost=9,569.75..88,992.04 rows=1 width=74) (actual time=4,997.248..11,569.674 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,649
6. 112.544 11,197.634 ↓ 145,146.0 145,146 1

Nested Loop (cost=9,569.32..88,989.40 rows=1 width=80) (actual time=4,997.229..11,197.634 rows=145,146 loops=1)

  • Join Filter: (training_center_bundle_2.id = training_center_bundle_due_date_1.bundle_id)
7. 128.809 10,939.944 ↓ 36,286.5 145,146 1

Nested Loop (cost=9,569.05..88,987.95 rows=4 width=78) (actual time=4,997.217..10,939.944 rows=145,146 loops=1)

8. 4,564.576 10,665.989 ↓ 36,286.5 145,146 1

Nested Loop (cost=9,568.77..88,986.55 rows=4 width=36) (actual time=4,997.188..10,665.989 rows=145,146 loops=1)

  • Join Filter: (training_center_bundle_2.id = training_center_bundle.id)
  • Rows Removed by Join Filter: 70,105,518
9. 2.529 2.529 ↑ 1.0 484 1

Index Only Scan using training_center_bundle_pkey on training_center_bundle (cost=0.27..20.72 rows=493 width=4) (actual time=0.033..2.529 rows=484 loops=1)

  • Heap Fetches: 484
10. 3,502.784 6,098.884 ↓ 36,286.5 145,146 484

Materialize (cost=9,568.50..88,936.26 rows=4 width=32) (actual time=1.250..12.601 rows=145,146 loops=484)

11. 579.171 2,596.100 ↓ 36,286.5 145,146 1

Merge Join (cost=9,568.50..88,936.24 rows=4 width=32) (actual time=604.895..2,596.100 rows=145,146 loops=1)

  • Merge Cond: (caregiver.id = caregiver_1.id)
  • Join Filter: (training_center_bundle_2.id = training_center_bundle_due_date_2.bundle_id)
  • Rows Removed by Join Filter: 1,064,404
12. 970.091 1,330.034 ↓ 1.4 6,576,901 1

Nested Loop (cost=0.70..67,509.13 rows=4,728,492 width=20) (actual time=0.057..1,330.034 rows=6,576,901 loops=1)

13. 96.866 96.866 ↓ 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.022..96.866 rows=263,077 loops=1)

  • Heap Fetches: 30,296
14. 262.959 263.077 ↓ 1.4 25 263,077

Materialize (cost=0.28..38.93 rows=18 width=16) (actual time=0.000..0.001 rows=25 loops=263,077)

15. 0.012 0.118 ↓ 1.4 25 1

Nested Loop (cost=0.28..38.84 rows=18 width=16) (actual time=0.030..0.118 rows=25 loops=1)

16. 0.066 0.066 ↑ 1.0 10 1

Seq Scan on training_center_bundle training_center_bundle_1 (cost=0.00..14.16 rows=10 width=8) (actual time=0.015..0.066 rows=10 loops=1)

  • Filter: (agency_id = 57)
  • Rows Removed by Filter: 474
17. 0.040 0.040 ↑ 1.0 2 10

Index Scan using tmp_index_654234234 on training_center_bundle_due_date training_center_bundle_due_date_2 (cost=0.28..2.45 rows=2 width=8) (actual time=0.003..0.004 rows=2 loops=10)

  • Index Cond: (bundle_id = training_center_bundle_1.id)
18. 63.653 686.895 ↓ 10,799.3 1,209,526 1

Materialize (cost=9,567.80..9,570.88 rows=112 width=16) (actual time=604.677..686.895 rows=1,209,526 loops=1)

19. 11.516 623.242 ↓ 432.0 48,382 1

Unique (cost=9,567.80..9,569.48 rows=112 width=25) (actual time=604.673..623.242 rows=48,382 loops=1)

20. 29.880 611.726 ↓ 432.0 48,382 1

Sort (cost=9,567.80..9,568.08 rows=112 width=25) (actual time=604.672..611.726 rows=48,382 loops=1)

  • Sort Key: caregiver_1.id, caregiver_agency_assoc_3.id, training_center_bundle_2.id, training_center_bundle_2.active, training_center_bundle_2.published_at
  • Sort Method: quicksort Memory: 5,316kB
21. 3.777 581.846 ↓ 432.0 48,382 1

Append (cost=1,036.42..9,563.99 rows=112 width=25) (actual time=0.831..581.846 rows=48,382 loops=1)

22. 8.686 577.990 ↓ 435.9 48,382 1

Gather (cost=1,036.42..9,511.54 rows=111 width=25) (actual time=0.831..577.990 rows=48,382 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
23. 62.369 569.304 ↓ 372.2 24,191 2 / 2

Nested Loop (cost=36.42..8,500.44 rows=65 width=25) (actual time=0.287..569.304 rows=24,191 loops=2)

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

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

25. 17.775 34.227 ↓ 36.1 102,648 2 / 2

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

  • Hash Cond: (caregiver_office_assoc.office_id = training_center_bundle_caregiver_certifications.office_id)
26. 16.336 16.336 ↑ 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.096..16.336 rows=14,664 loops=2)

  • Filter: (agency_id = 57)
  • Rows Removed by Filter: 168,336
27. 0.004 0.116 ↑ 3.9 7 2 / 2

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

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

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

29. 0.071 0.071 ↑ 1.2 4 2 / 2

Seq Scan on training_center_bundle training_center_bundle_2 (cost=0.00..14.16 rows=5 width=17) (actual time=0.022..0.071 rows=4 loops=2)

  • Filter: ((active IS TRUE) AND (published_at IS NOT NULL) AND (agency_id = 57))
  • Rows Removed by Filter: 480
30. 0.036 0.036 ↑ 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.009..0.009 rows=2 loops=8)

  • Index Cond: (bundle_id = training_center_bundle_2.id)
  • Heap Fetches: 7
31. 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
32. 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)
33. 0.001 0.079 ↓ 0.0 0 1

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

34. 0.003 0.078 ↓ 0.0 0 1

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

35. 0.067 0.067 ↑ 1.2 4 1

Seq Scan on training_center_bundle training_center_bundle_3 (cost=0.00..14.16 rows=5 width=17) (actual time=0.022..0.067 rows=4 loops=1)

  • Filter: ((active IS TRUE) AND (published_at IS NOT NULL) AND (agency_id = 57))
  • Rows Removed by Filter: 480
36. 0.000 0.008 ↓ 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.002..0.002 rows=0 loops=4)

  • Recheck Cond: (bundle_id = training_center_bundle_3.id)
37. 0.008 0.008 ↓ 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.002 rows=0 loops=4)

  • Index Cond: (bundle_id = training_center_bundle_3.id)
38. 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)
39. 145.146 145.146 ↑ 1.0 1 145,146

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=145,146)

  • Index Cond: (id = training_center_bundle_due_date_2.id)
40. 145.146 145.146 ↑ 1.0 1 145,146

Index Scan using training_center_bundle_due_date_pkey on training_center_bundle_due_date training_center_bundle_due_date_1 (cost=0.28..0.35 rows=1 width=18) (actual time=0.001..0.001 rows=1 loops=145,146)

  • Index Cond: (id = training_center_bundle_due_date.id)
41. 290.292 290.292 ↑ 1.0 1 145,146

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=145,146)

  • Index Cond: (id = caregiver_agency_assoc_3.id)
42. 236.994 236.994 ↑ 1.0 1 118,497

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=118,497)

  • Index Cond: (id = caregiver_agency_assoc_2.id)
43. 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)
44.          

SubPlan (for Nested Loop)

45. 91.239 91.239 ↓ 0.0 0 91,239

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=91,239)

  • Index Cond: ((bundle_due_date_id = training_center_bundle_due_date.id) AND (caregiver_id = caregiver_1.id))
46. 115.008 115.008 ↓ 0.0 0 115,008

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=115,008)

  • Index Cond: ((bundle_due_date_id = training_center_bundle_due_date.id) AND (caregiver_id = caregiver_1.id))
47. 78.888 78.888 ↓ 0.0 0 78,888

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,888)

  • 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_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))
49.          

SubPlan (for WindowAgg)

50. 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))
51. 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))
52. 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,481
53. 35.940 35.940 ↓ 0.0 0 35,940

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.001..0.001 rows=0 loops=35,940)

  • Index Cond: ((bundle_due_date_id = training_center_bundle_due_date_2.id) AND (caregiver_id = caregiver.id))
  • Heap Fetches: 161
54. 71.140 71.140 ↓ 0.0 0 35,570

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,570)

  • Index Cond: ((caregiver_id = caregiver.id) AND (bundle_due_date_id = training_center_bundle_due_date_2.id))
  • Heap Fetches: 1
55. 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
56. 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
57. 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
58. 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 : 26.069 ms
Execution time : 13,150.442 ms