explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xDH : Karl 2

Settings
# exclusive inclusive rows x rows loops node
1. 0.067 12,379.433 ↑ 6.1 7 1
→

Nested Loop (cost=121.75..3,780,865.98 rows=43 width=62) (actual time=402.781..12,379.433 rows=7 loops=1)

2. 0.058 12,379.324 ↑ 6.1 7 1
→

Nested Loop (cost=121.47..3,780,849.24 rows=43 width=53) (actual time=402.765..12,379.324 rows=7 loops=1)

3. 11,789.251 12,379.217 ↑ 6.1 7 1
→

Nested Loop (cost=121.20..3,780,835.84 rows=43 width=60) (actual time=402.747..12,379.217 rows=7 loops=1)

  • Join Filter: (((SubPlan 2) = on_deck_rs.rel_snapshot_id) OR (on_deck_rs.rel_snapshot_id IS NULL))
  • Rows Removed by Join Filter: 64049
4. 132.634 296.002 ↓ 1.2 51 1
→

Nested Loop (cost=120.92..2,862.48 rows=43 width=67) (actual time=76.880..296.002 rows=51 loops=1)

  • Join Filter: (stage_exec.pipeline_stage_exec_id = deploy_exec.exec_id)
  • Rows Removed by Join Filter: 73644
5. 7.237 46.323 ↓ 8.2 1,445 1
→

Nested Loop (cost=0.82..1,799.90 rows=177 width=21) (actual time=0.055..46.323 rows=1,445 loops=1)

6. 7.773 33.306 ↓ 1.9 1,445 1
→

Nested Loop (cost=0.69..1,677.51 rows=767 width=28) (actual time=0.043..33.306 rows=1,445 loops=1)

7. 8.413 18.308 ↑ 1.0 1,445 1
→

Nested Loop (cost=0.42..366.88 rows=1,445 width=28) (actual time=0.026..18.308 rows=1,445 loops=1)

8. 4.115 4.115 ↑ 1.0 1,445 1
→

Index Scan using fdix115 on pipeline_stage_exec stage_exec (cost=0.28..132.20 rows=1,445 width=28) (actual time=0.011..4.115 rows=1,445 loops=1)

9. 5.780 5.780 ↑ 1.0 1 1,445
→

Index Scan using pipeline_definition_pk on pipeline_definition pd (cost=0.14..0.16 rows=1 width=14) (actual time=0.004..0.004 rows=1 loops=1,445)

  • Index Cond: (pipeline_definition_id = stage_exec.pipeline_definition_id)
10. 7.225 7.225 ↑ 1.0 1 1,445
→

Index Only Scan using pipeline_stage_uk1 on pipeline_stage ps (cost=0.27..0.91 rows=1 width=14) (actual time=0.005..0.005 rows=1 loops=1,445)

  • Index Cond: ((pipeline_version_id = pd.active_pipeline_version_id) AND (environment_id = stage_exec.environment_id))
  • Heap Fetches: 0
11. 5.780 5.780 ↑ 1.0 1 1,445
→

Index Only Scan using environment_pk on environment env (cost=0.14..0.16 rows=1 width=7) (actual time=0.004..0.004 rows=1 loops=1,445)

  • Index Cond: (environment_id = stage_exec.environment_id)
  • Heap Fetches: 0
12. 104.194 117.045 ↑ 6.9 51 1,445
→

Materialize (cost=120.09..128.89 rows=352 width=46) (actual time=0.010..0.081 rows=51 loops=1,445)

13. 0.157 12.851 ↑ 6.9 51 1
→

Subquery Scan on deploy_exec (cost=120.09..127.13 rows=352 width=46) (actual time=12.533..12.851 rows=51 loops=1)

14. 2.340 12.694 ↑ 6.9 51 1
→

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

  • Group Key: snap.rel_definition_id, exec.environment_id
15. 3.386 10.354 ↑ 1.0 964 1
→

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

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

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

  • Filter: ((execution_status)::text = 'SUCCESSFUL'::text)
  • Rows Removed by Filter: 481
17. 2.479 4.878 ↑ 1.0 1,256 1
→

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

  • Buckets: 2048 Batches: 1 Memory Usage: 73kB
18. 2.399 2.399 ↑ 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.399 rows=1,256 loops=1)

19. 99.334 101.796 ↑ 1.0 1,256 51
→

Materialize (cost=0.28..49.40 rows=1,256 width=7) (actual time=0.002..1.996 rows=1,256 loops=51)

20. 2.462 2.462 ↑ 1.0 1,256 1
→

Index Only Scan using rel_snapshot_pk on rel_snapshot on_deck_rs (cost=0.28..43.12 rows=1,256 width=7) (actual time=0.014..2.462 rows=1,256 loops=1)

  • Heap Fetches: 0
21.          

SubPlan (forNested Loop)
22. 0.000 192.168 ↓ 0.0 0 64,056
→

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

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

Initplan (forIndex Scan)
24. 320.280 11,209.800 ↑ 1.0 1 64,056
→

Aggregate (cost=61.63..61.64 rows=1 width=32) (actual time=0.174..0.175 rows=1 loops=64,056)

25. 602.880 10,889.520 ↓ 0.0 0 64,056
→

Nested Loop (cost=16.17..61.63 rows=1 width=7) (actual time=0.156..0.170 rows=0 loops=64,056)

26. 7,558.608 9,992.736 ↑ 1.0 1 64,056
→

Bitmap Heap Scan on pipeline_stage_exec min_exec (cost=15.89..52.93 rows=1 width=14) (actual time=0.117..0.156 rows=1 loops=64,056)

  • Recheck Cond: (pipeline_stage_exec_id > deploy_exec.exec_id)
  • Filter: ((environment_id = deploy_exec.environment_id) AND ((execution_status)::text = ANY ('{RUNNING_GATES,GATES_COMPLETE,RUNNING_STEPS}'::text[])))
  • Rows Removed by Filter: 470
  • Heap Blocks: exact=1298704
27. 2,434.128 2,434.128 ↑ 1.0 470 64,056
→

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

  • Index Cond: (pipeline_stage_exec_id > deploy_exec.exec_id)
28. 293.904 293.904 ↓ 0.0 0 48,984
→

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=48,984)

  • Index Cond: (rel_snapshot_id = min_exec.rel_snapshot_id)
  • Filter: (rel_definition_id = deploy_exec.rel_definition_id)
  • Rows Removed by Filter: 1
29. 0.049 0.049 ↑ 1.0 1 7
→

Index Only Scan using rel_snapshot_pk on rel_snapshot rs (cost=0.28..0.31 rows=1 width=7) (actual time=0.007..0.007 rows=1 loops=7)

  • Index Cond: (rel_snapshot_id = stage_exec.rel_snapshot_id)
  • Heap Fetches: 0
30. 0.042 0.042 ↑ 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.006..0.006 rows=1 loops=7)

  • Index Cond: (pipeline_stage_exec_id = stage_exec.pipeline_stage_exec_id)