explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lvT9

Settings
# exclusive inclusive rows x rows loops node
1. 1.759 690.675 ↓ 13.2 66 1

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

2. 4.714 688.916 ↓ 149.6 748 1

Sort (cost=5,650.74..5,650.75 rows=5 width=180) (actual time=688.862..688.916 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.255 684.202 ↓ 149.6 748 1

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

4. 3.314 3.731 ↓ 149.6 748 1

Nested Loop (cost=1.41..818.38 rows=5 width=30) (actual time=0.106..3.731 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.009..0.020 rows=5 l
5. 0.100 0.417 ↓ 1.8 144 1

Nested Loop (cost=0.98..444.46 rows=80 width=30) (actual time=0.037..0.417 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.021 0.042 ↓ 1.7 5 1

Nested Loop (cost=0.56..23.53 rows=3 width=26) (actual time=0.026..0.042 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.014..0.015 rows=1 l
  • Index Cond: (org_code = 1618)
7. 0.021 0.021 ↓ 1.7 5 1

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

  • Index Cond: (org_code = 1618)
8. 0.275 0.275 ↑ 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.008..0.055 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 145.112 ↓ 5.0 5 748

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

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

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

  • Sort Key: (date_trunc('month'::text, rt2_1.work_on))
  • Sort Method: quicksort Memory: 25kB
12. 111.452 111.452 ↓ 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.149 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 139.128 ↓ 5.0 5 748

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

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

Sort (cost=241.51..241.52 rows=1 width=16) (actual time=0.167..0.172 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 195.976 ↓ 17.0 17 748

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

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

Sort (cost=241.51..241.52 rows=1 width=24) (actual time=0.221..0.227 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. 141.372 141.372 ↓ 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.189 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 184.008 ↓ 17.0 17 748

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

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

Sort (cost=241.51..241.52 rows=1 width=24) (actual time=0.219..0.225 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. 139.876 139.876 ↓ 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.187 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 : 3.086 ms
Execution time : 690.914 ms