explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ze6T

Settings
# exclusive inclusive rows x rows loops node
1. 0.132 5.324 ↓ 71.0 71 1

Hash Join (cost=24,981.23..280,230.36 rows=1 width=1,482) (actual time=3.322..5.324 rows=71 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))
2.          

CTE deployments_with_stop_action

3. 0.028 2.005 ↑ 16.7 76 1

Unique (cost=20,329.77..20,336.12 rows=1,269 width=138) (actual time=1.964..2.005 rows=76 loops=1)

4. 0.164 1.977 ↑ 7.6 168 1

Sort (cost=20,329.77..20,332.94 rows=1,269 width=138) (actual time=1.964..1.977 rows=168 loops=1)

  • Sort Key: deployments.environment_id, deployments.id DESC
  • Sort Method: quicksort Memory: 69kB
5. 0.024 1.813 ↑ 7.6 168 1

Nested Loop (cost=15.40..20,264.36 rows=1,269 width=138) (actual time=0.406..1.813 rows=168 loops=1)

6. 0.039 0.389 ↑ 1.0 100 1

HashAggregate (cost=14.83..15.83 rows=100 width=4) (actual time=0.379..0.389 rows=100 loops=1)

  • Group Key: environments.id
7. 0.008 0.350 ↑ 1.0 100 1

Limit (cost=0.43..13.58 rows=100 width=4) (actual time=0.019..0.350 rows=100 loops=1)

8. 0.342 0.342 ↑ 4,509.4 100 1

Index Scan using index_environments_on_project_id_state_environment_type on environments (cost=0.43..59,322.74 rows=450,941 width=4) (actual time=0.018..0.342 rows=100 loops=1)

  • Index Cond: (((state)::text = 'available'::text) AND (environment_type IS NOT NULL))
9. 1.400 1.400 ↑ 6.5 2 100

Index Scan using index_deployments_on_environment_id_and_status on deployments (cost=0.56..202.36 rows=13 width=138) (actual time=0.010..0.014 rows=2 loops=100)

  • Index Cond: ((environment_id = environments.id) AND (status = 2))
  • Filter: ((on_stop IS NOT NULL) AND (deployable_id IS NOT NULL))
  • Rows Removed by Filter: 1
10. 0.110 3.119 ↑ 3,405.2 147 1

Nested Loop (cost=4,597.52..17,699.29 rows=500,563 width=1,482) (actual time=1.216..3.119 rows=147 loops=1)

11. 0.053 1.185 ↑ 16.7 76 1

HashAggregate (cost=4,596.95..4,609.64 rows=1,269 width=4) (actual time=1.155..1.185 rows=76 loops=1)

  • Group Key: ci_builds_1.commit_id
12. 0.053 1.132 ↑ 16.7 76 1

Nested Loop (cost=0.57..4,593.78 rows=1,269 width=4) (actual time=0.019..1.132 rows=76 loops=1)

13. 0.015 0.015 ↑ 16.7 76 1

CTE Scan on deployments_with_stop_action deployments_with_stop_action_1 (cost=0.00..25.38 rows=1,269 width=4) (actual time=0.001..0.015 rows=76 loops=1)

14. 1.064 1.064 ↑ 1.0 1 76

Index Scan using ci_builds_pkey on ci_builds ci_builds_1 (cost=0.57..3.59 rows=1 width=8) (actual time=0.011..0.014 rows=1 loops=76)

  • Index Cond: (id = deployments_with_stop_action_1.deployable_id)
15. 1.824 1.824 ↑ 5.5 2 76

Index Scan using index_ci_builds_on_commit_id_and_status_and_type on ci_builds (cost=0.57..10.20 rows=11 width=1,482) (actual time=0.015..0.024 rows=2 loops=76)

  • Index Cond: ((commit_id = ci_builds_1.commit_id) AND ((status)::text = ANY ('{manual,scheduled}'::text[])) AND ((type)::text = 'Ci::Build'::text))
16. 0.030 2.073 ↑ 16.7 76 1

Hash (cost=25.38..25.38 rows=1,269 width=68) (actual time=2.072..2.073 rows=76 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 22kB
17. 2.043 2.043 ↑ 16.7 76 1

CTE Scan on deployments_with_stop_action (cost=0.00..25.38 rows=1,269 width=68) (actual time=1.967..2.043 rows=76 loops=1)

Planning time : 9.935 ms
Execution time : 5.550 ms