explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fo5W

Settings
# exclusive inclusive rows x rows loops node
1. 0.018 1,793.212 ↑ 1.0 1 1

Aggregate (cost=687,117.85..687,117.86 rows=1 width=32) (actual time=1,793.211..1,793.212 rows=1 loops=1)

  • Functions: 80
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 11.969 ms, Inlining 163.438 ms, Optimization 542.796 ms, Emission 357.411 ms, Total 1075.614 ms"Execution Time: 1,803.772 ms
2. 0.007 1,793.194 ↑ 1.0 1 1

Subquery Scan on cont_table (cost=385,959.67..687,117.84 rows=1 width=52) (actual time=1,793.192..1,793.194 rows=1 loops=1)

3. 748.121 1,793.187 ↑ 1.0 1 1

Limit (cost=385,959.67..687,117.83 rows=1 width=632) (actual time=1,793.184..1,793.187 rows=1 loops=1)

4. 0.009 1,045.066 ↑ 1.0 1 1

Result (cost=385,959.67..687,117.83 rows=1 width=632) (actual time=1,045.064..1,045.066 rows=1 loops=1)

5. 0.019 725.564 ↑ 1.0 1 1

Sort (cost=385,959.67..385,959.68 rows=1 width=126) (actual time=725.563..725.564 rows=1 loops=1)

  • Sort Key: ((process.proc_inst_id)::bigint) DESC
  • Sort Method: quicksort Memory: 25kB
6. 0.347 725.545 ↑ 1.0 1 1

Nested Loop (cost=84,797.37..385,959.66 rows=1 width=126) (actual time=725.102..725.545 rows=1 loops=1)

7. 0.021 396.274 ↑ 1.0 1 1

Nested Loop (cost=84,796.94..84,800.39 rows=1 width=88) (actual time=395.832..396.274 rows=1 loops=1)

8. 0.025 393.005 ↓ 29.0 29 1

Merge Join (cost=84,796.51..84,796.75 rows=1 width=8) (actual time=392.869..393.005 rows=29 loops=1)

  • Merge Cond: (((process_1.proc_inst_id)::bigint) = ((process_2.proc_inst_id)::bigint))
  • Join Filter: ((max((row_number() OVER (?)))) = (row_number() OVER (?)))
  • Rows Removed by Join Filter: 15
9. 0.044 171.682 ↓ 14.5 29 1

GroupAggregate (cost=42,398.25..42,398.35 rows=2 width=16) (actual time=171.623..171.682 rows=29 loops=1)

  • Group Key: ((process_1.proc_inst_id)::bigint)
10. 0.057 171.638 ↓ 22.0 44 1

WindowAgg (cost=42,398.25..42,398.30 rows=2 width=28) (actual time=171.598..171.638 rows=44 loops=1)

11. 0.030 171.581 ↓ 22.0 44 1

Sort (cost=42,398.25..42,398.26 rows=2 width=12) (actual time=171.575..171.581 rows=44 loops=1)

  • Sort Key: ((process_1.proc_inst_id)::bigint), task_1.id
  • Sort Method: quicksort Memory: 27kB
12. 0.000 171.551 ↓ 22.0 44 1

Nested Loop (cost=1,000.43..42,398.24 rows=2 width=12) (actual time=2.526..171.551 rows=44 loops=1)

13. 15.735 171.219 ↓ 29.0 29 1

Gather (cost=1,000.00..42,394.59 rows=1 width=10) (actual time=2.486..171.219 rows=29 loops=1)

  • Workers Planned: 4
  • Workers Launched: 3
14. 155.484 155.484 ↓ 7.0 7 4 / 4

Parallel Seq Scan on process_info process_1 (cost=0.00..41,394.49 rows=1 width=10) (actual time=105.028..155.484 rows=7 loops=4)

  • Filter: (proc_def = 'TSAR2'::text)
  • Rows Removed by Filter: 577,073
15. 0.348 0.348 ↑ 1.0 2 29

Index Scan using idx_task_info_proc_inst_id_ on task_info task_1 (cost=0.43..3.62 rows=2 width=14) (actual time=0.011..0.012 rows=2 loops=29)

  • Index Cond: (proc_inst_id = process_1.proc_inst_id)
16. 0.016 221.298 ↓ 22.0 44 1

Materialize (cost=42,398.25..42,398.34 rows=2 width=24) (actual time=221.236..221.298 rows=44 loops=1)

17. 0.066 221.282 ↓ 22.0 44 1

WindowAgg (cost=42,398.25..42,398.31 rows=2 width=28) (actual time=221.234..221.282 rows=44 loops=1)

18. 0.048 221.216 ↓ 22.0 44 1

Sort (cost=42,398.25..42,398.26 rows=2 width=22) (actual time=221.211..221.216 rows=44 loops=1)

  • Sort Key: ((process_2.proc_inst_id)::bigint), task_2.id
  • Sort Method: quicksort Memory: 28kB
19. 0.032 221.168 ↓ 22.0 44 1

Nested Loop (cost=1,000.43..42,398.24 rows=2 width=22) (actual time=2.520..221.168 rows=44 loops=1)

20. 0.451 220.788 ↓ 29.0 29 1

Gather (cost=1,000.00..42,394.59 rows=1 width=10) (actual time=2.497..220.788 rows=29 loops=1)

  • Workers Planned: 4
  • Workers Launched: 0
21. 220.337 220.337 ↓ 29.0 29 1

Parallel Seq Scan on process_info process_2 (cost=0.00..41,394.49 rows=1 width=10) (actual time=2.103..220.337 rows=29 loops=1)

  • Filter: (proc_def = 'TSAR2'::text)
  • Rows Removed by Filter: 2,308,292
22. 0.348 0.348 ↑ 1.0 2 29

Index Scan using idx_task_info_proc_inst_id_ on task_info task_2 (cost=0.43..3.62 rows=2 width=24) (actual time=0.011..0.012 rows=2 loops=29)

  • Index Cond: (proc_inst_id = process_2.proc_inst_id)
23. 3.248 3.248 ↓ 0.0 0 29

Index Scan using idx_task_info_task_id_bigint_ on task_info task (cost=0.43..3.63 rows=1 width=88) (actual time=0.112..0.112 rows=0 loops=29)

  • Index Cond: ((task_id)::bigint = ((task_2.task_id)::bigint))
  • Filter: (((doc -> 'pick_site'::text) ->> 'siteid'::text) = 'IHS_LAG_0011B'::text)
  • Rows Removed by Filter: 1
24. 0.026 328.924 ↑ 1.0 1 1

Index Only Scan using idx_process_info_proc_inst_id_ on process_info process (cost=0.43..301,159.24 rows=1 width=10) (actual time=328.923..328.924 rows=1 loops=1)

  • Index Cond: (proc_inst_id = task.proc_inst_id)
  • Filter: (((SubPlan 2))::text <> ALL ('{Cancelled,Rejected,Expired}'::text[]))
  • Heap Fetches: 1
25.          

SubPlan (for Index Only Scan)

26. 328.898 328.898 ↑ 1,352.0 1 1

Index Scan using ix_act_name_proc_id on act_hi_varinst ahv_1 (cost=0.70..301,158.12 rows=1,352 width=39) (actual time=328.876..328.898 rows=1 loops=1)

  • Index Cond: ((name_)::text = 'process_status'::text)
  • Filter: ((proc_inst_id_)::bigint = (process.proc_inst_id)::bigint)
  • Rows Removed by Filter: 309,942
27.          

SubPlan (for Result)

28. 319.493 319.493 ↑ 1,352.0 1 1

Index Scan using ix_act_name_proc_id on act_hi_varinst ahv (cost=0.70..301,158.12 rows=1,352 width=39) (actual time=319.470..319.493 rows=1 loops=1)

  • Index Cond: ((name_)::text = 'process_status'::text)
  • Filter: ((proc_inst_id_)::bigint = (process.proc_inst_id)::bigint)
  • Rows Removed by Filter: 309,942