explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 51LN

Settings
# exclusive inclusive rows x rows loops node
1. 0.747 1,663.654 ↓ 77.0 77 1

Hash Join (cost=32,744.36..299,549.52 rows=1 width=1,483) (actual time=1,182.010..1,663.654 rows=77 loops=1)

  • Hash Cond: ((ci_builds.project_id = deployments_with_stop_action.project_id) AND ((ci_builds.ref)::text = (deployments_with_stop_action.ref)::text) AND ((ci_builds.name)::text = (deployments_with_stop_action.on_stop)::text))
  • Buffers: shared hit=1,067 read=1,053 dirtied=126
  • I/O Timings: read=1,578.681
2.          

CTE deployments_with_stop_action

3. 0.079 779.490 ↑ 15.6 82 1

Unique (cost=26,786.58..26,792.96 rows=1,277 width=138) (actual time=779.350..779.490 rows=82 loops=1)

  • Buffers: shared hit=346 read=535 dirtied=111
  • I/O Timings: read=714.996
4. 1.025 779.411 ↑ 5.0 256 1

Sort (cost=26,786.58..26,789.77 rows=1,277 width=138) (actual time=779.349..779.411 rows=256 loops=1)

  • Sort Key: deployments.environment_id, deployments.id DESC
  • Sort Method: quicksort Memory: 93kB
  • Buffers: shared hit=346 read=535 dirtied=111
  • I/O Timings: read=714.996
5. 0.841 778.386 ↑ 5.0 256 1

Nested Loop (cost=18.26..26,720.70 rows=1,277 width=138) (actual time=176.418..778.386 rows=256 loops=1)

  • Buffers: shared hit=343 read=535 dirtied=111
  • I/O Timings: read=714.996
6. 0.567 173.445 ↑ 1.0 100 1

HashAggregate (cost=17.70..18.70 rows=100 width=4) (actual time=173.238..173.445 rows=100 loops=1)

  • Group Key: environments.id
  • Buffers: shared hit=5 read=119 dirtied=51
  • I/O Timings: read=150.399
7. 0.084 172.878 ↑ 1.0 100 1

Limit (cost=0.43..16.45 rows=100 width=4) (actual time=5.792..172.878 rows=100 loops=1)

  • Buffers: shared hit=5 read=119 dirtied=51
  • I/O Timings: read=150.399
8. 172.794 172.794 ↑ 4,488.3 100 1

Index Scan using index_environments_on_project_id_state_environment_type on public.environments (cost=0.43..71,895.54 rows=448,826 width=4) (actual time=5.790..172.794 rows=100 loops=1)

  • Index Cond: (((environments.state)::text = 'available'::text) AND (environments.environment_type IS NOT NULL))
  • Buffers: shared hit=5 read=119 dirtied=51
  • I/O Timings: read=150.399
9. 604.100 604.100 ↑ 4.3 3 100

Index Scan using index_deployments_on_environment_id_and_status on public.deployments (cost=0.56..266.89 rows=13 width=138) (actual time=3.568..6.041 rows=3 loops=100)

  • Index Cond: ((deployments.environment_id = environments.id) AND (deployments.status = 2))
  • Filter: ((deployments.on_stop IS NOT NULL) AND (deployments.deployable_id IS NOT NULL))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=338 read=416 dirtied=60
  • I/O Timings: read=564.597
10. 1.250 883.260 ↑ 3,165.4 159 1

Nested Loop (cost=5,903.51..27,352.83 rows=503,294 width=1,483) (actual time=402.304..883.260 rows=159 loops=1)

  • Buffers: shared hit=718 read=518 dirtied=15
  • I/O Timings: read=863.685
11. 0.607 390.256 ↑ 15.6 82 1

HashAggregate (cost=5,902.93..5,915.70 rows=1,277 width=4) (actual time=390.038..390.256 rows=82 loops=1)

  • Group Key: ci_builds_1.commit_id
  • Buffers: shared hit=201 read=212 dirtied=10
  • I/O Timings: read=380.335
12. 0.494 389.649 ↑ 15.6 82 1

Nested Loop (cost=0.57..5,899.74 rows=1,277 width=4) (actual time=13.132..389.649 rows=82 loops=1)

  • Buffers: shared hit=201 read=212 dirtied=10
  • I/O Timings: read=380.335
13. 0.147 0.147 ↑ 15.6 82 1

CTE Scan on deployments_with_stop_action deployments_with_stop_action_1 (cost=0.00..25.54 rows=1,277 width=4) (actual time=0.001..0.147 rows=82 loops=1)

14. 389.008 389.008 ↑ 1.0 1 82

Index Scan using ci_builds_pkey on public.ci_builds ci_builds_1 (cost=0.57..4.59 rows=1 width=8) (actual time=4.741..4.744 rows=1 loops=82)

  • Index Cond: (ci_builds_1.id = deployments_with_stop_action_1.deployable_id)
  • Buffers: shared hit=201 read=212 dirtied=10
  • I/O Timings: read=380.335
15. 491.754 491.754 ↑ 5.5 2 82

Index Scan using index_ci_builds_on_commit_id_and_status_and_type on public.ci_builds (cost=0.57..16.68 rows=11 width=1,483) (actual time=4.363..5.997 rows=2 loops=82)

  • Index Cond: ((ci_builds.commit_id = ci_builds_1.commit_id) AND ((ci_builds.status)::text = ANY ('{manual,scheduled}'::text[])) AND ((ci_builds.type)::text = 'Ci::Build'::text))
  • Buffers: shared hit=514 read=306 dirtied=5
  • I/O Timings: read=483.350
16. 0.084 779.647 ↑ 15.6 82 1

Hash (cost=25.54..25.54 rows=1,277 width=68) (actual time=779.647..779.647 rows=82 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 23kB
  • Buffers: shared hit=346 read=535 dirtied=111
  • I/O Timings: read=714.996
17. 779.563 779.563 ↑ 15.6 82 1

CTE Scan on deployments_with_stop_action (cost=0.00..25.54 rows=1,277 width=68) (actual time=779.355..779.563 rows=82 loops=1)

  • Buffers: shared hit=346 read=535 dirtied=111
  • I/O Timings: read=714.996