explain.depesz.com

PostgreSQL's explain analyze made readable

Result: P7lw : Karl's slow query

Settings
# exclusive inclusive rows x rows loops node
1. 0.064 22,143.259 ↑ 6.1 7 1

Nested Loop (cost=121.75..3,897,712.64 rows=43 width=62) (actual time=400.819..22,143.259 rows=7 loops=1)

  • Output: deploy_exec.rel_definition_id, stage_exec.environment_id, pse.start_time, pse.end_time, deploy_exec.exec_id
  • Inner Unique: true
2. 0.062 22,143.139 ↑ 6.1 7 1

Nested Loop (cost=121.47..3,897,695.55 rows=43 width=53) (actual time=400.790..22,143.139 rows=7 loops=1)

  • Output: stage_exec.environment_id, stage_exec.pipeline_stage_exec_id, deploy_exec.rel_definition_id, deploy_exec.exec_id
  • Inner Unique: true
3. 21,553.545 22,143.028 ↑ 6.1 7 1

Nested Loop (cost=121.20..3,897,682.15 rows=43 width=60) (actual time=400.771..22,143.028 rows=7 loops=1)

  • Output: stage_exec.environment_id, stage_exec.pipeline_stage_exec_id, stage_exec.rel_snapshot_id, deploy_exec.rel_definition_id, deploy_exec.exec_id
  • 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. 131.128 293.683 ↓ 1.2 51 1

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

  • Output: stage_exec.environment_id, stage_exec.pipeline_stage_exec_id, stage_exec.rel_snapshot_id, deploy_exec.rel_definition_id, deploy_exec.exec_id, deploy_exec.environment_id
  • Join Filter: (stage_exec.pipeline_stage_exec_id = deploy_exec.exec_id)
  • Rows Removed by Join Filter: 73644
5. 8.560 46.955 ↓ 8.2 1,445 1

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

  • Output: stage_exec.environment_id, stage_exec.pipeline_stage_exec_id, stage_exec.rel_snapshot_id
  • Inner Unique: true
6. 7.966 34.060 ↓ 1.9 1,445 1

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

  • Output: stage_exec.environment_id, stage_exec.pipeline_stage_exec_id, stage_exec.rel_snapshot_id, ps.environment_id
  • Inner Unique: true
7. 8.830 18.869 ↑ 1.0 1,445 1

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

  • Output: stage_exec.environment_id, stage_exec.pipeline_stage_exec_id, stage_exec.rel_snapshot_id, pd.active_pipeline_version_id
  • Inner Unique: true
8. 4.259 4.259 ↑ 1.0 1,445 1

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

  • Output: stage_exec.pipeline_stage_exec_id, stage_exec.rel_snapshot_id, stage_exec.pipeline_definition_id, stage_exec.environment_id, stage_exec.execution_status, stage_exec.created_on, stage_exec.created_by, stage_exec.updated_on, stage_exec.updated_by, stage_exec.version_number, stage_exec.pipeline_exec_id, stage_exec.action_type, stage_exec.action_by, stage_exec.action_on, stage_exec.start_time, stage_exec.end_time
9. 5.780 5.780 ↑ 1.0 1 1,445

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

  • Output: pd.pipeline_definition_id, pd.name, pd.description, pd.active_pipeline_version_id, pd.is_active, pd.created_on, pd.created_by, pd.updated_on, pd.updated_by, pd.version_number
  • Index Cond: (pd.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 fd.pipeline_stage ps (cost=0.27..0.91 rows=1 width=14) (actual time=0.005..0.005 rows=1 loops=1,445)

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

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

  • Output: env.environment_id
  • Index Cond: (env.environment_id = stage_exec.environment_id)
  • Heap Fetches: 0
12. 103.204 115.600 ↑ 6.9 51 1,445

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

  • Output: deploy_exec.rel_definition_id, deploy_exec.exec_id, deploy_exec.environment_id
13. 0.140 12.396 ↑ 6.9 51 1

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

  • Output: deploy_exec.rel_definition_id, deploy_exec.exec_id, deploy_exec.environment_id
14. 2.266 12.256 ↑ 6.9 51 1

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

  • Output: snap.rel_definition_id, exec.environment_id, max(exec.pipeline_stage_exec_id)
  • Group Key: snap.rel_definition_id, exec.environment_id
15. 3.257 9.990 ↑ 1.0 964 1

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

  • Output: snap.rel_definition_id, exec.environment_id, exec.pipeline_stage_exec_id
  • Inner Unique: true
  • Hash Cond: (exec.rel_snapshot_id = snap.rel_snapshot_id)
16. 1.960 1.960 ↑ 1.0 964 1

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

  • Output: exec.pipeline_stage_exec_id, exec.rel_snapshot_id, exec.pipeline_definition_id, exec.environment_id, exec.execution_status, exec.created_on, exec.created_by, exec.updated_on, exec.updated_by, exec.version_number, exec.pipeline_exec_id, exec.action_type, exec.action_by, exec.action_on, exec.start_time, exec.end_time
  • Filter: ((exec.execution_status)::text = 'SUCCESSFUL'::text)
  • Rows Removed by Filter: 481
17. 2.485 4.773 ↑ 1.0 1,256 1

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

  • Output: snap.rel_definition_id, snap.rel_snapshot_id
  • Buckets: 2048 Batches: 1 Memory Usage: 73kB
18. 2.288 2.288 ↑ 1.0 1,256 1

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

  • Output: snap.rel_definition_id, snap.rel_snapshot_id
19. 101.183 103.632 ↑ 1.0 1,256 51

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

  • Output: on_deck_rs.rel_snapshot_id
20. 2.449 2.449 ↑ 1.0 1,256 1

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

  • Output: on_deck_rs.rel_snapshot_id
  • Heap Fetches: 0
21.          

SubPlan (forNested Loop)

22. 0.000 192.168 ↓ 0.0 0 64,056

Index Scan using pipeline_stage_execution_pk on fd.pipeline_stage_exec (cost=64.08..72.10 rows=1 width=7) (actual time=0.002..0.003 rows=0 loops=64,056)

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

Initplan (forIndex Scan)

24. 384.336 21,010.368 ↑ 1.0 1 64,056

Aggregate (cost=63.79..63.80 rows=1 width=32) (actual time=0.326..0.328 rows=1 loops=64,056)

  • Output: max(min_exec.pipeline_stage_exec_id)
25. 538.824 20,626.032 ↓ 0.0 0 64,056

Nested Loop (cost=0.28..63.79 rows=1 width=7) (actual time=0.301..0.322 rows=0 loops=64,056)

  • Output: min_exec.pipeline_stage_exec_id
  • Inner Unique: true
26. 19,793.304 19,793.304 ↑ 1.0 1 64,056

Seq Scan on fd.pipeline_stage_exec min_exec (cost=0.00..55.09 rows=1 width=14) (actual time=0.247..0.309 rows=1 loops=64,056)

  • Output: min_exec.pipeline_stage_exec_id, min_exec.rel_snapshot_id, min_exec.pipeline_definition_id, min_exec.environment_id, min_exec.execution_status, min_exec.created_on, min_exec.created_by, min_exec.updated_on, min_exec.updated_by, min_exec.version_number, min_exec.pipeline_exec_id, min_exec.action_type, min_exec.action_by, min_exec.action_on, min_exec.start_time, min_exec.end_time
  • Filter: ((min_exec.pipeline_stage_exec_id > deploy_exec.exec_id) AND (min_exec.environment_id = deploy_exec.environment_id) AND ((min_exec.execution_status)::text = ANY ('{RUNNING_GATES,GATES_COMPLETE,RUNNING_STEPS}'::text[])))
  • Rows Removed by Filter: 1444
27. 293.904 293.904 ↓ 0.0 0 48,984

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

  • Output: min_snap.rel_snapshot_id, min_snap.rel_definition_id, min_snap.description, min_snap.rel_snapshot, min_snap.rel_snapshot_status, min_snap.rel_definition_hash, min_snap.rel_snapshot_hash, min_snap.duplicate_files_flag, min_snap.snapshot_version_count, min_snap.created_on, min_snap.created_by, min_snap.updated_on, min_snap.updated_by, min_snap.version_number
  • Index Cond: (min_snap.rel_snapshot_id = min_exec.rel_snapshot_id)
  • Filter: (min_snap.rel_definition_id = deploy_exec.rel_definition_id)
  • Rows Removed by Filter: 1
28. 0.049 0.049 ↑ 1.0 1 7

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

  • Output: rs.rel_snapshot_id
  • Index Cond: (rs.rel_snapshot_id = stage_exec.rel_snapshot_id)
  • Heap Fetches: 0
29. 0.056 0.056 ↑ 1.0 1 7

Index Scan using pipeline_stage_execution_pk on fd.pipeline_stage_exec pse (cost=0.28..0.40 rows=1 width=23) (actual time=0.008..0.008 rows=1 loops=7)

  • Output: pse.pipeline_stage_exec_id, pse.rel_snapshot_id, pse.pipeline_definition_id, pse.environment_id, pse.execution_status, pse.created_on, pse.created_by, pse.updated_on, pse.updated_by, pse.version_number, pse.pipeline_exec_id, pse.action_type, pse.action_by, pse.action_on, pse.start_time, pse.end_time
  • Index Cond: (pse.pipeline_stage_exec_id = stage_exec.pipeline_stage_exec_id)