explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 44PB : prod_poslije_prod_3

Settings
# exclusive inclusive rows x rows loops node
1. 1.362 30,749.147 ↓ 24.4 16,000 1

Subquery Scan on v (cost=225,134.39..225,177.09 rows=657 width=860) (actual time=30,042.048..30,749.147 rows=16,000 loops=1)

2. 147.516 30,747.785 ↓ 24.4 16,000 1

GroupAggregate (cost=225,134.39..225,170.52 rows=657 width=927) (actual time=30,042.046..30,747.785 rows=16,000 loops=1)

  • Group Key: sr.srservicetype_code, (date_trunc('day'::text, sr.created)), sr.srtype_code, sr.refferedgroup, sr.srarea_code, sr.srsubarea_code, sr.created_by, rsr.name, rs.name, ra.name, rsa.name, ((((em.firstname)::text || ' '::text) || (em.lastnam
3. 1,433.861 30,600.269 ↓ 507.1 333,161 1

Sort (cost=225,134.39..225,136.03 rows=657 width=903) (actual time=30,042.012..30,600.269 rows=333,161 loops=1)

  • Sort Key: (date_trunc('day'::text, sr.created)), sr.srtype_code, sr.refferedgroup, sr.srarea_code, sr.srsubarea_code, sr.created_by, rsr.name, rs.name, ra.name, rsa.name, ((((em.firstname)::text || ' '::text) || (em.lastname)::text))
  • Sort Method: external merge Disk: 55,304kB
4. 2,773.304 29,166.408 ↓ 507.1 333,161 1

Merge Join (cost=1,008.17..225,103.64 rows=657 width=903) (actual time=2,464.240..29,166.408 rows=333,161 loops=1)

  • Merge Cond: (us.employee_id = em.id)
  • Join Filter: ((sr.created_by)::text = (us.code)::text)
  • Rows Removed by Join Filter: 36,314,549
5. 0.325 0.325 ↓ 1.0 110 1

Index Scan using umr_user_mployee_fk_i on umr_user us (cost=0.14..16.03 rows=108 width=16) (actual time=0.010..0.325 rows=110 loops=1)

  • Filter: ((status)::text = 'Active'::text)
6. 15,842.545 26,392.779 ↓ 511.7 37,314,032 1

Materialize (cost=1,008.02..228,569.46 rows=72,927 width=893) (actual time=1.051..26,392.779 rows=37,314,032 loops=1)

7. 5,625.220 10,550.234 ↓ 511.7 37,314,032 1

Nested Loop (cost=1,008.02..228,387.14 rows=72,927 width=893) (actual time=1.048..10,550.234 rows=37,314,032 loops=1)

8. 0.214 1.494 ↓ 1.0 112 1

Nested Loop (cost=0.14..32.34 rows=111 width=358) (actual time=0.022..1.494 rows=112 loops=1)

9. 1.168 1.168 ↓ 1.0 112 1

Index Scan using umr_employee_pkey on umr_employee em (cost=0.14..29.90 rows=111 width=22) (actual time=0.007..1.168 rows=112 loops=1)

  • Filter: ((status)::text = 'Active'::text)
10. 0.100 0.112 ↑ 1.0 1 112

Materialize (cost=0.00..1.05 rows=1 width=336) (actual time=0.000..0.001 rows=1 loops=112)

11. 0.012 0.012 ↑ 1.0 1 1

Seq Scan on r_srservicetype rsr (cost=0.00..1.04 rows=1 width=336) (actual time=0.011..0.012 rows=1 loops=1)

  • Filter: (((code)::text = 'CUSTOMER_TICKET'::text) AND ((status)::text = 'Active'::text))
  • Rows Removed by Filter: 2
12. 4,404.919 4,923.520 ↓ 507.1 333,161 112

Materialize (cost=1,007.88..227,444.86 rows=657 width=613) (actual time=0.014..43.960 rows=333,161 loops=112)

13. 0.000 518.601 ↓ 507.1 333,161 1

Gather (cost=1,007.88..227,441.57 rows=657 width=613) (actual time=1.018..518.601 rows=333,161 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
14. 0.000 728.207 ↓ 406.3 66,632 5 / 5

Nested Loop Anti Join (cost=7.88..226,375.87 rows=164 width=613) (actual time=0.294..728.207 rows=66,632 loops=5)

15. 17.416 474.372 ↓ 407.8 66,878 5 / 5

Hash Left Join (cost=7.45..226,169.35 rows=164 width=621) (actual time=0.242..474.372 rows=66,878 loops=5)

  • Hash Cond: ((sr.srsubarea_code)::text = (rsa.code)::text)
16. 17.356 456.891 ↓ 407.8 66,878 5 / 5

Hash Join (cost=2.49..226,163.94 rows=164 width=607) (actual time=0.164..456.891 rows=66,878 loops=5)

  • Hash Cond: ((sr.srtype_code)::text = (rs.code)::text)
17. 17.948 439.504 ↓ 23.9 66,878 5 / 5

Hash Join (cost=1.26..226,153.56 rows=2,794 width=349) (actual time=0.064..439.504 rows=66,878 loops=5)

  • Hash Cond: ((sr.srarea_code)::text = (ra.code)::text)
18. 421.534 421.534 ↓ 1.2 66,878 5 / 5

Parallel Seq Scan on srservicerequest sr (cost=0.00..225,968.58 rows=58,148 width=91) (actual time=0.031..421.534 rows=66,878 loops=5)

  • Filter: ((duration IS NOT NULL) AND ((status)::text = ANY ('{Completed,Closed}'::text[])) AND ((srservicetype_code)::text = 'CUSTOMER_TICKET'::text) AND (date_trunc('day'::text, dat
  • Rows Removed by Filter: 421,425
19. 0.007 0.022 ↓ 20.0 20 5 / 5

Hash (cost=1.25..1.25 rows=1 width=336) (actual time=0.022..0.022 rows=20 loops=5)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
20. 0.015 0.015 ↓ 20.0 20 5 / 5

Seq Scan on r_srarea ra (cost=0.00..1.25 rows=1 width=336) (actual time=0.012..0.015 rows=20 loops=5)

  • Filter: ((status)::text = 'Active'::text)
21. 0.006 0.031 ↓ 14.0 14 5 / 5

Hash (cost=1.21..1.21 rows=1 width=336) (actual time=0.031..0.031 rows=14 loops=5)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
22. 0.025 0.025 ↓ 14.0 14 5 / 5

Seq Scan on r_srtype rs (cost=0.00..1.21 rows=1 width=336) (actual time=0.022..0.025 rows=14 loops=5)

  • Filter: ((status)::text = 'Active'::text)
  • Rows Removed by Filter: 3
23. 0.023 0.065 ↑ 1.0 117 5 / 5

Hash (cost=3.49..3.49 rows=118 width=27) (actual time=0.065..0.065 rows=117 loops=5)

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
24. 0.042 0.042 ↑ 1.0 117 5 / 5

Seq Scan on r_srsubarea rsa (cost=0.00..3.49 rows=118 width=27) (actual time=0.015..0.042 rows=117 loops=5)

  • Filter: ((status)::text = 'Active'::text)
  • Rows Removed by Filter: 1
25. 267.510 267.510 ↓ 0.0 0 334,388 / 5

Index Scan using srserviceassignment_servicereq_id on srserviceassignment sa (cost=0.43..1.25 rows=1 width=11) (actual time=0.004..0.004 rows=0 loops=334,388)

  • Index Cond: (servicerequest_id = sr.id)
  • Filter: ((assigneduser_code)::text <> (sr.created_by)::text)
  • Rows Removed by Filter: 1
Planning time : 9.775 ms
Execution time : 31,961.677 ms