explain.depesz.com

PostgreSQL's explain analyze made readable

Result: G1sc

Settings
# exclusive inclusive rows x rows loops node
1. 0.013 276.137 ↓ 0.0 0 1

Hash Join (cost=355,485.67..355,656.78 rows=1 width=4) (actual time=276.137..276.137 rows=0 loops=1)

  • Hash Cond: (lfs_objects_projects.lfs_object_id = lfs_objects.id)
2. 0.005 234.431 ↑ 1,939.0 5 1

Unique (cost=355,470.80..355,519.51 rows=9,695 width=4) (actual time=234.428..234.431 rows=5 loops=1)

3. 0.016 234.426 ↑ 1,948.4 5 1

Sort (cost=355,470.80..355,495.16 rows=9,742 width=4) (actual time=234.426..234.426 rows=5 loops=1)

  • Sort Key: lfs_objects_projects.lfs_object_id
  • Sort Method: quicksort Memory: 25kB
4. 180.928 234.410 ↑ 1,948.4 5 1

Hash Semi Join (cost=221,494.57..354,825.40 rows=9,742 width=4) (actual time=105.729..234.410 rows=5 loops=1)

  • Hash Cond: (lfs_objects_projects.project_id = projects.id)
5. 49.766 49.766 ↓ 2.5 24,256 1

Index Scan using index_lfs_objects_projects_on_lfs_object_id on lfs_objects_projects (cost=0.56..10,248.20 rows=9,742 width=8) (actual time=0.030..49.766 rows=24,256 loops=1)

  • Index Cond: ((lfs_object_id >= 1) AND (lfs_object_id <= 1,000))
6. 0.426 3.716 ↑ 7,066.0 1,110 1

Hash (cost=901.76..901.76 rows=7,843,220 width=4) (actual time=3.716..3.716 rows=1,110 loops=1)

  • Buckets: 4,194,304 Batches: 8 Memory Usage: 32,825kB
7. 0.227 3.290 ↑ 7,066.0 1,110 1

Nested Loop (cost=318.19..901.76 rows=7,843,220 width=4) (actual time=1.194..3.290 rows=1,110 loops=1)

8. 0.108 1.193 ↑ 1.1 187 1

HashAggregate (cost=317.75..319.75 rows=200 width=4) (actual time=1.147..1.193 rows=187 loops=1)

  • Group Key: namespaces.id
9. 1.085 1.085 ↑ 4.4 187 1

CTE Scan on base_and_descendants namespaces (cost=291.07..307.49 rows=821 width=4) (actual time=0.007..1.085 rows=187 loops=1)

10.          

CTE base_and_descendants

11. 0.080 1.032 ↑ 4.4 187 1

Recursive Union (cost=0.00..291.07 rows=821 width=4) (actual time=0.005..1.032 rows=187 loops=1)

12. 0.002 0.002 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1)

13. 0.935 0.950 ↑ 2.2 37 5

Nested Loop (cost=0.56..27.46 rows=82 width=4) (actual time=0.032..0.190 rows=37 loops=5)

  • -> Index Only Scan using index_namespaces_on_parent_id_and_id on namespaces namespaces_1 (cost=0.56..2.65 rows=8 width=8) (actual time=0.004..0.005 ro
14. 0.015 0.015 ↓ 3.7 37 5

WorkTable Scan on base_and_descendants (cost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.003 rows=37 loops=5)

  • Index Cond: (parent_id = base_and_descendants.id)
  • Heap Fetches: 8
15. 1.870 1.870 ↑ 3.0 6 187

Index Only Scan using index_projects_on_namespace_id_and_id on projects (cost=0.43..2.73 rows=18 width=8) (actual time=0.007..0.010 rows=6 loops=187)

  • Index Cond: (namespace_id = namespaces.id)
  • Heap Fetches: 103
16. 0.021 41.693 ↑ 3.2 34 1

Hash (cost=13.49..13.49 rows=110 width=4) (actual time=41.693..41.693 rows=34 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
17. 41.672 41.672 ↑ 3.2 34 1

Index Scan using index_lfs_objects_on_file_store on lfs_objects (cost=0.56..13.49 rows=110 width=4) (actual time=41.563..41.672 rows=34 loops=1)

  • Index Cond: (file_store = 1)
Planning time : 1.382 ms
Execution time : 278.918 ms