explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FVrj

Settings
# exclusive inclusive rows x rows loops node
1. 3.902 441.449 ↓ 3.9 5,000 1

Nested Loop (cost=3,465.32..6,364.50 rows=1,280 width=299) (actual time=2.732..441.449 rows=5,000 loops=1)

  • Output: project_registry.id, project_registry.project_id, project_registry.last_repository_synced_at, project_registry.last_repository_successful_sync_at, project_registry.created_at, pro
  • Buffers: shared hit=20034
2. 15.181 427.547 ↓ 3.9 5,000 1

Hash Join (cost=3,464.90..3,586.90 rows=1,280 width=4) (actual time=2.698..427.547 rows=5,000 loops=1)

  • Output: projects.id
  • Hash Cond: (projects.namespace_id = gitlab_secondary_namespaces.id)
3. 410.807 410.807 ↓ 39.1 100,001 1

Foreign Scan on gitlab_secondary.projects (cost=100.00..186.80 rows=2,560 width=8) (actual time=1.120..410.807 rows=100,001 loops=1)

  • Output: projects.id, projects.name, projects.path, projects.description, projects.created_at, projects.updated_at, projects.creator_id, projects.namespace_id, projects.last_ac
  • Remote SQL: SELECT id, namespace_id FROM public.projects
4. 0.003 1.559 ↑ 40.0 5 1

Hash (cost=3,362.40..3,362.40 rows=200 width=4) (actual time=1.559..1.559 rows=5 loops=1)

  • Output: gitlab_secondary_namespaces.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
5. 0.011 1.556 ↑ 40.0 5 1

HashAggregate (cost=3,360.40..3,362.40 rows=200 width=4) (actual time=1.554..1.556 rows=5 loops=1)

  • Output: gitlab_secondary_namespaces.id
  • Group Key: gitlab_secondary_namespaces.id
6. 1.545 1.545 ↑ 3,843.0 5 1

CTE Scan on base_and_descendants gitlab_secondary_namespaces (cost=2,735.91..3,120.21 rows=19,215 width=4) (actual time=0.616..1.545 rows=5 loops=1)

  • Output: gitlab_secondary_namespaces.id
7.          

CTE base_and_descendants

8. 0.011 1.544 ↑ 3,843.0 5 1

Recursive Union (cost=100.00..2,735.91 rows=19,215 width=4) (actual time=0.614..1.544 rows=5 loops=1)

9. 0.610 0.610 ↑ 3.0 5 1

Foreign Scan on gitlab_secondary.geo_node_namespace_links (cost=100.00..146.86 rows=15 width=4) (actual time=0.608..0.610 rows=5 loops=1)

  • Output: geo_node_namespace_links.namespace_id
  • Remote SQL: SELECT namespace_id FROM public.geo_node_namespace_links WHERE ((geo_node_id = 2))
10. 0.017 0.923 ↓ 0.0 0 1

Hash Join (cost=104.88..220.47 rows=1,920 width=4) (actual time=0.922..0.923 rows=0 loops=1)

  • Output: namespaces.id
  • Hash Cond: (namespaces.parent_id = base_and_descendants.id)
11. 0.902 0.902 ↑ 25.3 101 1

Foreign Scan on gitlab_secondary.namespaces (cost=100.00..186.80 rows=2,560 width=8) (actual time=0.721..0.902 rows=101 loops=1)

  • Output: namespaces.id, namespaces.name, namespaces.path, namespaces.owner_id, namespaces.created_at, namespaces.updated_at, namespaces.type, na
  • Remote SQL: SELECT id, parent_id FROM public.namespaces
12. 0.002 0.004 ↑ 30.0 5 1

Hash (cost=3.00..3.00 rows=150 width=4) (actual time=0.004..0.004 rows=5 loops=1)

  • Output: base_and_descendants.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
13. 0.002 0.002 ↑ 30.0 5 1

WorkTable Scan on base_and_descendants (cost=0.00..3.00 rows=150 width=4) (actual time=0.001..0.002 rows=5 loops=1)

  • Output: base_and_descendants.id
14. 10.000 10.000 ↑ 1.0 1 5,000

Index Scan using index_project_registry_on_project_id on public.project_registry (cost=0.42..2.16 rows=1 width=299) (actual time=0.002..0.002 rows=1 loops=5,000)

  • Output: project_registry.id, project_registry.project_id, project_registry.last_repository_synced_at, project_registry.last_repository_successful_sync_at, project_registry.created_a
  • Index Cond: (project_registry.project_id = projects.id)
  • Buffers: shared hit=20034