explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UpKg

Settings
# exclusive inclusive rows x rows loops node
1. 66.351 1,676.667 ↓ 0.0 0 1

Hash Join (cost=209,121.06..321,404.67 rows=736 width=4) (actual time=1,676.667..1,676.667 rows=0 loops=1)

  • Hash Cond: (restricted_job_artifacts.id = ci_job_artifacts.id)
  • Buffers: shared hit=73,312
  • rows=4,963,276 loops=1)
2.          

CTE restricted_job_artifacts

3. 1,451.504 1,463.854 ↑ 11.0 450,552 1

Hash Semi Join (cost=20,795.68..209,061.96 rows=4,962,811 width=4) (actual time=25.874..1,463.854 rows=450,552 loops=1)

  • Hash Cond: (ci_job_artifacts_1.project_id = projects.id)
  • Buffers: shared hit=73,168
  • -> Seq Scan on ci_job_artifacts ci_job_artifacts_1 (cost=0.00..113824.11 rows=4,962,811 width=8) (actual time=0.011..831.282
  • Buffers: shared hit=64,196
4. 2.638 12.350 ↑ 250.1 6,162 1

Hash (cost=1,530.63..1,530.63 rows=1,541,204 width=4) (actual time=12.350..12.350 rows=6,162 loops=1)

  • Buckets: 2,097,152 Batches: 1 Memory Usage: 16,601kB
  • Buffers: shared hit=8,972
5. 1.577 9.712 ↑ 250.1 6,162 1

Nested Loop (cost=961.64..1,530.63 rows=1,541,204 width=4) (actual time=2.995..9.712 rows=6,162 loops=1)

  • Buffers: shared hit=8,972
6. 0.399 3.179 ↓ 3.5 708 1

HashAggregate (cost=961.21..963.21 rows=200 width=4) (actual time=2.976..3.179 rows=708 loops=1)

  • Group Key: namespaces_1.id
  • Buffers: shared hit=4,228
7. 2.780 2.780 ↑ 2.6 708 1

CTE Scan on base_and_descendants namespaces_1 (cost=901.28..938.16 rows=1,844 width=4) (actual time=0.024..2.780 rows=708 loops=1)

  • Buffers: shared hit=4,228
8.          

CTE base_and_descendants

9. 0.243 2.602 ↑ 2.6 708 1

Recursive Union (cost=0.00..901.28 rows=1,844 width=4) (actual time=0.022..2.602 rows=708 loops=1)

  • Buffers: shared hit=4,228
10. 0.019 0.019 ↓ 1.2 5 1

Seq Scan on geo_node_namespace_links (cost=0.00..1.05 rows=4 width=4) (actual time=0.018..0.019 rows=5 loops=1)

  • Filter: (geo_node_id = 100,109)
  • Buffers: shared hit=1
11. 0.216 2.340 ↓ 1.3 234 3

Nested Loop (cost=0.43..86.34 rows=184 width=4) (actual time=0.019..0.780 rows=234 loops=3)

  • Buffers: shared hit=4,227
  • -> WorkTable Scan on base_and_descendants (cost=0.00..0.80 rows=40 width=4) (actualtime=0.001..0.016 rows=236 loops=3)
12. 2.124 2.124 ↑ 5.0 1 708

Index Only Scan using index_namespaces_on_parent_id_and_id on namespaces (cost=0.43..2.09 rows=5 width=8) (actual time=0.002..0.003 rows=1 loops=708)

  • Index Cond: (parent_id = base_and_descendants.id)
  • Heap Fetches: 2,510
  • Buffers: shared hit=4,227
13. 4.956 4.956 ↑ 1.0 9 708

Index Only Scan using index_projects_on_namespace_id_and_id on projects (cost=0.43..2.75 rows=9 width=8) (actual time=0.003..0.007 rows=9 loops=708)

  • Index Cond: (namespace_id = namespaces_1.id)
  • Heap Fetches: 1,352
  • Buffers: shared hit=4,744
14. 1,609.814 1,609.814 ↑ 11.0 450,552 1

CTE Scan on restricted_job_artifacts (cost=0.00..99,256.22 rows=4,962,811 width=4) (actual time=25.875..1,609.814 rows=450,552 loops=1)

  • Buffers: shared hit=73,168
15. 0.502 0.502 ↑ 1.9 380 1

Hash (cost=49.90..49.90 rows=736 width=4) (actual time=0.502..0.502 rows=380 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 22kB
  • Buffers: shared hit=144
  • -> Index Scan using ci_job_artifacts_pkey on ci_job_artifacts (cost=0.43..49.90 rows=736 width=4) (actual time=0.021..0.384rows=380 loops=1)
  • Index Cond: ((id >= 1) AND (id <= 1,000))
  • Filter: ((expire_at IS NULL) OR (expire_at > '2020-06-22 23:14:32.74825+00'::timestamp with time zone))
  • Buffers: shared hit=144
Planning time : 1.861 ms
Execution time : 1,683.724 ms