explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Rlko

Settings
# exclusive inclusive rows x rows loops node
1. 46.748 1,486.017 ↓ 0.0 0 1

Hash Join (cost=209,117.13..321,400.74 rows=274 width=4) (actual time=1,486.017..1,486.017 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,308.870 1,323.246 ↑ 11.0 450,552 1

Hash Semi Join (cost=20,795.68..209,061.96 rows=4,962,811 width=4) (actual time=31.886..1,323.246 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.010..758.750
  • Buffers: shared hit=64,196
4. 2.616 14.376 ↑ 250.1 6,162 1

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

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

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

  • Buffers: shared hit=8,972
6. 0.527 4.902 ↓ 3.5 708 1

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

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

  • Buffers: shared hit=4,228
8.          

CTE base_and_descendants

9. 0.383 4.107 ↑ 2.6 708 1

Recursive Union (cost=0.00..901.28 rows=1,844 width=4) (actual time=0.022..4.107 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.017..0.019 rows=5 loops=1)

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

Nested Loop (cost=0.43..86.34 rows=184 width=4) (actual time=0.024..1.235 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.035 rows=236 loops=3)
12. 3.540 3.540 ↑ 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.004..0.005 rows=1 loops=708)

  • Index Cond: (parent_id = base_and_descendants.id)
  • Heap Fetches: 2,510
  • Buffers: shared hit=4,227
13. 5.664 5.664 ↑ 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.008 rows=9 loops=708)

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

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

Hash (cost=51.74..51.74 rows=274 width=4) (actual time=0.372..0.372 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..51.74 rows=274 width=4) (actual time=0.021..0.358rows=5 loops=1)
  • Index Cond: ((id >= 1) AND (id <= 1,000))
  • Filter: (((file_store = 1) OR (file_store IS NULL)) AND ((expire_at IS NULL) OR (expire_at > '2020-06-22 22:59:27.497704+00'::timestamp with time zone)))
  • Rows Removed by Filter: 375
  • Buffers: shared hit=144
Planning time : 1.836 ms
Execution time : 1,489.636 ms