explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NICZ

Settings
# exclusive inclusive rows x rows loops node
1. 0.131 18.644 ↓ 69.0 69 1

Hash Join (cost=24,981.25..285,835.54 rows=1 width=1,544) (actual time=13.944..18.644 rows=69 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.031 9.808 ↑ 17.6 72 1

Unique (cost=20,329.79..20,336.13 rows=1,269 width=138) (actual time=9.767..9.808 rows=72 loops=1)

4. 0.168 9.777 ↑ 8.5 150 1

Sort (cost=20,329.79..20,332.96 rows=1,269 width=138) (actual time=9.767..9.777 rows=150 loops=1)

  • Sort Key: deployments.environment_id, deployments.id DESC
  • Sort Method: quicksort Memory: 64kB
5. 0.024 9.609 ↑ 8.5 150 1

Nested Loop (cost=15.42..20,264.38 rows=1,269 width=138) (actual time=2.641..9.609 rows=150 loops=1)

6. 0.056 2.585 ↑ 1.0 100 1

HashAggregate (cost=14.85..15.85 rows=100 width=4) (actual time=2.562..2.585 rows=100 loops=1)

  • Group Key: environments.id
7. 0.011 2.529 ↑ 1.0 100 1

Limit (cost=0.43..13.60 rows=100 width=4) (actual time=0.078..2.529 rows=100 loops=1)

8. 2.518 2.518 ↑ 4,499.7 100 1

Index Scan using index_environments_on_project_id_state_environment_type on environments (cost=0.43..59,279.33 rows=449,974 width=4) (actual time=0.078..2.518 rows=100 loops=1)

  • Index Cond: (((state)::text = 'available'::text) AND (environment_type IS NOT NULL))
9. 7.000 7.000 ↑ 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.053..0.070 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.159 8.638 ↑ 3,632.3 141 1

Nested Loop (cost=4,597.52..17,695.86 rows=512,157 width=1,544) (actual time=4.027..8.638 rows=141 loops=1)

11. 0.066 3.943 ↑ 17.6 72 1

HashAggregate (cost=4,596.95..4,609.64 rows=1,269 width=4) (actual time=3.916..3.943 rows=72 loops=1)

  • Group Key: ci_builds_1.commit_id
12. 0.041 3.877 ↑ 17.6 72 1

Nested Loop (cost=0.57..4,593.78 rows=1,269 width=4) (actual time=0.182..3.877 rows=72 loops=1)

13. 0.020 0.020 ↑ 17.6 72 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.020 rows=72 loops=1)

14. 3.816 3.816 ↑ 1.0 1 72

Index Scan using ci_builds_pkey on ci_builds ci_builds_1 (cost=0.57..3.59 rows=1 width=8) (actual time=0.050..0.053 rows=1 loops=72)

  • Index Cond: (id = deployments_with_stop_action_1.deployable_id)
15. 4.536 4.536 ↑ 5.5 2 72

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,544) (actual time=0.045..0.063 rows=2 loops=72)

  • Index Cond: ((commit_id = ci_builds_1.commit_id) AND ((status)::text = ANY ('{manual,scheduled}'::text[])) AND ((type)::text = 'Ci::Build'::text))
16. 0.033 9.875 ↑ 17.6 72 1

Hash (cost=25.38..25.38 rows=1,269 width=68) (actual time=9.875..9.875 rows=72 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 22kB
17. 9.842 9.842 ↑ 17.6 72 1

CTE Scan on deployments_with_stop_action (cost=0.00..25.38 rows=1,269 width=68) (actual time=9.770..9.842 rows=72 loops=1)

Planning time : 19.017 ms
Execution time : 18.883 ms