explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Y1IX

Settings
# exclusive inclusive rows x rows loops node
1. 79.027 51,189.232 ↓ 1.7 10,767 1

Hash Join (cost=2,950.39..1,016,400.21 rows=6,407 width=141) (actual time=31.703..51,189.232 rows=10,767 loops=1)

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

Initplan (for Hash Join)

3. 0.056 0.056 ↑ 1.0 1 1

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

  • Filter: ((name)::text = 'productivity.goal'::text)
  • Rows Removed by Filter: 265
4. 7.503 96.086 ↓ 1.7 10,767 1

Hash Join (cost=2,942.86..4,099.71 rows=6,407 width=100) (actual time=31.428..96.086 rows=10,767 loops=1)

  • Hash Cond: (d.org_region_id = r.org_region_id)
5. 8.243 88.555 ↓ 1.7 10,803 1

Hash Left Join (cost=2,941.09..4,079.55 rows=6,407 width=84) (actual time=31.392..88.555 rows=10,803 loops=1)

  • Hash Cond: (u.effective_supervisor_group_id = g.supervisor_group_id)
6. 8.435 79.490 ↓ 1.7 10,803 1

Hash Join (cost=2,901.99..4,023.57 rows=6,407 width=73) (actual time=30.559..79.490 rows=10,803 loops=1)

  • Hash Cond: (u.effective_district_id = d.district_id)
7. 31.232 70.913 ↓ 1.0 11,209 1

Hash Join (cost=2,895.99..3,988.28 rows=10,870 width=37) (actual time=30.408..70.913 rows=11,209 loops=1)

  • Hash Cond: (b.user_id = u.usr_id)
8. 9.305 9.305 ↓ 1.0 49,845 1

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

9. 5.231 30.376 ↓ 1.0 11,863 1

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

  • Buckets: 16,384 Batches: 1 Memory Usage: 904kB
10. 25.145 25.145 ↓ 1.0 11,863 1

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

  • Filter: activeuser
  • Rows Removed by Filter: 42,163
11. 0.051 0.142 ↓ 1.3 116 1

Hash (cost=4.89..4.89 rows=89 width=44) (actual time=0.142..0.142 rows=116 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
12. 0.091 0.091 ↓ 1.3 116 1

Seq Scan on district d (cost=0.00..4.89 rows=89 width=44) (actual time=0.012..0.091 rows=116 loops=1)

  • Filter: ((clock_start IS NOT NULL) AND (clock_start < '2020-06-08'::date))
  • Rows Removed by Filter: 35
13. 0.378 0.822 ↓ 1.0 1,250 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 70kB
14. 0.444 0.444 ↓ 1.0 1,250 1

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

15. 0.014 0.028 ↑ 1.0 34 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
16. 0.014 0.014 ↑ 1.0 34 1

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

17. 0.006 0.017 ↑ 1.0 9 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
18. 0.011 0.011 ↑ 1.0 9 1

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

19.          

SubPlan (for Hash Join)

20. 10.767 387.612 ↑ 1.0 1 10,767

Limit (cost=0.43..8.45 rows=1 width=8) (actual time=0.036..0.036 rows=1 loops=10,767)

21. 376.845 376.845 ↑ 1.0 1 10,767

Index Scan using operational_user_day on operational_user_goal (cost=0.43..8.45 rows=1 width=8) (actual time=0.035..0.035 rows=1 loops=10,767)

  • Index Cond: ((user_id = u.usr_id) AND (work_day = '2020-06-08'::date))
22. 10.767 48,677.607 ↑ 1.0 1 10,767

Result (cost=45.75..45.76 rows=1 width=8) (actual time=4.521..4.521 rows=1 loops=10,767)

23.          

Initplan (for Result)

24. 10.767 48,666.840 ↑ 1.0 1 10,767

Limit (cost=0.57..45.75 rows=1 width=8) (actual time=4.520..4.520 rows=1 loops=10,767)

25. 48,656.073 48,656.073 ↑ 13.0 1 10,767

Index Only Scan Backward using sync_record_by_user_time_date on sync_record (cost=0.57..587.95 rows=13 width=8) (actual time=4.519..4.519 rows=1 loops=10,767)

  • Index Cond: ((user_id = u.usr_id) AND (synctime IS NOT NULL) AND ((date(synctime)) = '2020-06-08'::date))
  • Heap Fetches: 7,607
26. 53.835 1,787.322 ↑ 1.0 1 10,767

Aggregate (cost=21.83..21.84 rows=1 width=8) (actual time=0.166..0.166 rows=1 loops=10,767)

27. 14.461 1,733.487 ↑ 1.0 1 10,767

Nested Loop (cost=1.00..21.81 rows=1 width=15) (actual time=0.160..0.161 rows=1 loops=10,767)

28. 1,668.885 1,668.885 ↑ 1.0 1 10,767

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

  • Index Cond: ((work_user_id = u.usr_id) AND (work_date = '2020-06-08'::date))
  • Filter: ((status)::text = ANY ('{entered,submitted}'::text[]))
  • Rows Removed by Filter: 0
29. 50.141 50.141 ↑ 2.5 2 7,163

Index Scan using time_clock_by_time_day on time_clock c (cost=0.44..13.18 rows=5 width=8) (actual time=0.006..0.007 rows=2 loops=7,163)

  • Index Cond: (time_day_id = d_1.time_day_id)
30. 9.501 96.903 ↑ 1.0 1 10,767

Aggregate (cost=40.96..40.97 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=10,767)

31. 17.975 64.602 ↑ 1.0 1 10,767

Nested Loop (cost=1.00..21.81 rows=1 width=24) (actual time=0.005..0.006 rows=1 loops=10,767)

32. 32.301 32.301 ↑ 1.0 1 10,767

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

  • Index Cond: ((work_user_id = u.usr_id) AND (work_date = '2020-06-08'::date))
  • Filter: ((status)::text = ANY ('{entered,submitted}'::text[]))
  • Rows Removed by Filter: 0
33. 14.326 14.326 ↑ 2.5 2 7,163

Index Scan using time_clock_by_time_day on time_clock c_1 (cost=0.44..13.18 rows=5 width=13) (actual time=0.002..0.002 rows=2 loops=7,163)

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

SubPlan (for Aggregate)

35. 3.036 3.036 ↑ 72.0 1 132

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 time=0.023..0.023 rows=1 loops=132)

  • 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)))))
  • Heap Fetches: 108
36. 2.196 19.764 ↑ 1.0 1 122

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

37. 17.568 17.568 ↑ 6.5 11 122

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 time=0.003..0.144 rows=11 loops=122)

  • 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)))))
  • Heap Fetches: 1,153
38. 7.659 64.602 ↑ 1.0 1 10,767

Aggregate (cost=40.96..40.97 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=10,767)

39. 17.975 53.835 ↑ 1.0 1 10,767

Nested Loop (cost=1.00..21.81 rows=1 width=24) (actual time=0.004..0.005 rows=1 loops=10,767)

40. 21.534 21.534 ↑ 1.0 1 10,767

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

  • Index Cond: ((work_user_id = u.usr_id) AND (work_date = '2020-06-08'::date))
  • Filter: ((status)::text = ANY ('{entered,submitted}'::text[]))
  • Rows Removed by Filter: 0
41. 14.326 14.326 ↑ 5.0 1 7,163

Index Scan using time_clock_by_time_day on time_clock c_2 (cost=0.44..13.18 rows=5 width=13) (actual time=0.002..0.002 rows=1 loops=7,163)

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

SubPlan (for Aggregate)

43. 0.348 0.348 ↑ 72.0 1 116

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 time=0.003..0.003 rows=1 loops=116)

  • 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)))))
  • Heap Fetches: 102
44. 1.955 2.760 ↑ 1.0 1 115

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

45. 0.805 0.805 ↑ 6.5 11 115

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 time=0.003..0.007 rows=11 loops=115)

  • 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)))))
  • Heap Fetches: 1,129
Planning time : 4.093 ms
Execution time : 51,191.300 ms