explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Rpn

Settings
# exclusive inclusive rows x rows loops node
1. 0.034 4,183.648 ↑ 1.0 1 1

Aggregate (cost=718,573.68..718,573.69 rows=1 width=32) (actual time=4,183.648..4,183.648 rows=1 loops=1)

  • Functions: 88
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 21.581 ms, Inlining 163.227 ms, Optimization 1481.480 ms, Emission 1784.921 ms, Total 3451.209 ms"Execution Time: 4,204.175 ms
2. 0.027 4,183.614 ↑ 1.0 1 1

Subquery Scan on cont_table (cost=385,959.71..718,573.68 rows=1 width=80) (actual time=4,183.603..4,183.614 rows=1 loops=1)

3. 3,132.673 4,183.587 ↑ 1.0 1 1

Limit (cost=385,959.71..718,573.67 rows=1 width=1,592) (actual time=4,183.578..4,183.587 rows=1 loops=1)

4. 3.213 1,050.914 ↑ 1.0 1 1

Result (cost=385,959.71..718,573.67 rows=1 width=1,592) (actual time=1,050.905..1,050.914 rows=1 loops=1)

5. 0.024 721.668 ↑ 1.0 1 1

Sort (cost=385,959.71..385,959.71 rows=1 width=346) (actual time=721.667..721.668 rows=1 loops=1)

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

Nested Loop (cost=84,797.37..385,959.70 rows=1 width=346) (actual time=721.237..721.644 rows=1 loops=1)

7. 0.021 387.208 ↑ 1.0 1 1

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

8. 0.029 383.881 ↓ 29.0 29 1

Merge Join (cost=84,796.51..84,796.75 rows=1 width=8) (actual time=383.705..383.881 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.036 166.609 ↓ 14.5 29 1

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

  • Group Key: ((process_1.proc_inst_id)::bigint)
10. 0.082 166.573 ↓ 22.0 44 1

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

11. 0.039 166.491 ↓ 22.0 44 1

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

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

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

13. 15.525 166.126 ↓ 29.0 29 1

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

  • Workers Planned: 4
  • Workers Launched: 3
14. 150.601 150.601 ↓ 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=106.655..150.601 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.018 217.243 ↓ 22.0 44 1

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

17. 0.110 217.225 ↓ 22.0 44 1

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

18. 0.037 217.115 ↓ 22.0 44 1

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

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

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

20. 0.473 216.757 ↓ 29.0 29 1

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

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

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

  • Filter: (proc_def = 'TSAR2'::text)
  • Rows Removed by Filter: 2,308,293
22. 0.319 0.319 ↑ 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.010..0.011 rows=2 loops=29)

  • Index Cond: (proc_inst_id = process_2.proc_inst_id)
23. 3.306 3.306 ↓ 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.114..0.114 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 333.159 ↑ 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=333.157..333.159 rows=1 loops=1)

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

SubPlan (for Index Only Scan)

26. 333.133 333.133 ↑ 1,352.0 1 1

Index Scan using ix_act_name_proc_id on act_hi_varinst ahv_2 (cost=0.70..301,158.12 rows=1,352 width=39) (actual time=333.109..333.133 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. 324.074 324.074 ↑ 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=324.051..324.074 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
29. 1.959 1.959 ↓ 0.0 0 1

Index Scan using ix_act_name_proc_id on act_hi_varinst ahv_1 (cost=0.70..31,453.99 rows=139 width=39) (actual time=1.959..1.959 rows=0 loops=1)

  • Index Cond: ((name_)::text = 'tamoProcesses'::text)
  • Filter: ((proc_inst_id_)::bigint = (process.proc_inst_id)::bigint)
  • Rows Removed by Filter: 2