explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JQGB

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

WindowAgg (cost=3,557,627.86..3,557,648.44 rows=1 width=43) (actual time=51,993.702..52,446.877 rows=39,384 loops=1)

2. 35.190 51,996.880 ↓ 39,384.0 39,384 1

Sort (cost=3,557,627.86..3,557,627.86 rows=1 width=66) (actual time=51,993.619..51,996.880 rows=39,384 loops=1)

  • Sort Key: training_center_bundle_1.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. 15,927.179 51,961.690 ↓ 39,384.0 39,384 1

Merge Join (cost=9,570.32..3,557,627.85 rows=1 width=66) (actual time=594.405..51,961.690 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
4. 22,500.577 32,605.125 ↑ 1.0 228,349,969 1

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

5. 107.622 107.622 ↓ 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.025..107.622 rows=263,077 loops=1)

  • Heap Fetches: 30,126
6. 9,996.698 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)

7. 0.228 0.228 ↑ 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.003..0.228 rows=868 loops=1)

8. 1,504.666 3,390.002 ↓ 5,697,407.5 34,184,445 1

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

9. 35.679 1,885.336 ↓ 6,564.0 39,384 1

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

10. 68.025 1,810.273 ↓ 6,564.0 39,384 1

Nested Loop (cost=9,569.62..10,621.23 rows=6 width=66) (actual time=591.431..1,810.273 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
11. 222.995 1,672.344 ↓ 3,291.2 39,495 1

Nested Loop (cost=9,569.20..10,585.06 rows=12 width=78) (actual time=591.407..1,672.344 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
12. 23.038 1,133.356 ↓ 1,087.1 118,497 1

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

13. 37.254 873.324 ↓ 1,087.1 118,497 1

Nested Loop (cost=9,568.50..9,941.20 rows=109 width=28) (actual time=591.345..873.324 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
14. 29.222 739.304 ↓ 432.0 48,383 1

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

15. 13.347 613.316 ↓ 432.0 48,383 1

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

16. 34.239 599.969 ↓ 432.0 48,383 1

Sort (cost=9,567.80..9,568.08 rows=112 width=25) (actual time=591.302..599.969 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
17. 3.853 565.730 ↓ 432.0 48,383 1

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

18. 19.243 561.752 ↓ 435.9 48,383 1

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

  • Workers Planned: 1
  • Workers Launched: 1
19. 48.377 542.509 ↓ 372.2 24,192 2 / 2

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

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

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

21. 17.034 32.774 ↓ 36.1 102,648 2 / 2

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

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

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
24. 0.004 0.095 ↑ 3.9 7 2 / 2

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

25. 0.067 0.067 ↑ 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.023..0.067 rows=4 loops=2)

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

  • Index Cond: (bundle_id = training_center_bundle_1.id)
  • Heap Fetches: 7
27. 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
28. 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)
29. 0.000 0.125 ↓ 0.0 0 1

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

30. 0.005 0.125 ↓ 0.0 0 1

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

31. 0.108 0.108 ↑ 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.035..0.108 rows=4 loops=1)

  • Filter: ((active IS TRUE) AND (published_at IS NOT NULL) AND (agency_id = 57))
  • Rows Removed by Filter: 480
32. 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_2.id)
33. 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_2.id)
34. 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)
35. 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)
36. 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)
37. 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)
38. 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)
39.          

SubPlan (for Nested Loop)

40. 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))
41. 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))
42. 39.495 39.495 ↑ 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.001..0.001 rows=1 loops=39,495)

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

SubPlan (for Nested Loop)

44. 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))
45. 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
46.          

SubPlan (for Merge Join)

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.          

SubPlan (for WindowAgg)

49. 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))
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 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))
51. 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,479
52. 35.941 35.941 ↓ 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.001..0.001 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
53. 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
54. 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
55. 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
56. 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
57. 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 : 25.848 ms
Execution time : 52,450.080 ms