explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SqED : https://gitlab.com/gitlab-org/gitlab/merge_requests/19580

Settings

Optimization(s) for this plan:

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

Limit (cost=4,648.49..5,094.03 rows=1 width=96) (actual time=10,955.929..10,955.929 rows=0 loops=1)

  • Buffers: shared hit=2000006, temp read=1376 written=2696
2.          

CTE restricted_lfs_objects

3. 0.802 2,448.229 ↑ 18,720.0 1 1

Merge Join (cost=4,210.63..4,498.65 rows=18,720 width=4) (actual time=2,448.229..2,448.229 rows=1 loops=1)

  • Merge Cond: (projects.id = lfs_objects_projects.project_id)
  • Buffers: temp read=1376 written=2696
4. 0.031 27.710 ↑ 1,462.0 1 1

Sort (cost=3,878.91..3,882.56 rows=1,462 width=8) (actual time=27.710..27.710 rows=1 loops=1)

  • Sort Key: projects.id
  • Sort Method: quicksort Memory: 27kB
5. 0.533 27.679 ↑ 27.6 53 1

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

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

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

7. 0.019 17.876 ↑ 3.8 53 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
8. 0.061 17.857 ↑ 3.8 53 1

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

  • Group Key: projects_1.id
9. 0.554 17.796 ↑ 24.2 53 1

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

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

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

11. 0.008 5.575 ↑ 11.8 17 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 0.021 5.567 ↑ 11.8 17 1

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

  • Group Key: gitlab_secondary_namespaces.id
13. 5.546 5.546 ↑ 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.366..5.546 rows=17 loops=1)

14.          

CTE base_and_descendants

15. 0.096 5.537 ↑ 1,130.3 17 1

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

16. 0.363 0.363 ↑ 1.2 12 1

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

17. 0.218 5.078 ↑ 960.0 2 2

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

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

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

19. 0.006 0.010 ↑ 18.8 8 2

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.004 0.004 ↓ 0.0 8 2

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

21. 809.617 2,419.717 ↓ 3.9 10,079 1

Sort (cost=331.72..338.12 rows=2,560 width=8) (actual time=2,418.737..2,419.717 rows=10,079 loops=1)

  • Sort Key: lfs_objects_projects.project_id
  • Sort Method: external sort Disk: 10784kB
  • Buffers: temp read=1376 written=2696
22. 1,610.100 1,610.100 ↓ 195.4 500,144 1

Foreign Scan on lfs_objects_projects (cost=100.00..186.80 rows=2,560 width=8) (actual time=0.460..1,610.1 rows=500,144 loops=1)

23. 0.022 10,955.928 ↓ 0.0 0 1

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

  • Hash Cond: (restricted_lfs_objects.lfs_object_id = lfs_objects.id)
  • Buffers: shared hit=2000006, temp read=1376 written=2696
24. 2,448.234 2,448.234 ↑ 18,720.0 1 1

CTE Scan on restricted_lfs_objects (cost=0.00..374.40 rows=18,720 width=4) (actual time=2,448.233..2,448.234 rows=1 loops=1)

  • Buffers: temp read=1376 written=2696
25. 0.001 8,507.672 ↓ 0.0 0 1

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

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

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

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

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

28. 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.794 ms
Execution time : 10,960.904 ms