explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 85DU

Settings
# exclusive inclusive rows x rows loops node
1. 1.747 863.963 ↑ 5.9 167 1

Unique (cost=966,934.29..966,953.89 rows=980 width=52) (actual time=862.135..863.963 rows=167 loops=1)

2. 20.153 862.216 ↓ 1.4 1,342 1

Sort (cost=966,934.29..966,936.74 rows=980 width=52) (actual time=862.135..862.216 rows=1,342 loops=1)

  • Sort Key: o.org_name, ws.system_desc, r.request_id, ((SubPlan 1)), ((SubPlan 2)), ((SubPlan 3)), ((SubPlan 4))
  • Sort Method: quicksort Memory: 1712kB
3. 12.018 842.063 ↓ 1.4 1,342 1

Nested Loop Left Join (cost=5.71..966,885.60 rows=980 width=52) (actual time=0.099..842.063 rows=1,342 loops=1)

4. 0.214 1.703 ↓ 2.1 167 1

Nested Loop Left Join (cost=5.28..458.32 rows=80 width=52) (actual time=0.070..1.703 rows=167 loops=1)

5. 0.096 1.155 ↓ 2.1 167 1

Nested Loop (cost=5.00..433.66 rows=80 width=30) (actual time=0.059..1.155 rows=167 loops=1)

6. 0.005 0.087 ↓ 2.0 6 1

Nested Loop (cost=4.58..23.67 rows=3 width=26) (actual time=0.047..0.087 rows=6 loops=1)

7. 0.020 0.020 ↑ 1.0 1 1

Index Scan using organisation_org_code_idx on organisation o (cost=0.28..8.29 rows=1 width=26) (actual time=0.020..0.020 rows=1 loops=1)

  • Index Cond: (org_code = 1618)
8. 0.044 0.062 ↓ 2.0 6 1

Bitmap Heap Scan on usr rqu (cost=4.31..15.35 rows=3 width=8) (actual time=0.024..0.062 rows=6 loops=1)

  • Recheck Cond: (org_code = 1618)
  • Heap Blocks: exact=6
9. 0.018 0.018 ↓ 2.0 6 1

Bitmap Index Scan on xak1_usr (cost=0.00..4.30 rows=3 width=0) (actual time=0.018..0.018 rows=6 loops=1)

  • Index Cond: (org_code = 1618)
10. 0.972 0.972 ↑ 2.1 28 6

Index Scan using request_requester_id_idx on request r (cost=0.42..136.07 rows=59 width=16) (actual time=0.009..0.162 rows=28 loops=6)

  • Index Cond: (requester_id = rqu.user_no)
11. 0.334 0.334 ↑ 1.0 1 167

Index Scan using work_system_pk1 on work_system ws (cost=0.28..0.30 rows=1 width=30) (actual time=0.002..0.002 rows=1 loops=167)

  • Index Cond: (r.system_id = system_id)
12. 1.670 1.670 ↑ 14.6 8 167

Index Scan using request_timesheet_request_id_idx on request_timesheet rt (cost=0.43..3.53 rows=117 width=8) (actual time=0.006..0.010 rows=8 loops=167)

  • Index Cond: (r.request_id = request_id)
13. 20.130 226.798 ↓ 15.0 15 1,342

GroupAggregate (cost=246.41..246.44 rows=1 width=16) (actual time=0.152..0.169 rows=15 loops=1,342)

  • Group Key: (date_trunc('month'::text, rt2_3.work_on)), (date_trunc('week'::text, rt2_3.work_on))
14. 32.208 206.668 ↓ 77.0 77 1,342

Sort (cost=246.41..246.42 rows=1 width=16) (actual time=0.150..0.154 rows=77 loops=1,342)

  • Sort Key: (date_trunc('month'::text, rt2_3.work_on)), (date_trunc('week'::text, rt2_3.work_on))
  • Sort Method: quicksort Memory: 29kB
15. 174.460 174.460 ↓ 77.0 77 1,342

Index Scan using request_timesheet_request_id_idx on request_timesheet rt2_3 (cost=0.43..246.40 rows=1 width=16) (actual time=0.013..0.130 rows=77 loops=1,342)

  • Index Cond: (request_id = r.request_id)
  • Filter: ((work_units = 'hours'::text) AND (date(work_on) >= '2019-01-01'::date) AND (date(work_on) <= '2020-01-31'::date))
  • Rows Removed by Filter: 15
16.          

SubPlan (for Nested Loop Left Join)

17. 16.104 173.118 ↓ 4.0 4 1,342

Group (cost=246.41..246.43 rows=1 width=8) (actual time=0.116..0.129 rows=4 loops=1,342)

  • Group Key: (date_trunc('month'::text, rt2.work_on))
18. 22.814 157.014 ↓ 77.0 77 1,342

Sort (cost=246.41..246.42 rows=1 width=8) (actual time=0.114..0.117 rows=77 loops=1,342)

  • Sort Key: (date_trunc('month'::text, rt2.work_on))
  • Sort Method: quicksort Memory: 27kB
19. 134.200 134.200 ↓ 77.0 77 1,342

Index Scan using request_timesheet_request_id_idx on request_timesheet rt2 (cost=0.43..246.40 rows=1 width=8) (actual time=0.013..0.100 rows=77 loops=1,342)

  • Index Cond: (request_id = r.request_id)
  • Filter: ((work_units = 'hours'::text) AND (date(work_on) >= '2019-01-01'::date) AND (date(work_on) <= '2020-01-31'::date))
  • Rows Removed by Filter: 15
20. 16.104 171.776 ↓ 4.0 4 1,342

GroupAggregate (cost=246.41..246.43 rows=1 width=16) (actual time=0.115..0.128 rows=4 loops=1,342)

  • Group Key: (date_trunc('month'::text, rt2_1.work_on))
21. 22.814 155.672 ↓ 77.0 77 1,342

Sort (cost=246.41..246.42 rows=1 width=16) (actual time=0.112..0.116 rows=77 loops=1,342)

  • Sort Key: (date_trunc('month'::text, rt2_1.work_on))
  • Sort Method: quicksort Memory: 27kB
22. 132.858 132.858 ↓ 77.0 77 1,342

Index Scan using request_timesheet_request_id_idx on request_timesheet rt2_1 (cost=0.43..246.40 rows=1 width=16) (actual time=0.012..0.099 rows=77 loops=1,342)

  • Index Cond: (request_id = r.request_id)
  • Filter: ((work_units = 'hours'::text) AND (date(work_on) >= '2019-01-01'::date) AND (date(work_on) <= '2020-01-31'::date))
  • Rows Removed by Filter: 15
23. 55.022 254.980 ↓ 15.0 15 1,342

Group (cost=246.41..246.45 rows=1 width=8) (actual time=0.149..0.190 rows=15 loops=1,342)

  • Group Key: (date_trunc('month'::text, rt2_2.work_on)), (date_trunc('week'::text, rt2_2.work_on))
24. 30.866 199.958 ↓ 77.0 77 1,342

Sort (cost=246.41..246.42 rows=1 width=8) (actual time=0.146..0.149 rows=77 loops=1,342)

  • Sort Key: (date_trunc('month'::text, rt2_2.work_on)), (date_trunc('week'::text, rt2_2.work_on))
  • Sort Method: quicksort Memory: 29kB
25. 169.092 169.092 ↓ 77.0 77 1,342

Index Scan using request_timesheet_request_id_idx on request_timesheet rt2_2 (cost=0.43..246.40 rows=1 width=8) (actual time=0.012..0.126 rows=77 loops=1,342)

  • Index Cond: (request_id = r.request_id)
  • Filter: ((work_units = 'hours'::text) AND (date(work_on) >= '2019-01-01'::date) AND (date(work_on) <= '2020-01-31'::date))
  • Rows Removed by Filter: 15
Planning time : 1.351 ms
Execution time : 864.105 ms