explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Zv0q

Settings
# exclusive inclusive rows x rows loops node
1. 46.471 1,468.709 ↓ 0.0 0 1

Hash Join (cost=209,115.28..321,398.89 rows=274 width=4) (actual time=1,468.709..1,468.709 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,293.110 1,303.391 ↑ 11.0 450,552 1

Hash Semi Join (cost=20,795.68..209,061.96 rows=4,962,811 width=4) (actual time=22.715..1,303.391 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.008..756.041
  • Buffers: shared hit=64,196
4. 2.051 10.281 ↑ 250.1 6,162 1

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

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

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

  • Buffers: shared hit=8,972
6. 0.359 3.235 ↓ 3.5 708 1

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

  • Group Key: namespaces_1.id
  • Buffers: shared hit=4,228
7. 2.876 2.876 ↑ 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.026..2.876 rows=708 loops=1)

  • Buffers: shared hit=4,228
8.          

CTE base_and_descendants

9. 0.242 2.695 ↑ 2.6 708 1

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

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

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

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

Nested Loop (cost=0.43..86.34 rows=184 width=4) (actual time=0.020..0.811 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.017 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.003..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.248 4.248 ↑ 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.002..0.006 rows=9 loops=708)

  • Index Cond: (namespace_id = namespaces_1.id)
  • Heap Fetches: 1,352
  • Buffers: shared hit=4,744
14. 1,421.887 1,421.887 ↑ 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=22.718..1,421.887 rows=450,552 loops=1)

  • Buffers: shared hit=73,168
15. 0.351 0.351 ↑ 54.8 5 1

Hash (cost=49.90..49.90 rows=274 width=4) (actual time=0.351..0.351 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=144
  • -> Index Scan using ci_job_artifacts_pkey on ci_job_artifacts (cost=0.43..49.90 rows=274 width=4) (actual time=0.017..0.338rows=5 loops=1)
  • Index Cond: ((id >= 1) AND (id <= 1,000))
  • Filter: ((file_store = 1) OR (file_store IS NULL))
  • Rows Removed by Filter: 375
  • Buffers: shared hit=144
Planning time : 1.324 ms
Execution time : 1,475.954 ms