explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NnsF

Settings
# exclusive inclusive rows x rows loops node
1. 0.054 4,317.240 ↑ 1.6 12 1

Unique (cost=20,012.04..20,012.42 rows=19 width=180) (actual time=4,317.185..4,317.240 rows=12 loops=1)

2. 0.171 4,317.186 ↓ 1.5 29 1

Sort (cost=20,012.04..20,012.09 rows=19 width=180) (actual time=4,317.183..4,317.186 rows=29 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: 54kB
3. 0.634 4,317.015 ↓ 1.5 29 1

Nested Loop Left Join (cost=1.69..20,011.64 rows=19 width=180) (actual time=790.461..4,317.015 rows=29 loops=1)

4. 3,821.706 4,178.486 ↓ 1.5 29 1

Nested Loop (cost=1.41..1,648.92 rows=19 width=30) (actual time=662.972..4,178.486 rows=29 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.090..0.091 rows=0 l
5. 27.948 356.780 ↓ 132.1 41,600 1

Nested Loop (cost=0.98..176.59 rows=315 width=30) (actual time=0.054..356.780 rows=41,600 loops=1)

  • Index Cond: (request_id = r.request_id)
  • Filter: ((date(work_on) >= '2020-01-01'::date) AND (date(work_on) <= '2020-01-31'::date))
  • Rows Removed by Filter: 21
6. 0.518 6.846 ↓ 63.6 763 1

Nested Loop (cost=0.56..66.17 rows=12 width=26) (actual time=0.028..6.846 rows=763 loops=1)

  • -> Index Scan using organisation_org_code_idx on organisation o (cost=0.28..14.95 rows=2 width=26) (actual time=0.016..0.017 rows=1
  • Index Cond: (org_code = ANY ('{37,24}'::integer[]))
7. 6.328 6.328 ↓ 127.2 763 1

Index Scan using xak1_usr on usr rqu (cost=0.28..25.55 rows=6 width=8) (actual time=0.008..6.328 rows=763 loops=1)

  • Index Cond: (org_code = o.org_code)
8. 321.986 321.986 ↑ 1.1 55 763

Index Scan using request_requester_id_idx on request r (cost=0.42..8.60 rows=60 width=16) (actual time=0.018..0.422 rows=55 loops=763)

  • Index Cond: (requester_id = rqu.user_no)
9. 0.290 0.290 ↑ 1.0 1 29

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

  • Index Cond: (r.system_id = system_id)
10.          

SubPlan (for Nested Loop Left Join)

11. 0.493 76.444 ↓ 4.0 4 29

Group (cost=241.51..241.53 rows=1 width=40) (actual time=2.618..2.636 rows=4 loops=29)

  • Group Key: (date_trunc('month'::text, rt2.work_on))
12. 75.951 75.951 ↓ 85.0 85 29

Sort (cost=241.51..241.52 rows=1 width=16) (actual time=2.613..2.619 rows=85 loops=29)

  • 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.009..2.
  • 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: 449
13. 0.522 20.735 ↓ 4.0 4 29

GroupAggregate (cost=241.51..241.53 rows=1 width=16) (actual time=0.697..0.715 rows=4 loops=29)

  • Group Key: (date_trunc('month'::text, rt2_1.work_on))
14. 0.870 20.213 ↓ 85.0 85 29

Sort (cost=241.51..241.52 rows=1 width=16) (actual time=0.691..0.697 rows=85 loops=29)

  • Sort Key: (date_trunc('month'::text, rt2_1.work_on))
  • Sort Method: quicksort Memory: 25kB
15. 19.343 19.343 ↓ 85.0 85 29

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.008..0.667 rows=85 loops=29)

  • 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: 449
16. 0.783 20.358 ↓ 10.0 10 29

Group (cost=241.51..241.55 rows=1 width=48) (actual time=0.672..0.702 rows=10 loops=29)

  • Group Key: (date_trunc('month'::text, rt2_2.work_on)), (date_trunc('week'::text, rt2_2.work_on))
17. 1.189 19.575 ↓ 85.0 85 29

Sort (cost=241.51..241.52 rows=1 width=24) (actual time=0.669..0.675 rows=85 loops=29)

  • Sort Key: (date_trunc('month'::text, rt2_2.work_on)), (date_trunc('week'::text, rt2_2.work_on))
  • Sort Method: quicksort Memory: 25kB
18. 18.386 18.386 ↓ 85.0 85 29

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.008..0.634 rows=85 loops=29)

  • 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: 449
19. 0.638 20.068 ↓ 10.0 10 29

GroupAggregate (cost=241.51..241.54 rows=1 width=24) (actual time=0.668..0.692 rows=10 loops=29)

  • Group Key: (date_trunc('month'::text, rt2_3.work_on)), (date_trunc('week'::text, rt2_3.work_on))
20. 1.160 19.430 ↓ 85.0 85 29

Sort (cost=241.51..241.52 rows=1 width=24) (actual time=0.664..0.670 rows=85 loops=29)

  • Sort Key: (date_trunc('month'::text, rt2_3.work_on)), (date_trunc('week'::text, rt2_3.work_on))
  • Sort Method: quicksort Memory: 25kB
21. 18.270 18.270 ↓ 85.0 85 29

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.008..0.630 rows=85 loops=29)

  • 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: 449
Planning time : 3.272 ms
Execution time : 4,317.475 ms