explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cAUh

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Hash Join (cost=2,950.39..1,016,400.21 rows=6,407 width=141) (actual rows= loops=)

  • Hash Cond: (r.org_super_region_id = s.org_super_region_id)
2.          

Initplan (for Hash Join)

3. 0.000 0.000 ↓ 0.0

Seq Scan on config (cost=0.00..6.33 rows=1 width=32) (actual rows= loops=)

  • Filter: ((name)::text = 'productivity.goal'::text)
4. 0.000 0.000 ↓ 0.0

Hash Join (cost=2,942.86..4,099.71 rows=6,407 width=100) (actual rows= loops=)

  • Hash Cond: (d.org_region_id = r.org_region_id)
5. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=2,941.09..4,079.55 rows=6,407 width=84) (actual rows= loops=)

  • Hash Cond: (u.effective_supervisor_group_id = g.supervisor_group_id)
6. 0.000 0.000 ↓ 0.0

Hash Join (cost=2,901.99..4,023.57 rows=6,407 width=73) (actual rows= loops=)

  • Hash Cond: (u.effective_district_id = d.district_id)
7. 0.000 0.000 ↓ 0.0

Hash Join (cost=2,895.99..3,988.28 rows=10,870 width=37) (actual rows= loops=)

  • Hash Cond: (b.user_id = u.usr_id)
8. 0.000 0.000 ↓ 0.0

Seq Scan on bucket b (cost=0.00..961.86 rows=49,686 width=8) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Hash (cost=2,748.25..2,748.25 rows=11,819 width=33) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Seq Scan on usr u (cost=0.00..2,748.25 rows=11,819 width=33) (actual rows= loops=)

  • Filter: activeuser
11. 0.000 0.000 ↓ 0.0

Hash (cost=4.89..4.89 rows=89 width=44) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Seq Scan on district d (cost=0.00..4.89 rows=89 width=44) (actual rows= loops=)

  • Filter: ((clock_start IS NOT NULL) AND (clock_start < '2020-06-08'::date))
13. 0.000 0.000 ↓ 0.0

Hash (cost=23.49..23.49 rows=1,249 width=19) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Seq Scan on supervisor_group g (cost=0.00..23.49 rows=1,249 width=19) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Hash (cost=1.34..1.34 rows=34 width=24) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Seq Scan on org_region r (cost=0.00..1.34 rows=34 width=24) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Hash (cost=1.09..1.09 rows=9 width=25) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Seq Scan on org_super_region s (cost=0.00..1.09 rows=9 width=25) (actual rows= loops=)

19.          

SubPlan (for Hash Join)

20. 0.000 0.000 ↓ 0.0

Limit (cost=0.43..8.45 rows=1 width=8) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Index Scan using operational_user_day on operational_user_goal (cost=0.43..8.45 rows=1 width=8) (actual rows= loops=)

  • Index Cond: ((user_id = u.usr_id) AND (work_day = '2020-06-08'::date))
22. 0.000 0.000 ↓ 0.0

Result (cost=45.75..45.76 rows=1 width=8) (actual rows= loops=)

23.          

Initplan (for Result)

24. 0.000 0.000 ↓ 0.0

Limit (cost=0.57..45.75 rows=1 width=8) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Index Only Scan Backward using sync_record_by_user_time_date on sync_record (cost=0.57..587.95 rows=13 width=8) (actual rows= loops=)

  • Index Cond: ((user_id = u.usr_id) AND (synctime IS NOT NULL) AND ((date(synctime)) = '2020-06-08'::date))
26. 0.000 0.000 ↓ 0.0

Aggregate (cost=21.83..21.84 rows=1 width=8) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.00..21.81 rows=1 width=15) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Index Scan using time_day_by_user_date_status on time_day d_1 (cost=0.56..8.58 rows=1 width=15) (actual rows= loops=)

  • Index Cond: ((work_user_id = u.usr_id) AND (work_date = '2020-06-08'::date))
  • Filter: ((status)::text = ANY ('{entered,submitted}'::text[]))
29. 0.000 0.000 ↓ 0.0

Index Scan using time_clock_by_time_day on time_clock c (cost=0.44..13.18 rows=5 width=8) (actual rows= loops=)

  • Index Cond: (time_day_id = d_1.time_day_id)
30. 0.000 0.000 ↓ 0.0

Aggregate (cost=40.96..40.97 rows=1 width=8) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.00..21.81 rows=1 width=24) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Index Scan using time_day_by_user_date_status on time_day d_2 (cost=0.56..8.58 rows=1 width=19) (actual rows= loops=)

  • Index Cond: ((work_user_id = u.usr_id) AND (work_date = '2020-06-08'::date))
  • Filter: ((status)::text = ANY ('{entered,submitted}'::text[]))
33. 0.000 0.000 ↓ 0.0

Index Scan using time_clock_by_time_day on time_clock c_1 (cost=0.44..13.18 rows=5 width=13) (actual rows= loops=)

  • Index Cond: (time_day_id = d_2.time_day_id)
34.          

SubPlan (for Aggregate)

35. 0.000 0.000 ↓ 0.0

Index Only Scan using sync_record_by_user_time_date on sync_record sync_record_1 (cost=0.60..16.07 rows=72 width=0) (actual rows= loops=)

  • Index Cond: ((user_id = d_2.work_user_id) AND (synctime >= timezone('CST6CDT'::text, timezone((d_2.time_zone)::text, (d_2.work_date + (((c_1.start_sec)::text || ' seconds'::text))::interval)))) AND (synctime < timezone('CST6CDT'::text, timezone((d_2.time_zone)::text, (d_2.work_date + ('86400 seconds'::cstring)::interval)))))
36. 0.000 0.000 ↓ 0.0

Aggregate (cost=18.05..18.06 rows=1 width=8) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Index Only Scan using sync_record_by_user_time_date on sync_record sync_record_2 (cost=0.60..16.07 rows=72 width=8) (actual rows= loops=)

  • Index Cond: ((user_id = d_2.work_user_id) AND (synctime >= timezone('CST6CDT'::text, timezone((d_2.time_zone)::text, (d_2.work_date + (((c_1.start_sec)::text || ' seconds'::text))::interval)))) AND (synctime < timezone('CST6CDT'::text, timezone((d_2.time_zone)::text, (d_2.work_date + ('86400 seconds'::cstring)::interval)))))
38. 0.000 0.000 ↓ 0.0

Aggregate (cost=40.96..40.97 rows=1 width=8) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.00..21.81 rows=1 width=24) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Index Scan using time_day_by_user_date_status on time_day d_3 (cost=0.56..8.58 rows=1 width=19) (actual rows= loops=)

  • Index Cond: ((work_user_id = u.usr_id) AND (work_date = '2020-06-08'::date))
  • Filter: ((status)::text = ANY ('{entered,submitted}'::text[]))
41. 0.000 0.000 ↓ 0.0

Index Scan using time_clock_by_time_day on time_clock c_2 (cost=0.44..13.18 rows=5 width=13) (actual rows= loops=)

  • Index Cond: (time_day_id = d_3.time_day_id)
  • Filter: ((training IS NULL) OR (NOT training))
42.          

SubPlan (for Aggregate)

43. 0.000 0.000 ↓ 0.0

Index Only Scan using sync_record_by_user_time_date on sync_record sync_record_3 (cost=0.60..16.07 rows=72 width=0) (actual rows= loops=)

  • Index Cond: ((user_id = d_3.work_user_id) AND (synctime >= timezone('CST6CDT'::text, timezone((d_3.time_zone)::text, (d_3.work_date + (((c_2.start_sec)::text || ' seconds'::text))::interval)))) AND (synctime < timezone('CST6CDT'::text, timezone((d_3.time_zone)::text, (d_3.work_date + ('86400 seconds'::cstring)::interval)))))
44. 0.000 0.000 ↓ 0.0

Aggregate (cost=18.05..18.06 rows=1 width=8) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Index Only Scan using sync_record_by_user_time_date on sync_record sync_record_4 (cost=0.60..16.07 rows=72 width=8) (actual rows= loops=)

  • Index Cond: ((user_id = d_3.work_user_id) AND (synctime >= timezone('CST6CDT'::text, timezone((d_3.time_zone)::text, (d_3.work_date + (((c_2.start_sec)::text || ' seconds'::text))::interval)))) AND (synctime < timezone('CST6CDT'::text, timezone((d_3.time_zone)::text, (d_3.work_date + ('86400 seconds'::cstring)::interval)))))