explain.depesz.com

PostgreSQL's explain analyze made readable

Result: u1gY

Settings
# exclusive inclusive rows x rows loops node
1. 0.839 724,851.394 ↓ 9.0 9 1

GroupAggregate (cost=1,926,870.09..1,926,870.70 rows=1 width=176) (actual time=60,323.309..724,851.394 rows=9 loops=1)

  • Group Key: ap.first_name, ap.last_name, ap.middle_name, em.id, lo.id, ti.id
2. 0.187 60,321.479 ↓ 9.0 9 1

Sort (cost=1,926,870.09..1,926,870.10 rows=1 width=76) (actual time=60,321.470..60,321.479 rows=9 loops=1)

  • Sort Key: ap.first_name, ap.last_name, ap.middle_name, em.id, ti.id
  • Sort Method: quicksort Memory: 26kB
3. 7,060.679 60,321.292 ↓ 9.0 9 1

Hash Right Join (cost=274.28..1,926,870.08 rows=1 width=76) (actual time=59,719.911..60,321.292 rows=9 loops=1)

  • Hash Cond: (bt.timeblock_id = ti.id)
  • Join Filter: CASE WHEN ((date_part('timezone'::text, CURRENT_TIME) / '3600'::double precision) = '-4'::double precision) THEN CASE WHEN ((tz.timezone_offset = '-5'::double precision) AND (tz.dst = 'Y'::bpchar)) THEN ((((ti.start_date - '13 days'::interval) - '08:00:00'::interval) >= bt.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '08:00:00'::interval) <= COALESCE(bt.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) WHEN (((tz.timezone_offset = '-6'::double precision) AND (tz.dst = 'Y'::bpchar)) OR ((tz.timezone_offset = '-5'::double precision) AND (tz.dst = 'N'::bpchar))) THEN ((((ti.start_date - '13 days'::interval) - '07:00:00'::interval) >= bt.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '07:00:00'::interval) <= COALESCE(bt.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) WHEN (((tz.timezone_offset = '-7'::double precision) AND (tz.dst = 'Y'::bpchar)) OR ((tz.timezone_offset = '-6'::double precision) AND (tz.dst = 'N'::bpchar))) THEN ((((ti.start_date - '13 days'::interval) - '06:00:00'::interval) >= bt.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '06:00:00'::interval) <= COALESCE(bt.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) WHEN (((tz.timezone_offset = '-8'::double precision) AND (tz.dst = 'Y'::bpchar)) OR ((tz.timezone_offset = '-7'::double precision) AND (tz.dst = 'N'::bpchar))) THEN ((((ti.start_date - '13 days'::interval) - '05:00:00'::interval) >= bt.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '05:00:00'::interval) <= COALESCE(bt.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) ELSE NULL::boolean END WHEN ((date_part('timezone'::text, CURRENT_TIME) / '3600'::double precision) = '-6'::double precision) THEN CASE WHEN (tz.timezone_offset = '-5'::double precision) THEN ((((ti.start_date - '13 days'::interval) - '08:00:00'::interval) >= bt.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '08:00:00'::interval) <= COALESCE(bt.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) WHEN (tz.timezone_offset = '-6'::double precision) THEN ((((ti.start_date - '13 days'::interval) - '07:00:00'::interval) >= bt.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '07:00:00'::interval) <= COALESCE(bt.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) WHEN (tz.timezone_offset = '-7'::double precision) THEN ((((ti.start_date - '13 days'::interval) - '06:00:00'::interval) >= bt.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '06:00:00'::interval) <= COALESCE(bt.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) WHEN (tz.timezone_offset = '-8'::double precision) THEN ((((ti.start_date - '13 days'::interval) - '05:00:00'::interval) >= bt.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '05:00:00'::interval) <= COALESCE(bt.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) ELSE NULL::boolean END ELSE NULL::boolean END
  • Rows Removed by Join Filter: 3
4. 52,939.178 52,939.178 ↑ 1.0 47,752,314 1

Seq Scan on break_time bt (cost=0.00..1,746,893.50 rows=47,920,562 width=28) (actual time=11.632..52,939.178 rows=47,752,314 loops=1)

  • Filter: (paid = 'N'::bpchar)
  • Rows Removed by Filter: 13984690
5. 0.058 321.435 ↓ 9.0 9 1

Hash (cost=274.27..274.27 rows=1 width=82) (actual time=321.435..321.435 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
6. 0.292 321.377 ↓ 9.0 9 1

Nested Loop Left Join (cost=235.54..274.27 rows=1 width=82) (actual time=121.039..321.377 rows=9 loops=1)

  • Filter: CASE WHEN ((date_part('timezone'::text, CURRENT_TIME) / '3600'::double precision) = '-4'::double precision) THEN CASE WHEN ((tz.timezone_offset = '-5'::double precision) AND (tz.dst = 'Y'::bpchar)) THEN ((((ti.start_date - '13 days'::interval) - '08:00:00'::interval) >= ti.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '08:00:00'::interval) <= COALESCE(ti.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) WHEN (((tz.timezone_offset = '-6'::double precision) AND (tz.dst = 'Y'::bpchar)) OR ((tz.timezone_offset = '-5'::double precision) AND (tz.dst = 'N'::bpchar))) THEN ((((ti.start_date - '13 days'::interval) - '07:00:00'::interval) >= ti.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '07:00:00'::interval) <= COALESCE(ti.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) WHEN (((tz.timezone_offset = '-7'::double precision) AND (tz.dst = 'Y'::bpchar)) OR ((tz.timezone_offset = '-6'::double precision) AND (tz.dst = 'N'::bpchar))) THEN ((((ti.start_date - '13 days'::interval) - '06:00:00'::interval) >= ti.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '06:00:00'::interval) <= COALESCE(ti.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) WHEN (((tz.timezone_offset = '-8'::double precision) AND (tz.dst = 'Y'::bpchar)) OR ((tz.timezone_offset = '-7'::double precision) AND (tz.dst = 'N'::bpchar))) THEN ((((ti.start_date - '13 days'::interval) - '05:00:00'::interval) >= ti.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '05:00:00'::interval) <= COALESCE(ti.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) ELSE NULL::boolean END WHEN ((date_part('timezone'::text, CURRENT_TIME) / '3600'::double precision) = '-6'::double precision) THEN CASE WHEN (tz.timezone_offset = '-5'::double precision) THEN ((((ti.start_date - '13 days'::interval) - '08:00:00'::interval) >= ti.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '08:00:00'::interval) <= COALESCE(ti.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) WHEN (tz.timezone_offset = '-6'::double precision) THEN ((((ti.start_date - '13 days'::interval) - '07:00:00'::interval) >= ti.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '07:00:00'::interval) <= COALESCE(ti.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) WHEN (tz.timezone_offset = '-7'::double precision) THEN ((((ti.start_date - '13 days'::interval) - '06:00:00'::interval) >= ti.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '06:00:00'::interval) <= COALESCE(ti.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) WHEN (tz.timezone_offset = '-8'::double precision) THEN ((((ti.start_date - '13 days'::interval) - '05:00:00'::interval) >= ti.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '05:00:00'::interval) <= COALESCE(ti.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) ELSE NULL::boolean END ELSE NULL::boolean END
  • Rows Removed by Filter: 18
7. 0.100 320.734 ↓ 27.0 27 1

Nested Loop (cost=235.12..271.63 rows=1 width=93) (actual time=120.903..320.734 rows=27 loops=1)

8. 0.078 317.718 ↓ 27.0 27 1

Nested Loop (cost=219.93..255.41 rows=1 width=109) (actual time=120.659..317.718 rows=27 loops=1)

9. 0.038 317.424 ↓ 27.0 27 1

Nested Loop (cost=219.50..252.97 rows=1 width=86) (actual time=120.606..317.424 rows=27 loops=1)

10. 0.041 317.170 ↓ 27.0 27 1

Nested Loop (cost=219.08..250.53 rows=1 width=94) (actual time=120.558..317.170 rows=27 loops=1)

11. 0.039 316.859 ↓ 27.0 27 1

Nested Loop (cost=218.66..247.90 rows=1 width=94) (actual time=120.495..316.859 rows=27 loops=1)

12. 0.181 316.631 ↓ 27.0 27 1

Nested Loop (cost=218.37..245.59 rows=1 width=102) (actual time=120.465..316.631 rows=27 loops=1)

  • Join Filter: (sc.department_id = dep.id)
13. 0.147 316.045 ↓ 27.0 27 1

Nested Loop (cost=218.08..231.93 rows=1 width=118) (actual time=120.282..316.045 rows=27 loops=1)

14. 0.067 299.104 ↓ 27.0 27 1

Nested Loop (cost=214.05..226.87 rows=1 width=118) (actual time=109.802..299.104 rows=27 loops=1)

15. 0.113 298.821 ↓ 27.0 27 1

Nested Loop (cost=213.76..224.56 rows=1 width=97) (actual time=109.760..298.821 rows=27 loops=1)

16. 0.118 298.303 ↓ 27.0 27 1

Nested Loop (cost=213.33..222.11 rows=1 width=97) (actual time=109.685..298.303 rows=27 loops=1)

17. 0.235 0.235 ↓ 5.0 5 1

Index Scan using idx_employee_id_del on employee em (cost=0.42..8.17 rows=1 width=19) (actual time=0.074..0.235 rows=5 loops=1)

  • Index Cond: (id = ANY ('{94948427,19041173,93071175,95083987,94264083}'::bigint[]))
18. 8.635 297.950 ↓ 5.0 5 5

Bitmap Heap Scan on timeblock ti (cost=212.91..213.93 rows=1 width=86) (actual time=59.545..59.590 rows=5 loops=5)

  • Recheck Cond: ((employee_id = em.id) AND (location_id = 114106))
  • Filter: ((start_date >= '2020-02-01'::date) AND (start_date <= '2020-02-05'::date))
  • Rows Removed by Filter: 262
  • Heap Blocks: exact=1026
19. 21.590 289.315 ↓ 0.0 0 5

BitmapAnd (cost=212.91..212.91 rows=1 width=0) (actual time=57.863..57.863 rows=0 loops=5)

20. 0.425 0.425 ↑ 2.1 276 5

Bitmap Index Scan on i_timeblock_emp (cost=0.00..6.93 rows=582 width=0) (actual time=0.085..0.085 rows=276 loops=5)

  • Index Cond: (employee_id = em.id)
21. 267.300 267.300 ↓ 7.4 148,223 5

Bitmap Index Scan on i_timeblock_loc (cost=0.00..205.72 rows=20,020 width=0) (actual time=53.460..53.460 rows=148,223 loops=5)

  • Index Cond: (location_id = 114106)
22. 0.405 0.405 ↑ 1.0 1 27

Index Scan using schedule_pkey on schedule sc (cost=0.43..2.45 rows=1 width=16) (actual time=0.015..0.015 rows=1 loops=27)

  • Index Cond: (id = ti.schedule_id)
23. 0.216 0.216 ↑ 1.0 1 27

Index Scan using idx_location_id_del on location lo (cost=0.29..2.31 rows=1 width=21) (actual time=0.008..0.008 rows=1 loops=27)

  • Index Cond: (id = 114106)
24. 0.405 16.794 ↑ 1.0 1 27

Bitmap Heap Scan on department_location de_lo (cost=4.03..5.05 rows=1 width=16) (actual time=0.620..0.622 rows=1 loops=27)

  • Recheck Cond: ((department_id = sc.department_id) AND (location_id = 114106))
  • Filter: (deleted = 'N'::bpchar)
  • Rows Removed by Filter: 4
  • Heap Blocks: exact=108
25. 0.918 16.389 ↓ 0.0 0 27

BitmapAnd (cost=4.03..4.03 rows=1 width=0) (actual time=0.607..0.607 rows=0 loops=27)

26. 15.012 15.012 ↓ 33.4 1,701 27

Bitmap Index Scan on department_location_dep_idx (cost=0.00..1.80 rows=51 width=0) (actual time=0.556..0.556 rows=1,701 loops=27)

  • Index Cond: (department_id = sc.department_id)
27. 0.459 0.459 ↓ 1.9 141 27

Bitmap Index Scan on department_location_loc_idx (cost=0.00..1.97 rows=73 width=0) (actual time=0.017..0.017 rows=141 loops=27)

  • Index Cond: (location_id = 114106)
28. 0.405 0.405 ↑ 1.0 1 27

Index Only Scan using i_department_id on department dep (cost=0.29..13.64 rows=1 width=4) (actual time=0.015..0.015 rows=1 loops=27)

  • Index Cond: ((id = de_lo.department_id) AND (id = ANY ('{18002,17998,17996,17997,16498,17994,17999,16500,17993,17995}'::integer[])))
  • Heap Fetches: 27
29. 0.189 0.189 ↑ 1.0 1 27

Index Scan using corporation_pkey on corporation co (cost=0.29..2.31 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=27)

  • Index Cond: (id = lo.corporation_id)
  • Filter: (deleted = 'N'::bpchar)
30. 0.270 0.270 ↑ 1.0 1 27

Index Scan using i_position_id on "position" po (cost=0.42..2.44 rows=1 width=24) (actual time=0.010..0.010 rows=1 loops=27)

  • Index Cond: (id = ti.position_id)
  • Filter: (location_id = 114106)
31. 0.216 0.216 ↑ 1.0 1 27

Index Scan using position_code_pkey on position_code po_code (cost=0.42..2.44 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=27)

  • Index Cond: (id = po.position_code_id)
  • Filter: (deleted = 'N'::bpchar)
32. 0.216 0.216 ↑ 1.0 1 27

Index Scan using i_au_id_del on app_user ap (cost=0.42..2.44 rows=1 width=23) (actual time=0.008..0.008 rows=1 loops=27)

  • Index Cond: (id = em.user_id)
33. 0.162 2.916 ↑ 1.0 1 27

Bitmap Heap Scan on user_location ul (cost=15.19..16.21 rows=1 width=16) (actual time=0.108..0.108 rows=1 loops=27)

  • Recheck Cond: ((location_id = 114106) AND (deleted = 'N'::bpchar) AND (user_id = em.user_id))
  • Filter: ((termination_date IS NULL) OR (termination_date >= '2020-02-05 00:00:00'::timestamp without time zone))
  • Heap Blocks: exact=27
34. 0.513 2.754 ↓ 0.0 0 27

BitmapAnd (cost=15.19..15.19 rows=1 width=0) (actual time=0.102..0.102 rows=0 loops=27)

35. 2.079 2.079 ↓ 9.6 555 27

Bitmap Index Scan on user_loc_loc_id (cost=0.00..1.86 rows=58 width=0) (actual time=0.077..0.077 rows=555 loops=27)

  • Index Cond: (location_id = 114106)
36. 0.162 0.162 ↑ 885.0 1 27

Bitmap Index Scan on user_loc_user_id_all_index (cost=0.00..13.07 rows=885 width=0) (actual time=0.006..0.006 rows=1 loops=27)

  • Index Cond: (user_id = em.user_id)
37. 0.351 0.351 ↑ 1.0 1 27

Index Scan using timezone_postal_idx on timezone tz (cost=0.43..2.45 rows=1 width=17) (actual time=0.013..0.013 rows=1 loops=27)

  • Index Cond: ((postal_code)::text = split_part((lo.zip)::text, '-'::text, 1))
38.          

SubPlan (for GroupAggregate)

39. 664,529.076 664,529.076 ↑ 1.0 1 9

Function Scan on getactivetimeblockid (cost=0.25..0.26 rows=1 width=8) (actual time=73,836.564..73,836.564 rows=1 loops=9)

Planning time : 80.301 ms
Execution time : 724,856.337 ms