explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zPET

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 4.485 ↓ 0.0 0 1

Hash Join (cost=37,269,924.66..48,997,748.72 rows=1 width=4) (actual time=4.485..4.485 rows=0 loops=1)

  • Hash Cond: (restricted_job_artifacts.id = ci_job_artifacts.id)
  • Buffers: shared hit=10 read=1
  • I/O Timings: read=4.239
2.          

CTE restricted_job_artifacts

3. 0.006 4.483 ↓ 0.0 0 1

Merge Semi Join (cost=976,263.98..37,269,882.79 rows=518,356,864 width=4) (actual time=4.483..4.483 rows=0 loops=1)

  • Merge Cond: (ci_job_artifacts_1.project_id = projects.id)
  • Buffers: shared hit=10 read=1
  • I/O Timings: read=4.239
4. 4.271 4.271 ↑ 518,356,864.0 1 1

Index Scan using index_ci_job_artifacts_on_project_id on public.ci_job_artifacts ci_job_artifacts_1 (cost=0.57..32,371,214.73 rows=518,356,864 width=8) (actual time=4.271..4.271 rows=1 loops=1)

  • Buffers: shared hit=4 read=1
  • I/O Timings: read=4.239
5. 0.188 0.206 ↓ 0.0 0 1

Sort (cost=976,253.01..993,628.19 rows=6,950,074 width=4) (actual time=0.206..0.206 rows=0 loops=1)

  • Sort Key: projects.id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=6
6. 0.001 0.018 ↓ 0.0 0 1

Nested Loop (cost=2,582.29..3,170.10 rows=6,950,074 width=4) (actual time=0.018..0.018 rows=0 loops=1)

  • Buffers: shared hit=1
7. 0.004 0.017 ↓ 0.0 0 1

HashAggregate (cost=2,581.85..2,583.85 rows=200 width=4) (actual time=0.017..0.017 rows=0 loops=1)

  • Group Key: namespaces_1.id
  • Buffers: shared hit=1
8. 0.013 0.013 ↓ 0.0 0 1

CTE Scan on base_and_descendants namespaces_1 (cost=2,411.97..2,516.51 rows=5,227 width=4) (actual time=0.013..0.013 rows=0 loops=1)

  • Buffers: shared hit=1
9.          

CTE base_and_descendants

10. 0.001 0.012 ↓ 0.0 0 1

Recursive Union (cost=0.15..2,411.97 rows=5,227 width=4) (actual time=0.012..0.012 rows=0 loops=1)

  • Buffers: shared hit=1
11. 0.008 0.008 ↓ 0.0 0 1

Index Only Scan using index_geo_node_namespace_links_on_geo_node_id_and_namespace_id on public.geo_node_namespace_links (cost=0.15..10.78 rows=7 width=4) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: (geo_node_namespace_links.geo_node_id = 100,109)
  • Heap Fetches: 0
  • Buffers: shared hit=1
12. 0.001 0.003 ↓ 0.0 0 1

Nested Loop (cost=0.56..229.67 rows=522 width=4) (actual time=0.003..0.003 rows=0 loops=1)

13. 0.002 0.002 ↓ 0.0 0 1

WorkTable Scan on base_and_descendants (cost=0.00..1.40 rows=70 width=4) (actual time=0.002..0.002 rows=0 loops=1)

14. 0.000 0.000 ↓ 0.0 0 0

Index Only Scan using index_namespaces_on_parent_id_and_id on public.namespaces (cost=0.56..3.19 rows=7 width=8) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: (namespaces.parent_id = base_and_descendants.id)
  • Heap Fetches: 0
15. 0.000 0.000 ↓ 0.0 0 0

Index Only Scan using index_projects_on_namespace_id_and_id on public.projects (cost=0.43..2.76 rows=17 width=8) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: (projects.namespace_id = namespaces_1.id)
  • Heap Fetches: 0
16. 4.484 4.484 ↓ 0.0 0 1

CTE Scan on restricted_job_artifacts (cost=0.00..10,367,137.28 rows=518,356,864 width=4) (actual time=4.484..4.484 rows=0 loops=1)

  • Buffers: shared hit=10 read=1
  • I/O Timings: read=4.239
17. 0.000 0.000 ↓ 0.0 0 0

Hash (cost=41.86..41.86 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)

18. 0.000 0.000 ↓ 0.0 0 0

Index Scan using ci_job_artifacts_pkey on public.ci_job_artifacts (cost=0.57..41.86 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=0)

  • Index Cond: ((ci_job_artifacts.id >= 1) AND (ci_job_artifacts.id <= 1,000))
  • Filter: (((ci_job_artifacts.file_store = 1) OR (ci_job_artifacts.file_store IS NULL)) AND ((ci_job_artifacts.expire_at IS NULL) OR (ci_job_artifacts.expire_at > '2020-06-22 22:59:27.497704+00'::timestamp with time zone)))