explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZyvI

Settings
# exclusive inclusive rows x rows loops node
1. 22.543 346.866 ↑ 1.6 3,394 1

GroupAggregate (cost=32,668.80..33,140.30 rows=5,547 width=463) (actual time=322.482..346.866 rows=3,394 loops=1)

  • Group Key: referrals.id, (concat(people.first_name, ' ', people.last_name)), (concat(referrers.first_name, ' ', referrers.last_name)), people.mobile, people.landline, latest_logs_cte.action_by, latest_logs_cte.activity, latest_logs_cte.created_at, latest_appointments_cte.appointment_date, latest_appointments_cte.clinic_name, activities.sort_order, activities.name, activities.master_stage, activities.classification
2.          

CTE latest_logs_cte

3. 8.559 176.670 ↓ 1.0 3,391 1

Unique (cost=20,589.99..21,088.43 rows=3,366 width=55) (actual time=146.910..176.670 rows=3,391 loops=1)

4. 123.165 168.111 ↓ 1.0 99,768 1

Sort (cost=20,589.99..20,839.21 rows=99,688 width=55) (actual time=146.909..168.111 rows=99,768 loops=1)

  • Sort Key: referrals_1.id, logs.created_at DESC
  • Sort Method: external merge Disk: 6768kB
5. 30.505 44.946 ↓ 1.0 99,768 1

Hash Join (cost=402.74..8,904.33 rows=99,688 width=55) (actual time=1.231..44.946 rows=99,768 loops=1)

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

Seq Scan on logs (cost=0.00..7,130.88 rows=99,688 width=55) (actual time=0.082..13.317 rows=99,768 loops=1)

7. 0.504 1.124 ↓ 1.0 3,391 1

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

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

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

9.          

CTE latest_appointments_cte

10. 0.931 13.169 ↓ 1.0 2,920 1

Unique (cost=1,169.17..1,206.43 rows=2,800 width=68) (actual time=11.511..13.169 rows=2,920 loops=1)

11. 5.508 12.238 ↓ 1.0 7,761 1

Sort (cost=1,169.17..1,187.80 rows=7,453 width=68) (actual time=11.511..12.238 rows=7,761 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.245 6.730 ↓ 1.0 7,761 1

Hash Join (cost=171.10..689.80 rows=7,453 width=68) (actual time=0.626..6.730 rows=7,761 loops=1)

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

Seq Scan on appointments (cost=0.00..248.53 rows=7,453 width=40) (actual time=0.003..0.891 rows=7,761 loops=1)

14. 0.270 0.594 ↓ 1.0 1,261 1

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

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

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

16.          

CTE availabilities_cte

17. 0.829 0.829 ↑ 1.0 5,384 1

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

18.          

CTE latest_referral_activity_cte

19. 3.055 56.653 ↓ 1.0 3,391 1

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

20. 34.848 53.598 ↓ 1.0 41,593 1

Sort (cost=5,783.57..5,883.30 rows=39,891 width=56) (actual time=48.794..53.598 rows=41,593 loops=1)

  • Sort Key: referral_activities.referral_id, referral_activities.created_at DESC
  • Sort Method: external merge Disk: 2680kB
21. 13.048 18.750 ↓ 1.0 41,593 1

Hash Join (cost=402.74..2,735.15 rows=39,891 width=56) (actual time=1.335..18.750 rows=41,593 loops=1)

  • Hash Cond: (referral_activities.referral_id = referrals_2.id)
22. 4.476 4.476 ↓ 1.0 41,593 1

Seq Scan on referral_activities (cost=0.00..1,783.91 rows=39,891 width=40) (actual time=0.086..4.476 rows=41,593 loops=1)

23. 0.478 1.226 ↓ 1.0 3,391 1

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

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

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

25. 24.920 324.323 ↓ 1.3 7,022 1

Sort (cost=4,181.50..4,195.37 rows=5,547 width=554) (actual time=322.424..324.323 rows=7,022 loops=1)

  • Sort Key: referrals.id, (concat(people.first_name, ' ', people.last_name)), (concat(referrers.first_name, ' ', referrers.last_name)), people.mobile, people.landline, latest_logs_cte.action_by, latest_logs_cte.activity, latest_logs_cte.created_at, latest_appointments_cte.appointment_date, latest_appointments_cte.clinic_name, activities.sort_order, activities.name, activities.master_stage, activities.classification
  • Sort Method: external merge Disk: 3408kB
26. 11.550 299.403 ↓ 1.3 7,022 1

Hash Right Join (cost=3,621.72..3,836.55 rows=5,547 width=554) (actual time=285.631..299.403 rows=7,022 loops=1)

  • Hash Cond: (availabilities_cte.referral_id = referrals.id)
27. 2.265 2.265 ↑ 1.0 5,384 1

CTE Scan on availabilities_cte (cost=0.00..110.84 rows=5,542 width=32) (actual time=0.005..2.265 rows=5,384 loops=1)

28. 2.975 285.588 ↓ 1.0 3,394 1

Hash (cost=3,579.61..3,579.61 rows=3,369 width=500) (actual time=285.588..285.588 rows=3,394 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 1697kB
29. 1.007 282.613 ↓ 1.0 3,394 1

Hash Left Join (cost=2,589.05..3,579.61 rows=3,369 width=500) (actual time=277.907..282.613 rows=3,394 loops=1)

  • Hash Cond: (latest_referral_activity_cte.activity_id = activities.id)
30. 1.073 281.579 ↓ 1.0 3,394 1

Hash Left Join (cost=2,586.15..3,557.33 rows=3,369 width=481) (actual time=277.866..281.579 rows=3,394 loops=1)

  • Hash Cond: (contacts.person_id = referrers.id)
31. 3.283 274.874 ↓ 1.0 3,394 1

Hash Join (cost=1,556.79..2,481.64 rows=3,369 width=484) (actual time=272.215..274.874 rows=3,394 loops=1)

  • Hash Cond: (people.id = referrals.person_id)
32. 1.756 1.756 ↑ 1.0 15,468 1

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

33. 2.141 269.835 ↓ 1.0 3,394 1

Hash (cost=1,514.67..1,514.67 rows=3,369 width=480) (actual time=269.835..269.835 rows=3,394 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 1617kB
34. 1.546 267.694 ↓ 1.0 3,394 1

Hash Right Join (cost=850.67..1,514.67 rows=3,369 width=480) (actual time=264.055..267.694 rows=3,394 loops=1)

  • Hash Cond: (contacts.referral_id = referrals.id)
35. 2.205 2.205 ↓ 1.0 3,388 1

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

  • Filter: (relationship = 2)
  • Rows Removed by Filter: 13457
36. 2.118 263.943 ↓ 1.0 3,391 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 1563kB
37. 1.558 261.825 ↓ 1.0 3,391 1

Hash Right Join (cost=694.99..808.59 rows=3,366 width=464) (actual time=251.576..261.825 rows=3,391 loops=1)

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

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

39. 2.271 202.753 ↓ 1.0 3,391 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 1510kB
40. 1.920 200.482 ↓ 1.0 3,391 1

Hash Right Join (cost=539.31..652.91 rows=3,366 width=448) (actual time=168.063..200.482 rows=3,391 loops=1)

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

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

42. 2.159 21.122 ↓ 1.0 3,391 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 1373kB
43. 1.468 18.963 ↓ 1.0 3,391 1

Hash Right Join (cost=402.74..497.24 rows=3,366 width=376) (actual time=15.111..18.963 rows=3,391 loops=1)

  • Hash Cond: (latest_appointments_cte.referral_id = referrals.id)
44. 13.923 13.923 ↓ 1.0 2,920 1

CTE Scan on latest_appointments_cte (cost=0.00..56.00 rows=2,800 width=56) (actual time=11.513..13.923 rows=2,920 loops=1)

45. 1.905 3.572 ↓ 1.0 3,391 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 1275kB
46. 1.667 1.667 ↓ 1.0 3,391 1

Seq Scan on referrals (cost=0.00..360.66 rows=3,366 width=336) (actual time=0.082..1.667 rows=3,391 loops=1)

47. 3.115 5.632 ↑ 1.0 15,468 1

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

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

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

49. 0.013 0.027 ↓ 1.1 46 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
50. 0.014 0.014 ↓ 1.1 46 1

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

Planning time : 2.217 ms
Execution time : 351.600 ms