explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HVmi

Settings
# exclusive inclusive rows x rows loops node
1. 21.334 323.095 ↑ 1.6 3,551 1

GroupAggregate (cost=32,696.84..33,003.49 rows=5,841 width=338) (actual time=300.783..323.095 rows=3,551 loops=1)

  • Group Key: referrals.id, (concat(people.first_name, ' ', people.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.name
2.          

CTE latest_logs_cte

3. 9.145 184.277 ↓ 1.0 3,551 1

Unique (cost=21,638.11..22,161.00 rows=3,543 width=55) (actual time=153.045..184.277 rows=3,551 loops=1)

4. 128.284 175.132 ↑ 1.0 104,543 1

Sort (cost=21,638.11..21,899.56 rows=104,577 width=55) (actual time=153.045..175.132 rows=104,543 loops=1)

  • Sort Key: referrals_1.id, logs.created_at DESC
  • Sort Method: external merge Disk: 7096kB
5. 31.968 46.848 ↑ 1.0 104,543 1

Hash Join (cost=433.72..9,342.42 rows=104,577 width=55) (actual time=1.187..46.848 rows=104,543 loops=1)

  • Hash Cond: (logs.referral_id = referrals_1.id)
6. 13.799 13.799 ↑ 1.0 104,543 1

Seq Scan on logs (cost=0.00..7,470.77 rows=104,577 width=55) (actual time=0.080..13.799 rows=104,543 loops=1)

7. 0.522 1.081 ↓ 1.0 3,551 1

Hash (cost=389.43..389.43 rows=3,543 width=16) (actual time=1.081..1.081 rows=3,551 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 199kB
8. 0.559 0.559 ↓ 1.0 3,551 1

Seq Scan on referrals referrals_1 (cost=0.00..389.43 rows=3,543 width=16) (actual time=0.038..0.559 rows=3,551 loops=1)

9.          

CTE latest_appointments_cte

10. 0.937 13.864 ↓ 1.0 3,049 1

Unique (cost=1,224.63..1,264.24 rows=2,997 width=68) (actual time=12.123..13.864 rows=3,049 loops=1)

11. 5.842 12.927 ↓ 1.0 8,062 1

Sort (cost=1,224.63..1,244.44 rows=7,923 width=68) (actual time=12.122..12.927 rows=8,062 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: 1326kB
12. 5.560 7.085 ↓ 1.0 8,062 1

Hash Join (cost=171.10..711.54 rows=7,923 width=68) (actual time=0.595..7.085 rows=8,062 loops=1)

  • Hash Cond: (appointments.location_id = locations.id)
13. 0.963 0.963 ↓ 1.0 8,062 1

Seq Scan on appointments (cost=0.00..253.23 rows=7,923 width=40) (actual time=0.003..0.963 rows=8,062 loops=1)

14. 0.246 0.562 ↓ 1.0 1,261 1

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

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

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

16.          

CTE availabilities_cte

17. 0.809 0.809 ↑ 1.0 5,680 1

Seq Scan on availabilities (cost=0.00..229.41 rows=5,841 width=32) (actual time=0.005..0.809 rows=5,680 loops=1)

18.          

CTE latest_referral_activity_cte

19. 3.296 58.427 ↓ 1.0 3,551 1

Unique (cost=6,317.44..6,536.24 rows=3,543 width=56) (actual time=50.023..58.427 rows=3,551 loops=1)

20. 35.755 55.131 ↑ 1.0 43,592 1

Sort (cost=6,317.44..6,426.84 rows=43,761 width=56) (actual time=50.022..55.131 rows=43,592 loops=1)

  • Sort Key: referral_activities.referral_id, referral_activities.created_at DESC
  • Sort Method: external merge Disk: 2808kB
21. 13.308 19.376 ↑ 1.0 43,592 1

Hash Join (cost=433.72..2,944.04 rows=43,761 width=56) (actual time=1.395..19.376 rows=43,592 loops=1)

  • Hash Cond: (referral_activities.referral_id = referrals_2.id)
22. 4.777 4.777 ↑ 1.0 43,592 1

Seq Scan on referral_activities (cost=0.00..1,908.61 rows=43,761 width=40) (actual time=0.083..4.777 rows=43,592 loops=1)

23. 0.465 1.291 ↓ 1.0 3,551 1

Hash (cost=389.43..389.43 rows=3,543 width=16) (actual time=1.291..1.291 rows=3,551 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 199kB
24. 0.826 0.826 ↓ 1.0 3,551 1

Seq Scan on referrals referrals_2 (cost=0.00..389.43 rows=3,543 width=16) (actual time=0.074..0.826 rows=3,551 loops=1)

25. 10.777 301.761 ↓ 1.3 7,369 1

Sort (cost=2,505.95..2,520.55 rows=5,841 width=463) (actual time=300.731..301.761 rows=7,369 loops=1)

  • Sort Key: referrals.id, (concat(people.first_name, ' ', people.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.name
  • Sort Method: quicksort Memory: 3999kB
26. 6.351 290.984 ↓ 1.3 7,369 1

Hash Left Join (cost=1,895.21..2,140.53 rows=5,841 width=463) (actual time=279.620..290.984 rows=7,369 loops=1)

  • Hash Cond: (latest_referral_activity_cte.activity_id = activities.id)
27. 2.700 284.612 ↓ 1.3 7,369 1

Hash Right Join (cost=1,892.31..2,089.45 rows=5,841 width=437) (actual time=279.582..284.612 rows=7,369 loops=1)

  • Hash Cond: (availabilities_cte.referral_id = referrals.id)
28. 2.359 2.359 ↑ 1.0 5,680 1

CTE Scan on availabilities_cte (cost=0.00..116.82 rows=5,841 width=32) (actual time=0.006..2.359 rows=5,680 loops=1)

29. 2.080 279.553 ↓ 1.0 3,551 1

Hash (cost=1,848.03..1,848.03 rows=3,543 width=421) (actual time=279.553..279.553 rows=3,551 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 1495kB
30. 3.283 277.473 ↓ 1.0 3,551 1

Hash Join (cost=906.88..1,848.03 rows=3,543 width=421) (actual time=274.664..277.473 rows=3,551 loops=1)

  • Hash Cond: (people.id = referrals.person_id)
31. 1.973 1.973 ↑ 1.0 15,636 1

Seq Scan on people (cost=0.00..845.52 rows=16,052 width=36) (actual time=0.088..1.973 rows=15,636 loops=1)

32. 1.768 272.217 ↓ 1.0 3,551 1

Hash (cost=862.59..862.59 rows=3,543 width=417) (actual time=272.217..272.217 rows=3,551 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 1455kB
33. 1.570 270.449 ↓ 1.0 3,551 1

Hash Right Join (cost=743.02..862.59 rows=3,543 width=417) (actual time=259.689..270.449 rows=3,551 loops=1)

  • Hash Cond: (latest_referral_activity_cte.referral_id = referrals.id)
34. 59.242 59.242 ↓ 1.0 3,551 1

CTE Scan on latest_referral_activity_cte (cost=0.00..70.86 rows=3,543 width=32) (actual time=50.027..59.242 rows=3,551 loops=1)

35. 2.292 209.637 ↓ 1.0 3,551 1

Hash (cost=698.73..698.73 rows=3,543 width=401) (actual time=209.637..209.637 rows=3,551 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 1400kB
36. 1.663 207.345 ↓ 1.0 3,551 1

Hash Right Join (cost=579.15..698.73 rows=3,543 width=401) (actual time=173.692..207.345 rows=3,551 loops=1)

  • Hash Cond: (latest_logs_cte.referral_id = referrals.id)
37. 185.062 185.062 ↓ 1.0 3,551 1

CTE Scan on latest_logs_cte (cost=0.00..70.86 rows=3,543 width=88) (actual time=153.047..185.062 rows=3,551 loops=1)

38. 1.802 20.620 ↓ 1.0 3,551 1

Hash (cost=534.87..534.87 rows=3,543 width=329) (actual time=20.620..20.620 rows=3,551 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 1256kB
39. 1.240 18.818 ↓ 1.0 3,551 1

Hash Right Join (cost=433.72..534.87 rows=3,543 width=329) (actual time=15.035..18.818 rows=3,551 loops=1)

  • Hash Cond: (latest_appointments_cte.referral_id = referrals.id)
40. 14.689 14.689 ↓ 1.0 3,049 1

CTE Scan on latest_appointments_cte (cost=0.00..59.94 rows=2,997 width=56) (actual time=12.124..14.689 rows=3,049 loops=1)

41. 1.666 2.889 ↓ 1.0 3,551 1

Hash (cost=389.43..389.43 rows=3,543 width=289) (actual time=2.889..2.889 rows=3,551 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 1152kB
42. 1.223 1.223 ↓ 1.0 3,551 1

Seq Scan on referrals (cost=0.00..389.43 rows=3,543 width=289) (actual time=0.114..1.223 rows=3,551 loops=1)

43. 0.009 0.021 ↓ 1.1 46 1

Hash (cost=2.40..2.40 rows=40 width=39) (actual time=0.021..0.021 rows=46 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
44. 0.012 0.012 ↓ 1.1 46 1

Seq Scan on activities (cost=0.00..2.40 rows=40 width=39) (actual time=0.003..0.012 rows=46 loops=1)

Planning time : 1.379 ms
Execution time : 327.010 ms