explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vRDF

Settings
# exclusive inclusive rows x rows loops node
1. 144.183 11,724.273 ↓ 39,384.0 39,384 1

WindowAgg (cost=89,003.14..89,023.73 rows=1 width=43) (actual time=11,273.420..11,724.273 rows=39,384 loops=1)

2. 16.815 11,276.090 ↓ 39,384.0 39,384 1

Sort (cost=89,003.14..89,003.15 rows=1 width=66) (actual time=11,273.318..11,276.090 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. 271.190 11,259.275 ↓ 39,384.0 39,384 1

Nested Loop (cost=9,570.59..89,003.13 rows=1 width=66) (actual time=4,383.626..11,259.275 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. 110.136 10,545.069 ↓ 118,497.0 118,497 1

Nested Loop (cost=9,570.17..88,992.51 rows=1 width=82) (actual time=4,383.554..10,545.069 rows=118,497 loops=1)

5. 29.588 10,316.436 ↓ 118,497.0 118,497 1

Nested Loop (cost=9,569.75..88,992.04 rows=1 width=74) (actual time=4,383.545..10,316.436 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. 82.989 9,996.556 ↓ 145,146.0 145,146 1

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

  • Join Filter: (training_center_bundle_2.id = training_center_bundle_due_date_1.bundle_id)
7. 91.670 9,768.421 ↓ 36,286.5 145,146 1

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

8. 4,171.540 9,531.605 ↓ 36,286.5 145,146 1

Nested Loop (cost=9,568.77..88,986.55 rows=4 width=36) (actual time=4,383.492..9,531.605 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.185 2.185 ↑ 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.032..2.185 rows=484 loops=1)

  • Heap Fetches: 484
10. 3,148.521 5,357.880 ↓ 36,286.5 145,146 484

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

11. 473.710 2,209.359 ↓ 36,286.5 145,146 1

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

  • Merge Cond: (caregiver.id = caregiver_1.id)
  • Join Filter: (training_center_bundle_2.id = training_center_bundle_1.id)
  • Rows Removed by Join Filter: 1,064,404
12. 771.315 1,107.398 ↓ 1.4 6,576,901 1

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

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

  • Heap Fetches: 30,362
14. 262.963 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.010 0.114 ↓ 1.4 25 1

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

16. 0.064 0.064 ↑ 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.016..0.064 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. 55.037 628.251 ↓ 10,799.3 1,209,526 1

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

19. 9.621 573.214 ↓ 432.0 48,382 1

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

20. 25.834 563.593 ↓ 432.0 48,382 1

Sort (cost=9,567.80..9,568.08 rows=112 width=25) (actual time=558.165..563.593 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.505 537.759 ↓ 432.0 48,382 1

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

22. 23.649 534.165 ↓ 435.9 48,382 1

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

  • Workers Planned: 1
  • Workers Launched: 1
23. 33.732 510.516 ↓ 372.2 24,191 2 / 2

Nested Loop (cost=36.42..8,500.44 rows=65 width=25) (actual time=0.266..510.516 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. 36.468 271.558 ↓ 240.3 102,613 2 / 2

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

25. 15.650 29.794 ↓ 36.1 102,648 2 / 2

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

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

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

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

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

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

29. 0.066 0.066 ↑ 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.066 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.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_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.089 ↓ 0.0 0 1

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

34. 0.003 0.088 ↓ 0.0 0 1

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

35. 0.073 0.073 ↑ 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.026..0.073 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.004 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_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.002..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. 118.497 118.497 ↑ 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.001..0.001 rows=1 loops=118,497)

  • Index Cond: (id = caregiver_agency_assoc_2.id)
43. 118.497 118.497 ↑ 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.001..0.001 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. 39.384 39.384 ↓ 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.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: 2,482
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 : 24.802 ms
Execution time : 11,729.645 ms