explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XX3y

Settings
# exclusive inclusive rows x rows loops node
1. 0.276 290.744 ↑ 25.1 245 1

Hash Right Join (cost=43,397.83..48,673.01 rows=6,155 width=56) (actual time=238.354..290.744 rows=245 loops=1)

  • Hash Cond: ((act_ru_identitylink.user_id_)::bpchar = (active.personid)::bpchar)
2.          

CTE active

3. 0.034 130.724 ↓ 27.2 245 1

Unique (cost=34,531.51..34,533.27 rows=9 width=13) (actual time=130.549..130.724 rows=245 loops=1)

4. 0.126 130.690 ↓ 28.9 260 1

Merge Anti Join (cost=34,531.51..34,533.25 rows=9 width=13) (actual time=130.549..130.690 rows=260 loops=1)

  • Merge Cond: (s.personid = a.personid)
5. 0.334 130.051 ↓ 29.6 266 1

Sort (cost=34,503.99..34,504.01 rows=9 width=13) (actual time=130.043..130.051 rows=266 loops=1)

  • Sort Key: s.personid
  • Sort Method: quicksort Memory: 37kB
6. 0.017 129.717 ↓ 29.6 266 1

Nested Loop (cost=0.44..34,503.85 rows=9 width=13) (actual time=0.028..129.717 rows=266 loops=1)

7. 0.006 0.006 ↑ 1.0 1 1

Seq Scan on session_policy p (cost=0.00..1.01 rows=1 width=0) (actual time=0.005..0.006 rows=1 loops=1)

8. 129.694 129.694 ↓ 29.6 266 1

Index Scan using sessions_signonstatus on sessions s (cost=0.44..34,502.75 rows=9 width=13) (actual time=0.022..129.694 rows=266 loops=1)

  • Index Cond: (signonstatus = 'A'::bpchar)
  • Filter: (lastts > (CURRENT_TIMESTAMP - '00:20:00'::interval))
  • Rows Removed by Filter: 140006
9. 0.447 0.513 ↑ 1.0 336 1

Sort (cost=27.52..28.36 rows=337 width=13) (actual time=0.503..0.513 rows=336 loops=1)

  • Sort Key: a.personid
  • Sort Method: quicksort Memory: 41kB
10. 0.066 0.066 ↓ 1.0 344 1

Seq Scan on company_api a (cost=0.00..13.37 rows=337 width=13) (actual time=0.009..0.066 rows=344 loops=1)

11. 32.755 53.443 ↑ 1.0 2,846 1

GroupAggregate (cost=0.42..4,991.09 rows=2,863 width=14) (actual time=0.054..53.443 rows=2,846 loops=1)

  • Group Key: act_ru_identitylink.user_id_
12. 20.688 20.688 ↑ 2.0 74,525 1

Index Scan using act_idx_ident_lnk_user on act_ru_identitylink (cost=0.42..4,230.20 rows=146,452 width=14) (actual time=0.011..20.688 rows=74,525 loops=1)

13. 0.033 237.025 ↑ 1.8 245 1

Hash (cost=8,858.77..8,858.77 rows=430 width=76) (actual time=237.025..237.025 rows=245 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
14. 0.092 236.992 ↑ 1.8 245 1

Hash Right Join (cost=8,630.69..8,858.77 rows=430 width=76) (actual time=199.264..236.992 rows=245 loops=1)

  • Hash Cond: ((astc.assignee_)::bpchar = (active.personid)::bpchar)
15. 37.526 62.709 ↑ 2.1 666 1

GroupAggregate (cost=3,670.06..3,864.35 rows=1,388 width=14) (actual time=24.370..62.709 rows=666 loops=1)

  • Group Key: astc.assignee_
16. 18.088 25.183 ↓ 1.2 30,023 1

Sort (cost=3,670.06..3,730.20 rows=24,054 width=14) (actual time=23.975..25.183 rows=30,023 loops=1)

  • Sort Key: astc.assignee_
  • Sort Method: quicksort Memory: 2176kB
17. 7.095 7.095 ↓ 1.2 30,023 1

Seq Scan on act_ru_task astc (cost=0.00..1,919.65 rows=24,054 width=14) (actual time=0.005..7.095 rows=30,023 loops=1)

  • Filter: (((proc_def_id_)::text ~~ 'asyncProcess%'::text) AND ((task_def_key_)::text = 'confirmProcess'::text))
  • Rows Removed by Filter: 13537
18. 0.027 174.191 ↓ 4.0 245 1

Hash (cost=4,959.85..4,959.85 rows=62 width=68) (actual time=174.191..174.191 rows=245 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
19. 0.087 174.164 ↓ 4.0 245 1

Hash Right Join (cost=4,808.53..4,959.85 rows=62 width=68) (actual time=148.409..174.164 rows=245 loops=1)

  • Hash Cond: ((t.assignee_)::bpchar = active.personid)
20. 25.705 35.133 ↑ 1.9 747 1

GroupAggregate (cost=2,887.23..3,018.84 rows=1,388 width=14) (actual time=9.028..35.133 rows=747 loops=1)

  • Group Key: t.assignee_
21. 2.653 9.428 ↑ 1.2 13,537 1

Sort (cost=2,887.23..2,926.47 rows=15,698 width=14) (actual time=9.019..9.428 rows=13,537 loops=1)

  • Sort Key: t.assignee_
  • Sort Method: quicksort Memory: 1019kB
22. 6.775 6.775 ↑ 1.2 13,537 1

Seq Scan on act_ru_task t (cost=0.00..1,793.21 rows=15,698 width=14) (actual time=0.007..6.775 rows=13,537 loops=1)

  • Filter: ((proc_def_id_)::text !~~ 'asyncProcess%'::text)
  • Rows Removed by Filter: 30023
23. 0.031 138.944 ↓ 27.2 245 1

Hash (cost=1,921.19..1,921.19 rows=9 width=60) (actual time=138.944..138.944 rows=245 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
24. 0.021 138.913 ↓ 27.2 245 1

Merge Left Join (cost=1,920.98..1,921.19 rows=9 width=60) (actual time=138.884..138.913 rows=245 loops=1)

  • Merge Cond: ((active.personid)::bpchar = (astp.personid)::bpchar)
25. 0.066 130.814 ↓ 27.2 245 1

Sort (cost=0.32..0.35 rows=9 width=52) (actual time=130.804..130.814 rows=245 loops=1)

  • Sort Key: active.personid
  • Sort Method: quicksort Memory: 36kB
26. 130.748 130.748 ↓ 27.2 245 1

CTE Scan on active (cost=0.00..0.18 rows=9 width=52) (actual time=130.551..130.748 rows=245 loops=1)

27. 0.002 8.078 ↓ 0.0 0 1

Sort (cost=1,920.65..1,920.69 rows=15 width=14) (actual time=8.078..8.078 rows=0 loops=1)

  • Sort Key: astp.personid USING <
  • Sort Method: quicksort Memory: 25kB
28. 0.000 8.076 ↓ 0.0 0 1

Subquery Scan on astp (cost=1,919.95..1,920.36 rows=15 width=14) (actual time=8.076..8.076 rows=0 loops=1)

29. 0.001 8.076 ↓ 0.0 0 1

GroupAggregate (cost=1,919.95..1,920.21 rows=15 width=14) (actual time=8.076..8.076 rows=0 loops=1)

  • Group Key: astp_1.assignee_
30. 0.003 8.075 ↓ 0.0 0 1

Sort (cost=1,919.95..1,919.99 rows=15 width=14) (actual time=8.075..8.075 rows=0 loops=1)

  • Sort Key: astp_1.assignee_
  • Sort Method: quicksort Memory: 25kB
31. 8.072 8.072 ↓ 0.0 0 1

Seq Scan on act_ru_task astp_1 (cost=0.00..1,919.65 rows=15 width=14) (actual time=8.072..8.072 rows=0 loops=1)

  • Filter: (((proc_def_id_)::text ~~ 'asyncProcess%'::text) AND ((task_def_key_)::text = ANY ('{initiateProcess,executeProcess}'::text[])))
  • Rows Removed by Filter: 43560