explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zPC7 : LOVVO x

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1.943 48.420 ↑ 6.1 7 1
→

Nested Loop (cost=271.01..9,646.47 rows=43 width=207) (actual time=36.887..48.420 rows=7 loops=1)

2. 0.323 46.393 ↑ 6.1 7 1
→

Nested Loop (cost=270.73..6,622.48 rows=43 width=153) (actual time=36.544..46.393 rows=7 loops=1)

3. 0.270 35.972 ↓ 1.2 51 1
→

Nested Loop (cost=195.84..222.04 rows=43 width=134) (actual time=35.086..35.972 rows=51 loops=1)

4. 0.229 35.396 ↓ 1.2 51 1
→

Hash Join (cost=195.57..204.36 rows=43 width=115) (actual time=35.069..35.396 rows=51 loops=1)

  • Hash Cond: ((max(exec.pipeline_stage_exec_id)) = stage_exec.pipeline_stage_exec_id)
5. 2.337 12.347 ↑ 6.9 51 1
→

HashAggregate (cost=120.09..123.61 rows=352 width=46) (actual time=12.226..12.347 rows=51 loops=1)

  • Group Key: snap.rel_definition_id, exec.environment_id
6. 3.332 10.010 ↑ 1.0 964 1
→

Hash Join (cost=64.26..112.86 rows=964 width=21) (actual time=4.817..10.010 rows=964 loops=1)

  • Hash Cond: (exec.rel_snapshot_id = snap.rel_snapshot_id)
7. 1.880 1.880 ↑ 1.0 964 1
→

Seq Scan on pipeline_stage_exec exec (cost=0.00..46.06 rows=964 width=21) (actual time=0.008..1.880 rows=964 loops=1)

  • Filter: ((execution_status)::text = 'SUCCESSFUL'::text)
  • Rows Removed by Filter: 481
8. 2.505 4.798 ↑ 1.0 1,256 1
→

Hash (cost=48.56..48.56 rows=1,256 width=14) (actual time=4.797..4.798 rows=1,256 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 73kB
9. 2.293 2.293 ↑ 1.0 1,256 1
→

Seq Scan on rel_snapshot snap (cost=0.00..48.56 rows=1,256 width=14) (actual time=0.006..2.293 rows=1,256 loops=1)

10. 3.227 22.820 ↓ 8.2 1,445 1
→

Hash (cost=73.26..73.26 rows=177 width=69) (actual time=22.819..22.820 rows=1,445 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 163kB
11. 5.102 19.593 ↓ 8.2 1,445 1
→

Hash Join (cost=16.36..73.26 rows=177 width=69) (actual time=1.515..19.593 rows=1,445 loops=1)

  • Hash Cond: (stage_exec.environment_id = env.environment_id)
12. 5.357 14.396 ↓ 1.9 1,445 1
→

Hash Join (cost=14.89..69.36 rows=767 width=67) (actual time=1.412..14.396 rows=1,445 loops=1)

  • Hash Cond: ((stage_exec.environment_id = ps.environment_id) AND (pd.active_pipeline_version_id = ps.pipeline_version_id))
13. 5.322 7.758 ↑ 1.0 1,445 1
→

Hash Join (cost=1.58..48.45 rows=1,445 width=62) (actual time=0.121..7.758 rows=1,445 loops=1)

  • Hash Cond: (stage_exec.pipeline_definition_id = pd.pipeline_definition_id)
14. 2.330 2.330 ↑ 1.0 1,445 1
→

Seq Scan on pipeline_stage_exec stage_exec (cost=0.00..42.45 rows=1,445 width=62) (actual time=0.005..2.330 rows=1,445 loops=1)

15. 0.053 0.106 ↑ 1.0 26 1
→

Hash (cost=1.26..1.26 rows=26 width=14) (actual time=0.105..0.106 rows=26 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
16. 0.053 0.053 ↑ 1.0 26 1
→

Seq Scan on pipeline_definition pd (cost=0.00..1.26 rows=26 width=14) (actual time=0.006..0.053 rows=26 loops=1)

17. 0.668 1.281 ↑ 1.0 332 1
→

Hash (cost=8.32..8.32 rows=332 width=19) (actual time=1.280..1.281 rows=332 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
18. 0.613 0.613 ↑ 1.0 332 1
→

Seq Scan on pipeline_stage ps (cost=0.00..8.32 rows=332 width=19) (actual time=0.007..0.613 rows=332 loops=1)

19. 0.047 0.095 ↑ 1.0 21 1
→

Hash (cost=1.21..1.21 rows=21 width=16) (actual time=0.093..0.095 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.048 0.048 ↑ 1.0 21 1
→

Seq Scan on environment env (cost=0.00..1.21 rows=21 width=16) (actual time=0.009..0.048 rows=21 loops=1)

21. 0.306 0.306 ↑ 1.0 1 51
→

Index Scan using rel_snapshot_pk on rel_snapshot rs (cost=0.28..0.41 rows=1 width=26) (actual time=0.006..0.006 rows=1 loops=51)

  • Index Cond: (rel_snapshot_id = stage_exec.rel_snapshot_id)
22. 0.204 10.098 ↓ 0.0 0 51
→

Bitmap Heap Scan on rel_snapshot on_deck_rs (cost=74.89..148.84 rows=1 width=26) (actual time=0.198..0.198 rows=0 loops=51)

  • Recheck Cond: (((SubPlan 4) = rel_snapshot_id) OR (rel_snapshot_id IS NULL))
  • Heap Blocks: exact=7
23. 9.639 9.894 ↓ 0.0 0 51
→

BitmapOr (cost=74.89..74.89 rows=1 width=0) (actual time=0.194..0.194 rows=0 loops=51)

24. 0.000 0.102 ↓ 0.0 0 51
→

Bitmap Index Scan on rel_snapshot_pk (cost=0.00..70.29 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=51)

  • Index Cond: ((SubPlan 4) = rel_snapshot_id)
25.          

SubPlan (forBitmap Index Scan)
26. 0.000 0.153 ↓ 0.0 0 51
→

Index Scan using fdk on pipeline_stage_exec pipeline_stage_exec_1 (cost=61.92..69.94 rows=1 width=7) (actual time=0.002..0.003 rows=0 loops=51)

  • Index Cond: (pipeline_stage_exec_id = $9)
27.          

Initplan (forIndex Scan)
28. 0.306 8.874 ↑ 1.0 1 51
→

Aggregate (cost=61.63..61.64 rows=1 width=32) (actual time=0.173..0.174 rows=1 loops=51)

29. 0.429 8.568 ↓ 0.0 0 51
→

Nested Loop (cost=16.17..61.63 rows=1 width=7) (actual time=0.155..0.168 rows=0 loops=51)

30. 5.967 7.905 ↑ 1.0 1 51
→

Bitmap Heap Scan on pipeline_stage_exec min_exec_1 (cost=15.89..52.93 rows=1 width=14) (actual time=0.116..0.155 rows=1 loops=51)

  • Recheck Cond: (pipeline_stage_exec_id > (max(exec.pipeline_stage_exec_id)))
  • Filter: ((environment_id = exec.environment_id) AND ((execution_status)::text = ANY ('{RUNNING_GATES,GATES_COMPLETE,RUNNING_STEPS}'::text[])))
  • Rows Removed by Filter: 470
  • Heap Blocks: exact=1034
31. 1.938 1.938 ↑ 1.0 470 51
→

Bitmap Index Scan on fdk (cost=0.00..15.89 rows=482 width=0) (actual time=0.038..0.038 rows=470 loops=51)

  • Index Cond: (pipeline_stage_exec_id > (max(exec.pipeline_stage_exec_id)))
32. 0.234 0.234 ↓ 0.0 0 39
→

Index Scan using rel_snapshot_pk on rel_snapshot min_snap_1 (cost=0.28..8.30 rows=1 width=7) (actual time=0.006..0.006 rows=0 loops=39)

  • Index Cond: (rel_snapshot_id = min_exec_1.rel_snapshot_id)
  • Filter: (rel_definition_id = snap.rel_definition_id)
  • Rows Removed by Filter: 1
33. 0.153 0.153 ↓ 0.0 0 51
→

Bitmap Index Scan on rel_snapshot_pk (cost=0.00..4.29 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=51)

  • Index Cond: (rel_snapshot_id IS NULL)
34. 0.035 0.035 ↑ 1.0 1 7
→

Index Scan using fdk on pipeline_stage_exec pse (cost=0.28..0.39 rows=1 width=23) (actual time=0.005..0.005 rows=1 loops=7)

  • Index Cond: (pipeline_stage_exec_id = stage_exec.pipeline_stage_exec_id)
35.          

SubPlan (forNested Loop)
36. 0.000 0.049 ↑ 1.0 1 7
→

Index Scan using fdk on pipeline_stage_exec (cost=61.92..69.94 rows=1 width=7) (actual time=0.005..0.007 rows=1 loops=7)

  • Index Cond: (pipeline_stage_exec_id = $4)
37.          

Initplan (forIndex Scan)
38. 0.056 1.834 ↑ 1.0 1 7
→

Aggregate (cost=61.63..61.64 rows=1 width=32) (actual time=0.261..0.262 rows=1 loops=7)

39. 0.130 1.778 ↑ 1.0 1 7
→

Nested Loop (cost=16.17..61.63 rows=1 width=7) (actual time=0.156..0.254 rows=1 loops=7)

40. 1.169 1.540 ↓ 3.0 3 7
→

Bitmap Heap Scan on pipeline_stage_exec min_exec (cost=15.89..52.93 rows=1 width=14) (actual time=0.101..0.220 rows=3 loops=7)

  • Recheck Cond: (pipeline_stage_exec_id > (max(exec.pipeline_stage_exec_id)))
  • Filter: ((environment_id = exec.environment_id) AND ((execution_status)::text = ANY ('{RUNNING_GATES,GATES_COMPLETE,RUNNING_STEPS}'::text[])))
  • Rows Removed by Filter: 695
  • Heap Blocks: exact=174
41. 0.371 0.371 ↓ 1.4 697 7
→

Bitmap Index Scan on fdk (cost=0.00..15.89 rows=482 width=0) (actual time=0.053..0.053 rows=697 loops=7)

  • Index Cond: (pipeline_stage_exec_id > (max(exec.pipeline_stage_exec_id)))
42. 0.108 0.108 ↓ 0.0 0 18
→

Index Scan using rel_snapshot_pk on rel_snapshot min_snap (cost=0.28..8.30 rows=1 width=7) (actual time=0.006..0.006 rows=0 loops=18)

  • Index Cond: (rel_snapshot_id = min_exec.rel_snapshot_id)
  • Filter: (rel_definition_id = snap.rel_definition_id)
  • Rows Removed by Filter: 0