explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZGIz

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 8,713.813 ↓ 0.0 0 1

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

  • Buffers: shared hit=2000006
2.          

CTE restricted_lfs_objects

3. 0.039 27.240 ↑ 1,280.0 1 1

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

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

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

5. 0.031 26.458 ↑ 3.8 53 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
6. 0.060 26.427 ↑ 3.8 53 1

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

  • Group Key: projects.id
7. 0.505 26.367 ↑ 27.6 53 1

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

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

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

9. 0.029 16.675 ↑ 3.8 53 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
10. 0.058 16.646 ↑ 3.8 53 1

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

  • Group Key: projects_1.id
11. 0.544 16.588 ↑ 24.2 53 1

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

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

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

13. 0.014 5.728 ↑ 11.8 17 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.022 5.714 ↑ 11.8 17 1

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

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

16.          

CTE base_and_descendants

17. 0.106 5.684 ↑ 1,130.3 17 1

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

18. 0.402 0.402 ↑ 1.2 12 1

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

19. 0.226 5.176 ↑ 960.0 2 2

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

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

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

21. 0.008 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
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. 27.258 8,713.812 ↓ 0.0 0 1

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

  • Hash Cond: (restricted_lfs_objects.lfs_object_id = lfs_objects.id)
  • Buffers: shared hit=2000006
  • -> CTE Scan on restricted_lfs_objects (cost=0.00..25.60 rows=1280 width=4) (actual time=27.242..27.242 rows=1 =1)
24. 0.001 8,686.554 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=2000006
25. 294.469 8,686.553 ↓ 0.0 0 1

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

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

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

27. 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.549 ms
Execution time : 8,715.548 ms