explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Xof7

Settings
# exclusive inclusive rows x rows loops node
1. 1.782 431.552 ↑ 16.7 3,388 1

Group (cost=53,891.58..56,301.33 rows=56,700 width=505) (actual time=429.451..431.552 rows=3,388 loops=1)

  • Group Key: referrals.id, people_cte.name, referrers_cte.referrer, people_cte.mobile, people_cte.landline, latest_logs_cte.action_by, latest_logs_cte.activity, latest_logs_cte.created_at, latest_appointments_cte.appointment_date, availabilities_cte.availabilities, latest_appointments_cte.clinic_name, referral_testing_location_cte.testing_location, activities.sort_order, activities.name, master_stage_cte.master_stage, activities.classification
2.          

CTE latest_logs_cte

3. 17.048 214.141 ↓ 1.0 3,391 1

Unique (cost=20,581.40..21,079.60 rows=3,366 width=55) (actual time=154.290..214.141 rows=3,391 loops=1)

4. 151.217 197.093 ↓ 1.0 99,733 1

Sort (cost=20,581.40..20,830.50 rows=99,639 width=55) (actual time=154.289..197.093 rows=99,733 loops=1)

  • Sort Key: referrals_1.id, logs.created_at DESC
  • Sort Method: external merge Disk: 6768kB
5. 31.332 45.876 ↓ 1.0 99,733 1

Hash Join (cost=402.74..8,900.16 rows=99,639 width=55) (actual time=1.115..45.876 rows=99,733 loops=1)

  • Hash Cond: (logs.referral_id = referrals_1.id)
6. 13.541 13.541 ↓ 1.0 99,733 1

Seq Scan on logs (cost=0.00..7,127.39 rows=99,639 width=55) (actual time=0.079..13.541 rows=99,733 loops=1)

7. 0.513 1.003 ↓ 1.0 3,391 1

Hash (cost=360.66..360.66 rows=3,366 width=16) (actual time=1.003..1.003 rows=3,391 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 191kB
8. 0.490 0.490 ↓ 1.0 3,391 1

Seq Scan on referrals referrals_1 (cost=0.00..360.66 rows=3,366 width=16) (actual time=0.039..0.490 rows=3,391 loops=1)

9.          

CTE latest_appointments_cte

10. 0.881 13.962 ↓ 1.0 2,919 1

Unique (cost=1,169.17..1,206.43 rows=2,800 width=68) (actual time=12.318..13.962 rows=2,919 loops=1)

11. 6.073 13.081 ↓ 1.0 7,758 1

Sort (cost=1,169.17..1,187.80 rows=7,453 width=68) (actual time=12.317..13.081 rows=7,758 loops=1)

  • Sort Key: appointments.referral_id, (CASE WHEN (appointments.scheduled_at > timezone('UTC'::text, now())) THEN 0 ELSE 1 END), (abs(date_part('epoch'::text, ((timezone('UTC'::text, now()) - appointments.scheduled_at) / '3600'::double precision))))
  • Sort Method: quicksort Memory: 1283kB
12. 5.470 7.008 ↓ 1.0 7,758 1

Hash Join (cost=171.10..689.80 rows=7,453 width=68) (actual time=0.655..7.008 rows=7,758 loops=1)

  • Hash Cond: (appointments.location_id = locations.id)
13. 0.924 0.924 ↓ 1.0 7,758 1

Seq Scan on appointments (cost=0.00..248.53 rows=7,453 width=40) (actual time=0.006..0.924 rows=7,758 loops=1)

14. 0.297 0.614 ↓ 1.0 1,261 1

Hash (cost=155.49..155.49 rows=1,249 width=48) (actual time=0.614..0.614 rows=1,261 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 116kB
15. 0.317 0.317 ↓ 1.0 1,261 1

Seq Scan on locations (cost=0.00..155.49 rows=1,249 width=48) (actual time=0.004..0.317 rows=1,261 loops=1)

16.          

CTE availabilities_cte

17. 9.214 14.387 ↑ 1.0 1,762 1

GroupAggregate (cost=1,032.96..1,111.03 rows=1,812 width=48) (actual time=4.764..14.387 rows=1,762 loops=1)

  • Group Key: availabilities.referral_id
18. 2.179 5.173 ↑ 1.0 5,384 1

Sort (cost=1,032.96..1,046.82 rows=5,542 width=32) (actual time=4.729..5.173 rows=5,384 loops=1)

  • Sort Key: availabilities.referral_id
  • Sort Method: quicksort Memory: 613kB
19. 1.233 2.994 ↑ 1.0 5,384 1

Hash Join (cost=402.74..688.36 rows=5,542 width=32) (actual time=1.247..2.994 rows=5,384 loops=1)

  • Hash Cond: (availabilities.referral_id = referrals_2.id)
20. 0.530 0.530 ↑ 1.0 5,384 1

Seq Scan on availabilities (cost=0.00..209.42 rows=5,542 width=32) (actual time=0.005..0.530 rows=5,384 loops=1)

21. 0.499 1.231 ↓ 1.0 3,391 1

Hash (cost=360.66..360.66 rows=3,366 width=16) (actual time=1.231..1.231 rows=3,391 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 191kB
22. 0.732 0.732 ↓ 1.0 3,391 1

Seq Scan on referrals referrals_2 (cost=0.00..360.66 rows=3,366 width=16) (actual time=0.064..0.732 rows=3,391 loops=1)

23.          

CTE latest_referral_activity_cte

24. 6.047 101.004 ↓ 1.0 3,391 1

Unique (cost=5,783.57..5,983.03 rows=3,366 width=56) (actual time=85.712..101.004 rows=3,391 loops=1)

25. 60.764 94.957 ↓ 1.0 41,575 1

Sort (cost=5,783.57..5,883.30 rows=39,891 width=56) (actual time=85.709..94.957 rows=41,575 loops=1)

  • Sort Key: referral_activities.referral_id, referral_activities.created_at DESC
  • Sort Method: external merge Disk: 2680kB
26. 24.328 34.193 ↓ 1.0 41,575 1

Hash Join (cost=402.74..2,735.15 rows=39,891 width=56) (actual time=2.244..34.193 rows=41,575 loops=1)

  • Hash Cond: (referral_activities.referral_id = referrals_3.id)
27. 7.756 7.756 ↓ 1.0 41,575 1

Seq Scan on referral_activities (cost=0.00..1,783.91 rows=39,891 width=40) (actual time=0.104..7.756 rows=41,575 loops=1)

28. 0.935 2.109 ↓ 1.0 3,391 1

Hash (cost=360.66..360.66 rows=3,366 width=16) (actual time=2.109..2.109 rows=3,391 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 191kB
29. 1.174 1.174 ↓ 1.0 3,391 1

Seq Scan on referrals referrals_3 (cost=0.00..360.66 rows=3,366 width=16) (actual time=0.108..1.174 rows=3,391 loops=1)

30.          

CTE referrers_cte

31. 4.817 22.808 ↓ 1.0 3,388 1

Hash Join (cost=1,432.10..2,150.86 rows=3,369 width=48) (actual time=12.889..22.808 rows=3,388 loops=1)

  • Hash Cond: (contacts.person_id = referrers.id)
32. 1.469 7.414 ↓ 1.0 3,388 1

Hash Join (cost=402.74..1,066.74 rows=3,369 width=32) (actual time=2.233..7.414 rows=3,388 loops=1)

  • Hash Cond: (contacts.referral_id = referrals_4.id)
33. 3.874 3.874 ↓ 1.0 3,388 1

Seq Scan on contacts (cost=0.00..617.62 rows=3,385 width=32) (actual time=0.133..3.874 rows=3,388 loops=1)

  • Filter: (relationship = 2)
  • Rows Removed by Filter: 13457
34. 0.906 2.071 ↓ 1.0 3,391 1

Hash (cost=360.66..360.66 rows=3,366 width=16) (actual time=2.071..2.071 rows=3,391 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 191kB
35. 1.165 1.165 ↓ 1.0 3,391 1

Seq Scan on referrals referrals_4 (cost=0.00..360.66 rows=3,366 width=16) (actual time=0.112..1.165 rows=3,391 loops=1)

36. 5.394 10.577 ↑ 1.0 15,468 1

Hash (cost=831.94..831.94 rows=15,794 width=29) (actual time=10.577..10.577 rows=15,468 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1072kB
37. 5.183 5.183 ↑ 1.0 15,468 1

Seq Scan on people referrers (cost=0.00..831.94 rows=15,794 width=29) (actual time=0.114..5.183 rows=15,468 loops=1)

38.          

CTE people_cte

39. 5.266 9.442 ↓ 1.0 3,391 1

Hash Join (cost=402.74..1,335.98 rows=3,366 width=55) (actual time=4.980..9.442 rows=3,391 loops=1)

  • Hash Cond: (people.id = referrals_5.person_id)
40. 2.439 2.439 ↑ 1.0 15,468 1

Seq Scan on people (cost=0.00..831.94 rows=15,794 width=36) (actual time=0.110..2.439 rows=15,468 loops=1)

41. 0.783 1.737 ↓ 1.0 3,391 1

Hash (cost=360.66..360.66 rows=3,366 width=16) (actual time=1.737..1.737 rows=3,391 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 191kB
42. 0.954 0.954 ↓ 1.0 3,391 1

Seq Scan on referrals referrals_5 (cost=0.00..360.66 rows=3,366 width=16) (actual time=0.039..0.954 rows=3,391 loops=1)

43.          

CTE referral_testing_location_cte

44. 7.296 7.296 ↓ 1.0 3,391 1

Seq Scan on referrals referrals_6 (cost=0.00..377.49 rows=3,366 width=48) (actual time=0.125..7.296 rows=3,391 loops=1)

45.          

CTE master_stage_cte

46. 0.004 0.004 ↑ 1.0 8 1

Values Scan on "*VALUES*" (cost=0.00..0.10 rows=8 width=36) (actual time=0.002..0.004 rows=8 loops=1)

47. 2.993 429.770 ↑ 16.7 3,388 1

Sort (cost=20,647.07..20,788.82 rows=56,700 width=505) (actual time=429.450..429.770 rows=3,388 loops=1)

  • Sort Key: referrals.id, people_cte.name, referrers_cte.referrer, people_cte.mobile, people_cte.landline, latest_logs_cte.action_by, latest_logs_cte.activity, latest_logs_cte.created_at, latest_appointments_cte.appointment_date, availabilities_cte.availabilities, latest_appointments_cte.clinic_name, referral_testing_location_cte.testing_location, activities.sort_order, activities.name, master_stage_cte.master_stage, activities.classification
  • Sort Method: quicksort Memory: 1938kB
48. 0.856 426.777 ↑ 16.7 3,388 1

Hash Left Join (cost=1,738.72..3,185.31 rows=56,700 width=505) (actual time=422.193..426.777 rows=3,388 loops=1)

  • Hash Cond: (activities.master_stage = master_stage_cte.id)
49. 0.984 425.909 ↑ 16.7 3,388 1

Merge Left Join (cost=1,738.46..2,405.42 rows=56,700 width=477) (actual time=422.172..425.909 rows=3,388 loops=1)

  • Merge Cond: (referrals.id = availabilities_cte.referral_id)
50. 1.697 409.095 ↑ 16.7 3,388 1

Merge Join (cost=1,604.16..1,671.53 rows=56,700 width=445) (actual time=406.504..409.095 rows=3,388 loops=1)

  • Merge Cond: (referrals.id = referrers_cte.referral_id)
51. 4.145 381.056 ↓ 1.0 3,391 1

Sort (cost=1,339.39..1,347.81 rows=3,366 width=429) (actual time=380.622..381.056 rows=3,391 loops=1)

  • Sort Key: referrals.id
  • Sort Method: quicksort Memory: 1844kB
52. 1.781 376.911 ↓ 1.0 3,391 1

Hash Left Join (cost=1,009.24..1,142.20 rows=3,366 width=429) (actual time=355.444..376.911 rows=3,391 loops=1)

  • Hash Cond: (latest_referral_activity_cte.activity_id = activities.id)
53. 2.968 375.078 ↓ 1.0 3,391 1

Hash Right Join (cost=1,006.34..1,119.95 rows=3,366 width=410) (actual time=355.379..375.078 rows=3,391 loops=1)

  • Hash Cond: (latest_referral_activity_cte.referral_id = referrals.id)
54. 102.488 102.488 ↓ 1.0 3,391 1

CTE Scan on latest_referral_activity_cte (cost=0.00..67.32 rows=3,366 width=32) (actual time=85.714..102.488 rows=3,391 loops=1)

55. 3.655 269.622 ↓ 1.0 3,391 1

Hash (cost=964.27..964.27 rows=3,366 width=394) (actual time=269.622..269.622 rows=3,391 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 1036kB
56. 3.696 265.967 ↓ 1.0 3,391 1

Hash Right Join (cost=850.67..964.27 rows=3,366 width=394) (actual time=201.137..265.967 rows=3,391 loops=1)

  • Hash Cond: (latest_logs_cte.referral_id = referrals.id)
57. 215.463 215.463 ↓ 1.0 3,391 1

CTE Scan on latest_logs_cte (cost=0.00..67.32 rows=3,366 width=88) (actual time=154.292..215.463 rows=3,391 loops=1)

58. 1.948 46.808 ↓ 1.0 3,391 1

Hash (cost=808.59..808.59 rows=3,366 width=322) (actual time=46.808..46.808 rows=3,391 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 901kB
59. 1.525 44.860 ↓ 1.0 3,391 1

Hash Join (cost=694.99..808.59 rows=3,366 width=322) (actual time=35.213..44.860 rows=3,391 loops=1)

  • Hash Cond: (referral_testing_location_cte.referral_id = referrals.id)
60. 8.283 8.283 ↓ 1.0 3,391 1

CTE Scan on referral_testing_location_cte (cost=0.00..67.32 rows=3,366 width=48) (actual time=0.127..8.283 rows=3,391 loops=1)

61. 1.791 35.052 ↓ 1.0 3,391 1

Hash (cost=652.91..652.91 rows=3,366 width=274) (actual time=35.052..35.052 rows=3,391 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 801kB
62. 1.391 33.261 ↓ 1.0 3,391 1

Hash Join (cost=539.31..652.91 rows=3,366 width=274) (actual time=26.235..33.261 rows=3,391 loops=1)

  • Hash Cond: (people_cte.person_id = referrals.person_id)
63. 10.670 10.670 ↓ 1.0 3,391 1

CTE Scan on people_cte (cost=0.00..67.32 rows=3,366 width=112) (actual time=4.981..10.670 rows=3,391 loops=1)

64. 1.518 21.200 ↓ 1.0 3,391 1

Hash (cost=497.24..497.24 rows=3,366 width=194) (actual time=21.200..21.200 rows=3,391 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 761kB
65. 1.451 19.682 ↓ 1.0 3,391 1

Hash Right Join (cost=402.74..497.24 rows=3,366 width=194) (actual time=15.792..19.682 rows=3,391 loops=1)

  • Hash Cond: (latest_appointments_cte.referral_id = referrals.id)
66. 14.792 14.792 ↓ 1.0 2,919 1

CTE Scan on latest_appointments_cte (cost=0.00..56.00 rows=2,800 width=56) (actual time=12.321..14.792 rows=2,919 loops=1)

67. 1.785 3.439 ↓ 1.0 3,391 1

Hash (cost=360.66..360.66 rows=3,366 width=154) (actual time=3.439..3.439 rows=3,391 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 663kB
68. 1.654 1.654 ↓ 1.0 3,391 1

Seq Scan on referrals (cost=0.00..360.66 rows=3,366 width=154) (actual time=0.063..1.654 rows=3,391 loops=1)

69. 0.026 0.052 ↓ 1.1 46 1

Hash (cost=2.40..2.40 rows=40 width=51) (actual time=0.052..0.052 rows=46 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
70. 0.026 0.026 ↓ 1.1 46 1

Seq Scan on activities (cost=0.00..2.40 rows=40 width=51) (actual time=0.009..0.026 rows=46 loops=1)

71. 1.879 26.342 ↓ 1.0 3,388 1

Sort (cost=264.77..273.19 rows=3,369 width=48) (actual time=25.876..26.342 rows=3,388 loops=1)

  • Sort Key: referrers_cte.referral_id
  • Sort Method: quicksort Memory: 361kB
72. 24.463 24.463 ↓ 1.0 3,388 1

CTE Scan on referrers_cte (cost=0.00..67.38 rows=3,369 width=48) (actual time=12.892..24.463 rows=3,388 loops=1)

73. 0.690 15.830 ↑ 1.0 1,764 1

Sort (cost=134.30..138.83 rows=1,812 width=48) (actual time=15.665..15.830 rows=1,764 loops=1)

  • Sort Key: availabilities_cte.referral_id
  • Sort Method: quicksort Memory: 519kB
74. 15.140 15.140 ↑ 1.0 1,762 1

CTE Scan on availabilities_cte (cost=0.00..36.24 rows=1,812 width=48) (actual time=4.765..15.140 rows=1,762 loops=1)

75. 0.003 0.012 ↑ 1.0 8 1

Hash (cost=0.16..0.16 rows=8 width=36) (actual time=0.012..0.012 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
76. 0.009 0.009 ↑ 1.0 8 1

CTE Scan on master_stage_cte (cost=0.00..0.16 rows=8 width=36) (actual time=0.004..0.009 rows=8 loops=1)

Planning time : 3.518 ms
Execution time : 435.840 ms