explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HOI5

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 3,821.716 ↓ 0.0 0 1

Limit (cost=4,165.47..17,470.33 rows=1 width=4) (actual time=3,821.716..3,821.716 rows=0 loops=1)

  • Buffers: shared hit=5138, temp read=2601 written=2599
2.          

CTE restricted_lfs_objects

3. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=3,910.21..4,017.97 rows=1,280 width=4) (never executed)

  • Hash Cond: (lfs_objects_projects.project_id = projects.id)
4. 0.000 0.000 ↓ 0.0 0

Foreign Scan on lfs_objects_projects (cost=100.00..186.80 rows=2,560 width=8) (never executed)

5. 0.000 0.000 ↓ 0.0 0

Hash (cost=3,807.71..3,807.71 rows=200 width=8) (never executed)

6. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=3,805.71..3,807.71 rows=200 width=8) (never executed)

  • Group Key: projects.id
7. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=3,680.36..3,802.05 rows=1,462 width=8) (never executed)

  • Hash Cond: (projects.id = projects_1.id)
8. 0.000 0.000 ↓ 0.0 0

Foreign Scan on projects (cost=100.00..197.75 rows=2,925 width=4) (never executed)

9. 0.000 0.000 ↓ 0.0 0

Hash (cost=3,577.86..3,577.86 rows=200 width=4) (never executed)

10. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=3,575.86..3,577.86 rows=200 width=4) (never executed)

  • Group Key: projects_1.id
11. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=3,464.90..3,572.66 rows=1,280 width=4) (never executed)

  • Hash Cond: (projects_1.namespace_id = gitlab_secondary_namespaces.id)
12. 0.000 0.000 ↓ 0.0 0

Foreign Scan on projects projects_1 (cost=100.00..186.80 rows=2,560 width=8) (never executed)

13. 0.000 0.000 ↓ 0.0 0

Hash (cost=3,362.40..3,362.40 rows=200 width=4) (never executed)

14. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=3,360.40..3,362.40 rows=200 width=4) (never executed)

  • Group Key: gitlab_secondary_namespaces.id
15. 0.000 0.000 ↓ 0.0 0

CTE Scan on base_and_descendants gitlab_secondary_namespaces (cost=2,735.91..3,120.21 rows=19,215 width=4) (never executed)

16.          

CTE base_and_descendants

17. 0.000 0.000 ↓ 0.0 0

Recursive Union (cost=100.00..2,735.91 rows=19,215 width=4) (never executed)

18. 0.000 0.000 ↓ 0.0 0

Foreign Scan on geo_node_namespace_links (cost=100.00..146.86 rows=15 width=4) (never executed)

19. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=104.88..220.47 rows=1,920 width=4) (never executed)

  • Hash Cond: (namespaces.parent_id = base_and_descendants.id)
20. 0.000 0.000 ↓ 0.0 0

Foreign Scan on namespaces (cost=100.00..186.80 rows=2,560 width=8) (never executed)

21. 0.000 0.000 ↓ 0.0 0

Hash (cost=3.00..3.00 rows=150 width=4) (never executed)

22. 0.000 0.000 ↓ 0.0 0

WorkTable Scan on base_and_descendants (cost=0.00..3.00 rows=150 width=4) (never executed)

23. 0.003 3,821.715 ↓ 0.0 0 1

Nested Loop (cost=147.50..13,452.36 rows=1 width=4) (actual time=3,821.715..3,821.715 rows=0 loops=1)

  • Join Filter: (lfs_objects.id = restricted_lfs_objects.lfs_object_id)
  • Buffers: shared hit=5138, temp read=2601 written=2599
24. 1,103.466 3,821.712 ↓ 0.0 0 1

Hash Right Join (cost=147.50..13,410.76 rows=1 width=4) (actual time=3,821.712..3,821.712 rows=0 loops=1)

  • Hash Cond: (lfs_object_registry.lfs_object_id = lfs_objects.id)
  • Filter: (lfs_object_registry.id IS NULL)
  • Rows Removed by Filter: 499998
  • Buffers: shared hit=5138, temp read=2601 written=2599
25. 77.973 77.973 ↑ 1.0 499,998 1

Seq Scan on lfs_object_registry (cost=0.00..10,137.98 rows=499,998 width=12) (actual time=0.025..77.973 rows=499,998 loops=1)

  • Buffers: shared hit=5138
26. 207.269 2,640.273 ↓ 17,241.3 499,998 1

Hash (cost=147.14..147.14 rows=29 width=4) (actual time=2,640.273..2,640.273 rows=499,998 loops=1)

  • Buckets: 524288 (originally 1024) Batches: 2 (originally 1) Memory Usage: 12874kB
  • Buffers: temp written=733
27. 2,433.004 2,433.004 ↓ 17,241.3 499,998 1

Foreign Scan on lfs_objects (cost=100.00..147.14 rows=29 width=4) (actual time=0.606..2,433.004 rows=499,998 loops=1)

28. 0.000 0.000 ↓ 0.0 0

CTE Scan on restricted_lfs_objects (cost=0.00..25.60 rows=1,280 width=4) (never executed)

Planning time : 0.774 ms
Execution time : 3,822.948 ms