explain.depesz.com

PostgreSQL's explain analyze made readable

Result: C01 : Optimization for: LOVVO x; plan #zPC7 - select less columns - which gives less rows?

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.057 12,424.610 ↑ 6.1 7 1

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

2. 0.059 12,424.511 ↑ 6.1 7 1

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

3. 11,833.539 12,424.403 ↑ 6.1 7 1

Nested Loop (cost=121.20..3,780,835.84 rows=43 width=60) (actual time=390.452..12,424.403 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. 133.280 299.909 ↓ 1.2 51 1

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

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

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

6. 7.728 35.086 ↓ 1.9 1,445 1

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

7. 8.847 20.133 ↑ 1.0 1,445 1

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

8. 4.061 4.061 ↑ 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.010..4.061 rows=1,445 loops=1)

9. 7.225 7.225 ↑ 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.005..0.005 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. 105.732 118.490 ↑ 6.9 51 1,445

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

13. 0.146 12.758 ↑ 6.9 51 1

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

14. 2.394 12.612 ↑ 6.9 51 1

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

  • Group Key: snap.rel_definition_id, exec.environment_id
15. 3.476 10.218 ↑ 1.0 964 1

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

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

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

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

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

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

19. 96.370 98.787 ↑ 1.0 1,256 51

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

20. 2.417 2.417 ↑ 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.028..2.417 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,273.856 ↑ 1.0 1 64,056

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

25. 602.880 10,953.576 ↓ 0.0 0 64,056

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

26. 7,622.664 10,056.792 ↑ 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.118..0.157 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.005..0.006 rows=1 loops=7)

  • Index Cond: (pipeline_stage_exec_id = stage_exec.pipeline_stage_exec_id)