explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 82XS

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 5,956.906 ↓ 0.0 0 1

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

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

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

3. 0.032 5,918.994 ↑ 1,948.4 5 1

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

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

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

  • Hash Cond: (lfs_objects_projects.project_id = projects.id)
5. 5,703.422 5,703.422 ↓ 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.036..5,703.422 rows=24,256 loops=1)

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

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

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

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

8. 0.118 1.227 ↑ 1.1 187 1

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

  • Group Key: namespaces.id
9. 1.109 1.109 ↑ 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.109 rows=187 loops=1)

10.          

CTE base_and_descendants

11. 0.085 1.041 ↑ 4.4 187 1

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

12. 0.001 0.001 ↑ 1.0 1 1

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

13. 0.940 0.955 ↑ 2.2 37 5

Nested Loop (cost=0.56..27.46 rows=82 width=4) (actual time=0.029..0.191 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.496 1.496 ↑ 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.005..0.008 rows=6 loops=187)

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
17. 37.873 37.873 ↑ 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=37.786..37.873 rows=34 loops=1)

  • Index Cond: (file_store = 1)
Planning time : 1.575 ms
Execution time : 5,959.117 ms