explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XtnL

Settings
# exclusive inclusive rows x rows loops node
1. 1.927 699.120 ↓ 13.2 66 1

Unique (cost=5,650.74..5,650.84 rows=5 width=180) (actual time=697.138..699.120 rows=66 loops=1)

2. 6.208 697.193 ↓ 149.6 748 1

Sort (cost=5,650.74..5,650.75 rows=5 width=180) (actual time=697.136..697.193 rows=748 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: 782kB
3. 13.872 690.985 ↓ 149.6 748 1

Nested Loop Left Join (cost=1.69..5,650.68 rows=5 width=180) (actual time=0.877..690.985 rows=748 loops=1)

4. 9.235 12.141 ↓ 149.6 748 1

Nested Loop (cost=1.41..818.38 rows=5 width=30) (actual time=0.427..12.141 rows=748 loops=1)

  • -> Index Scan using request_timesheet_request_id_idx on request_timesheet rt (cost=0.43..4.66 rows=1 width=8) (actual time=0.048..0.061 rows=5 l
5. 0.143 2.906 ↓ 1.8 144 1

Nested Loop (cost=0.98..444.46 rows=80 width=30) (actual time=0.166..2.906 rows=144 loops=1)

  • Index Cond: (request_id = r.request_id)
  • Filter: ((date(work_on) >= '2019-01-01'::date) AND (date(work_on) <= '2020-01-31'::date))
  • Rows Removed by Filter: 2
6. 0.080 0.218 ↓ 1.7 5 1

Nested Loop (cost=0.56..23.53 rows=3 width=26) (actual time=0.126..0.218 rows=5 loops=1)

  • -> Index Scan using organisation_org_code_idx on organisation o (cost=0.28..8.29 rows=1 width=26) (actual time=0.072..0.073 rows=1 l
  • Index Cond: (org_code = 1618)
7. 0.138 0.138 ↓ 1.7 5 1

Index Scan using xak1_usr on usr rqu (cost=0.28..15.20 rows=3 width=8) (actual time=0.050..0.138 rows=5 loops=1)

  • Index Cond: (org_code = 1618)
8. 2.545 2.545 ↑ 2.1 29 5

Index Scan using request_requester_id_idx on request r (cost=0.42..139.71 rows=60 width=16) (actual time=0.038..0.509 rows=29 loops=5)

  • Index Cond: (requester_id = rqu.user_no)
9. 2.992 2.992 ↑ 1.0 1 748

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

  • Index Cond: (r.system_id = system_id)
10. 12.716 139.128 ↓ 5.0 5 748

GroupAggregate (cost=241.51..241.53 rows=1 width=16) (actual time=0.168..0.186 rows=5 loops=748)

  • Group Key: (date_trunc('month'::text, rt2_1.work_on))
11. 20.196 126.412 ↓ 77.0 77 748

Sort (cost=241.51..241.52 rows=1 width=16) (actual time=0.164..0.169 rows=77 loops=748)

  • Sort Key: (date_trunc('month'::text, rt2_1.work_on))
  • Sort Method: quicksort Memory: 25kB
12. 106.216 106.216 ↓ 77.0 77 748

Index Scan using request_timesheet_request_id_idx on request_timesheet rt2_1 (cost=0.43..241.50 rows=1 width=16) (actual time=0.014..0.142 rows=77 loops=748)

  • 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: 6
13.          

SubPlan (for Nested Loop Left Join)

14. 10.472 142.120 ↓ 5.0 5 748

Group (cost=241.51..241.53 rows=1 width=40) (actual time=0.174..0.190 rows=5 loops=748)

  • Group Key: (date_trunc('month'::text, rt2.work_on))
15. 131.648 131.648 ↓ 77.0 77 748

Sort (cost=241.51..241.52 rows=1 width=16) (actual time=0.171..0.176 rows=77 loops=748)

  • Sort Key: (date_trunc('month'::text, rt2.work_on))
  • Sort Method: quicksort Memory: 25kB
  • -> Index Scan using request_timesheet_request_id_idx on request_timesheet rt2 (cost=0.43..241.50 rows=1 width=16) (actual time=0.013..0.
  • 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: 6
16. 26.180 198.968 ↓ 17.0 17 748

Group (cost=241.51..241.55 rows=1 width=48) (actual time=0.229..0.266 rows=17 loops=748)

  • Group Key: (date_trunc('month'::text, rt2_2.work_on)), (date_trunc('week'::text, rt2_2.work_on))
17. 33.660 172.788 ↓ 77.0 77 748

Sort (cost=241.51..241.52 rows=1 width=24) (actual time=0.226..0.231 rows=77 loops=748)

  • Sort Key: (date_trunc('month'::text, rt2_2.work_on)), (date_trunc('week'::text, rt2_2.work_on))
  • Sort Method: quicksort Memory: 26kB
18. 139.128 139.128 ↓ 77.0 77 748

Index Scan using request_timesheet_request_id_idx on request_timesheet rt2_2 (cost=0.43..241.50 rows=1 width=24) (actual time=0.014..0.186 rows=77 loops=748)

  • 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: 6
19. 15.708 181.764 ↓ 17.0 17 748

GroupAggregate (cost=241.51..241.54 rows=1 width=24) (actual time=0.220..0.243 rows=17 loops=748)

  • Group Key: (date_trunc('month'::text, rt2_3.work_on)), (date_trunc('week'::text, rt2_3.work_on))
20. 27.676 166.056 ↓ 77.0 77 748

Sort (cost=241.51..241.52 rows=1 width=24) (actual time=0.217..0.222 rows=77 loops=748)

  • Sort Key: (date_trunc('month'::text, rt2_3.work_on)), (date_trunc('week'::text, rt2_3.work_on))
  • Sort Method: quicksort Memory: 26kB
21. 138.380 138.380 ↓ 77.0 77 748

Index Scan using request_timesheet_request_id_idx on request_timesheet rt2_3 (cost=0.43..241.50 rows=1 width=24) (actual time=0.015..0.185 rows=77 loops=748)

  • 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: 6
Planning time : 7.370 ms
Execution time : 699.817 ms