explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zIBm

Settings
# exclusive inclusive rows x rows loops node
1. 89.815 2,576.924 ↓ 0.0 0 1

Hash Join (cost=209,092.47..321,376.08 rows=737 width=4) (actual time=2,576.924..2,576.924 rows=0 loops=1)

  • Hash Cond: (restricted_job_artifacts.id = ci_job_artifacts.id)
  • Buffers: shared hit=73,177
2.          

CTE restricted_job_artifacts

3. 908.315 2,297.734 ↑ 11.0 450,552 1

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

  • Hash Cond: (ci_job_artifacts_1.project_id = projects.id)
  • Buffers: shared hit=73,168
4. 1,362.957 1,362.957 ↓ 1.0 4,963,276 1

Seq Scan on ci_job_artifacts ci_job_artifacts_1 (cost=0.00..113,824.11 rows=4,962,811 width=8) (actual time=0.011..1,362.957 rows=4,963,276 loops=1)

  • Buffers: shared hit=64,196
5. 3.233 26.462 ↑ 250.1 6,162 1

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

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

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

  • Buffers: shared hit=8,972
7. 0.611 7.849 ↓ 3.5 708 1

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

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

  • Buffers: shared hit=4,228
9.          

CTE base_and_descendants

10. 0.418 6.947 ↑ 2.6 708 1

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

  • Buffers: shared hit=4,228
11. 0.040 0.040 ↓ 1.2 5 1

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

  • Filter: (geo_node_id = 100,109)
  • Buffers: shared hit=1
12. 0.117 6.489 ↓ 1.3 234 3

Nested Loop (cost=0.43..86.34 rows=184 width=4) (actual time=0.063..2.163 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.031 rows=236 loops=3)
13. 6.372 6.372 ↑ 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.007..0.009 rows=1 loops=708)

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

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

  • Buffers: shared hit=73,168
16. 0.096 0.246 ↑ 1.9 380 1

Hash (cost=21.30..21.30 rows=737 width=4) (actual time=0.246..0.246 rows=380 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 22kB
  • Buffers: shared hit=9
17. 0.150 0.150 ↑ 1.9 380 1

Index Only Scan using ci_job_artifacts_pkey on ci_job_artifacts (cost=0.43..21.30 rows=737 width=4) (actual time=0.030..0.150 rows=380 loops=1)

  • Index Cond: ((id >= 1) AND (id <= 1,000))
  • Heap Fetches: 5
  • Buffers: shared hit=9
Planning time : 8.958 ms
Execution time : 2,585.790 ms