explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AHSe : Optimization for: Optimization for: https://gitlab.com/gitlab-org/gitlab/merge_requests/19580; plan #SqED; plan #7QrN

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.002 8,583.382 ↓ 0.0 0 1

Limit (cost=3,937.96..3,968.42 rows=1 width=96) (actual time=8,583.382..8,583.382 rows=0 loops=1)

  • Buffers: shared hit=2000006
2.          

CTE restricted_lfs_objects

3. 0.042 19.944 ↑ 1,280.0 1 1

Hash Join (cost=3,680.36..3,788.12 rows=1,280 width=4) (actual time=19.944..19.944 rows=1 loops=1)

  • Hash Cond: (lfs_objects_projects.project_id = projects.id)
4. 0.752 0.752 ↑ 16.4 156 1

Foreign Scan on lfs_objects_projects (cost=100.00..186.80 rows=2,560 width=8) (actual time=0.531..0.752 rows=156 loops=1)

5. 0.021 19.150 ↑ 3.8 53 1

Hash (cost=3,577.86..3,577.86 rows=200 width=4) (actual time=19.149..19.150 rows=53 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
6. 0.063 19.129 ↑ 3.8 53 1

HashAggregate (cost=3,575.86..3,577.86 rows=200 width=4) (actual time=19.115..19.129 rows=53 loops=1)

  • Group Key: projects.id
7. 0.551 19.066 ↑ 24.2 53 1

Hash Join (cost=3,464.90..3,572.66 rows=1,280 width=4) (actual time=6.796..19.066 rows=53 loops=1)

  • Hash Cond: (projects.namespace_id = gitlab_secondary_namespaces.id)
8. 12.380 12.380 ↓ 1.6 4,000 1

Foreign Scan on projects (cost=100.00..186.80 rows=2,560 width=8) (actual time=0.622..12.380 rows=4,000 loops=1)

9. 0.008 6.135 ↑ 11.8 17 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
10. 0.023 6.127 ↑ 11.8 17 1

HashAggregate (cost=3,360.40..3,362.40 rows=200 width=4) (actual time=6.121..6.127 rows=17 loops=1)

  • Group Key: gitlab_secondary_namespaces.id
11. 6.104 6.104 ↑ 1,130.3 17 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.395..6.104 rows=17 loops=1)

12.          

CTE base_and_descendants

13. 0.126 6.095 ↑ 1,130.3 17 1

Recursive Union (cost=100.00..2,735.91 rows=19,215 width=4) (actual time=0.393..6.095 rows=17 loops=1)

14. 0.391 0.391 ↑ 1.2 12 1

Foreign Scan on geo_node_namespace_links (cost=100.00..146.86 rows=15 width=4) (actual time=0.388..0.391 rows=12 loops=1)

15. 0.238 5.578 ↑ 960.0 2 2

Hash Join (cost=104.88..220.47 rows=1,920 width=4) (actual time=2.339..2.789 rows=2 loops=2)

  • Hash Cond: (namespaces.parent_id = base_and_descendants.id)
16. 5.332 5.332 ↑ 2.5 1,004 2

Foreign Scan on namespaces (cost=100.00..186.80 rows=2,560 width=8) (actual time=0.429..2.666 rows=1,004 loops=2)

17. 0.004 0.008 ↑ 18.8 8 2

Hash (cost=3.00..3.00 rows=150 width=4) (actual time=0.004..0.004 rows=8 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.004 0.004 ↑ 18.8 8 2

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

19. 0.012 8,583.380 ↓ 0.0 0 1

Hash Join (cost=149.84..180.30 rows=1 width=96) (actual time=8,583.380..8,583.380 rows=0 loops=1)

  • Hash Cond: (restricted_lfs_objects.lfs_object_id = lfs_objects.id)
  • Buffers: shared hit=2000006
20. 19.947 19.947 ↑ 1,280.0 1 1

CTE Scan on restricted_lfs_objects (cost=0.00..25.60 rows=1,280 width=4) (actual time=19.947..19.947 rows=1 loops=1)

21. 0.001 8,563.421 ↓ 0.0 0 1

Hash (cost=149.83..149.83 rows=1 width=96) (actual time=8,563.421..8,563.421 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=2000006
22. 164.914 8,563.420 ↓ 0.0 0 1

Nested Loop Left Join (cost=100.42..149.83 rows=1 width=96) (actual time=8,563.420..8,563.420 rows=0 loops=1)

  • Filter: (file_registry.id IS NULL)
  • Rows Removed by Filter: 499998
  • Buffers: shared hit=2000006
23. 6,398.514 6,398.514 ↓ 71,428.3 499,998 1

Foreign Scan on lfs_objects (cost=100.00..118.66 rows=7 width=96) (actual time=0.896..6,398.514 rows=499,998 loops=1)

24. 1,999.992 1,999.992 ↑ 1.0 1 499,998

Index Scan using index_file_registry_on_file_type_and_file_id on file_registry (cost=0.42..4.44 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=499,998)

  • Index Cond: (((file_type)::text = 'lfs'::text) AND (lfs_objects.id = file_id))
  • Buffers: shared hit=2000006
Planning time : 0.464 ms
Execution time : 8,584.490 ms