explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3SOI

Settings
# exclusive inclusive rows x rows loops node
1. 0.097 19,073.122 ↓ 551.0 551 1

Limit (cost=8,104.58..8,104.72 rows=1 width=1,620) (actual time=18,100.125..19,073.122 rows=551 loops=1)

2. 966.045 19,073.025 ↓ 551.0 551 1

Unique (cost=8,104.58..8,104.72 rows=1 width=1,620) (actual time=18,100.123..19,073.025 rows=551 loops=1)

3. 1,345.634 18,106.980 ↓ 35,009.0 35,009 1

Sort (cost=8,104.58..8,104.58 rows=1 width=1,620) (actual time=18,100.121..18,106.980 rows=35,009 loops=1)

  • Sort Key: e.id, e.min_hours, e.max_hours, e.min_pay, e.max_pay, e.datecreated, e.user_id, e.notice_timeframe, e.parent_id, e.starttimestamp, e.endtimestamp, e.hire_date, e.max_shifts, e.min_shifts, e.active_attendance_block, e.termination_date, e.internal_number, e.current_location, e.max_shifts_per_week, e.min_shifts_per_week, e.birth_date, e.max_hours_per_day, e.min_hours_per_day, e.overtime, e.ssn, e.ssn_suffix, e.payroll_id, e.home_location_id, e.home_department_id, e.internal_password, e.small_pic_id, e.medium_pic_id, e.large_pic_id, e.corporate_user, e.finger1, e.finger2, e.finger3, e.finger4, e.zkpin, e.copy_schedule, e.admin_user, e.holidaycalendar_id, e.default_position_code_id, e.applicant_id, e.designation, e.max_hours_per_month, e.min_hours_per_month, e.max_shifts_per_month, e.min_shifts_per_month, e.minor_id, e.pto_track_id, e.replicate_locations, e.af_employeeid, e.integrate_agile
  • Sort Method: quicksort Memory: 48804kB
4. 1,770.464 16,761.346 ↓ 35,009.0 35,009 1

GroupAggregate (cost=8,104.54..8,104.57 rows=1 width=1,620) (actual time=12,343.498..16,761.346 rows=35,009 loops=1)

  • Group Key: e.id, ab.allocated_date
  • Filter: (max(ab1.allocated_date) <> '2018-12-25'::date)
5. 11,592.458 14,990.882 ↓ 2,734,809.0 2,734,809 1

Sort (cost=8,104.54..8,104.55 rows=1 width=1,624) (actual time=12,343.387..14,990.882 rows=2,734,809 loops=1)

  • Sort Key: e.id, ab.allocated_date
  • Sort Method: external merge Disk: 2428128kB
6. 1,041.060 3,398.424 ↓ 2,734,809.0 2,734,809 1

Nested Loop (cost=2.70..8,104.53 rows=1 width=1,624) (actual time=0.500..3,398.424 rows=2,734,809 loops=1)

  • Join Filter: (ul.location_id = ab1.location_id)
7. 20.596 103.353 ↓ 36,951.0 36,951 1

Nested Loop (cost=2.13..8,094.14 rows=1 width=1,652) (actual time=0.474..103.353 rows=36,951 loops=1)

  • Join Filter: (ul.location_id = ab.location_id)
8. 1.673 38.937 ↓ 626.0 626 1

Nested Loop (cost=1.57..8,083.75 rows=1 width=1,632) (actual time=0.378..38.937 rows=626 loops=1)

  • Join Filter: (u.id = e.user_id)
9. 1.764 25.000 ↓ 876.0 876 1

Nested Loop (cost=1.15..8,066.85 rows=1 width=32) (actual time=0.131..25.000 rows=876 loops=1)

10. 0.592 10.972 ↓ 10.7 876 1

Nested Loop (cost=0.71..7,865.75 rows=82 width=24) (actual time=0.096..10.972 rows=876 loops=1)

11. 0.150 0.150 ↑ 4.3 10 1

Index Scan using i_loc_corp_id on location l (cost=0.29..33.67 rows=43 width=8) (actual time=0.057..0.150 rows=10 loops=1)

  • Index Cond: (corporation_id = 53921)
12. 10.230 10.230 ↑ 1.4 88 10

Index Scan using user_loc_loc_id on user_location ul (cost=0.42..180.94 rows=120 width=16) (actual time=0.027..1.023 rows=88 loops=10)

  • Index Cond: (location_id = l.id)
  • Filter: ((termination_date IS NULL) OR (termination_date > '2018-12-25 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 77
13. 12.264 12.264 ↑ 1.0 1 876

Index Scan using app_user_pkey on app_user u (cost=0.43..2.45 rows=1 width=8) (actual time=0.014..0.014 rows=1 loops=876)

  • Index Cond: (id = ul.user_id)
  • Filter: (corporation_id = 53921)
14. 12.264 12.264 ↑ 1.0 1 876

Index Scan using i_emp_user_id on employee e (cost=0.42..16.88 rows=1 width=1,616) (actual time=0.013..0.014 rows=1 loops=876)

  • Index Cond: (user_id = ul.user_id)
  • Filter: ((default_position_code_id IS NOT NULL) AND (ul.location_id = home_location_id))
  • Rows Removed by Filter: 0
15. 43.820 43.820 ↓ 59.0 59 626

Index Only Scan using i_attblock_combo on attendance_block ab (cost=0.56..10.38 rows=1 width=20) (actual time=0.036..0.070 rows=59 loops=626)

  • Index Cond: ((employee_id = e.id) AND (location_id = e.home_location_id) AND (allocated_date >= '2018-08-27'::date) AND (allocated_date <= '2018-12-25'::date))
  • Heap Fetches: 36951
16. 2,254.011 2,254.011 ↓ 74.0 74 36,951

Index Only Scan using i_attblock_combo on attendance_block ab1 (cost=0.56..10.38 rows=1 width=20) (actual time=0.021..0.061 rows=74 loops=36,951)

  • Index Cond: ((employee_id = ab.employee_id) AND (location_id = ab.location_id) AND (allocated_date >= '2018-08-27'::date) AND (allocated_date <= '2018-12-27'::date))
  • Heap Fetches: 2734809