explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.001 10,284.410 ↓ 0.0 0 1

Limit (cost=4,167.81..4,198.27 rows=1 width=96) (actual time=10,284.410..10,284.410 rows=0 loops=1)

  • Buffers: shared hit=2000006
2.          

CTE restricted_lfs_objects

3. 0.036 30.644 ↑ 1,280.0 1 1

Hash Join (cost=3,910.21..4,017.97 rows=1,280 width=4) (actual time=30.644..30.644 rows=1 loops=1)

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

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

5. 0.027 29.869 ↑ 3.8 53 1

Hash (cost=3,807.71..3,807.71 rows=200 width=8) (actual time=29.869..29.869 rows=53 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
6. 0.058 29.842 ↑ 3.8 53 1

HashAggregate (cost=3,805.71..3,807.71 rows=200 width=8) (actual time=29.827..29.842 rows=53 loops=1)

  • Group Key: projects.id
7. 0.528 29.784 ↑ 27.6 53 1

Hash Join (cost=3,680.36..3,802.05 rows=1,462 width=8) (actual time=20.109..29.784 rows=53 loops=1)

  • Hash Cond: (projects.id = projects_1.id)
8. 9.764 9.764 ↓ 1.4 4,000 1

Foreign Scan on projects (cost=100.00..197.75 rows=2,925 width=4) (actual time=0.589..9.764 rows=4,000 loops=1)

9. 0.013 19.492 ↑ 3.8 53 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
10. 0.056 19.479 ↑ 3.8 53 1

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

  • Group Key: projects_1.id
11. 0.611 19.423 ↑ 24.2 53 1

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

  • Hash Cond: (projects_1.namespace_id = gitlab_secondary_namespaces.id)
12. 11.737 11.737 ↓ 1.6 4,000 1

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

13. 0.008 7.075 ↑ 11.8 17 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.019 7.067 ↑ 11.8 17 1

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

  • Group Key: gitlab_secondary_namespaces.id
15. 7.048 7.048 ↑ 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.711..7.048 rows=17 loops=1)

16.          

CTE base_and_descendants

17. 0.236 7.039 ↑ 1,130.3 17 1

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

18. 0.707 0.707 ↑ 1.2 12 1

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

19. 0.234 6.096 ↑ 960.0 2 2

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

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

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

21. 0.006 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
22. 0.002 0.002 ↑ 18.8 8 2

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

23. 0.015 10,284.409 ↓ 0.0 0 1

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

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

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

25. 0.002 10,253.747 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=2000006
26. 300.119 10,253.745 ↓ 0.0 0 1

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

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

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

28. 2,499.990 2,499.990 ↑ 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.005..0.005 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.562 ms
Execution time : 10,285.667 ms