explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zzA

Settings
# exclusive inclusive rows x rows loops node
1. 0.014 309.566 ↓ 0.0 0 1

Hash Join (cost=355,389.64..355,560.75 rows=1 width=4) (actual time=309.566..309.566 rows=0 loops=1)

  • Hash Cond: (lfs_objects_projects.lfs_object_id = lfs_objects.id)
  • Buffers: shared hit=26,366
2. 0.008 304.558 ↑ 1,939.0 5 1

Unique (cost=355,374.78..355,423.49 rows=9,695 width=4) (actual time=304.551..304.558 rows=5 loops=1)

  • Buffers: shared hit=26,156
3. 0.024 304.550 ↑ 1,948.4 5 1

Sort (cost=355,374.78..355,399.13 rows=9,742 width=4) (actual time=304.549..304.550 rows=5 loops=1)

  • Sort Key: lfs_objects_projects.lfs_object_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=26,156
4. 224.213 304.526 ↑ 1,948.4 5 1

Hash Semi Join (cost=221,414.54..354,729.37 rows=9,742 width=4) (actual time=148.259..304.526 rows=5 loops=1)

  • Hash Cond: (lfs_objects_projects.project_id = projects.id)
  • Buffers: shared hit=26,156
5. 71.018 71.018 ↓ 2.5 24,256 1

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

  • Index Cond: ((lfs_objects_projects.lfs_object_id >= 1) AND (lfs_objects_projects.lfs_object_id <= 1,000))
  • Buffers: shared hit=23,687
6. 1.841 9.295 ↑ 7,065.0 1,110 1

Hash (cost=851.63..851.63 rows=7,842,108 width=4) (actual time=9.295..9.295 rows=1,110 loops=1)

  • Buckets: 4,194,304 Batches: 8 Memory Usage: 32,825kB
  • Buffers: shared hit=2,469
7. 0.467 7.454 ↑ 7,065.0 1,110 1

Nested Loop (cost=318.19..851.63 rows=7,842,108 width=4) (actual time=3.011..7.454 rows=1,110 loops=1)

  • Buffers: shared hit=2,469
8. 0.268 3.060 ↑ 1.1 187 1

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

  • Group Key: namespaces.id
  • Buffers: shared hit=888
9. 2.792 2.792 ↑ 4.4 187 1

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

  • Buffers: shared hit=888
10.          

CTE base_and_descendants

11. 0.181 2.689 ↑ 4.4 187 1

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

  • Buffers: shared hit=888
12. 0.003 0.003 ↑ 1.0 1 1

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

13. 0.226 2.505 ↑ 2.2 37 5

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

  • Buffers: shared hit=888
14. 0.035 0.035 ↓ 3.7 37 5

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

15. 2.244 2.244 ↑ 8.0 1 187

Index Only Scan using index_namespaces_on_parent_id_and_id on public.namespaces namespaces_1 (cost=0.56..2.65 rows=8 width=8) (actual time=0.011..0.012 rows=1 loops=187)

  • Index Cond: (namespaces_1.parent_id = base_and_descendants.id)
  • Heap Fetches: 8
  • Buffers: shared hit=888
16. 3.927 3.927 ↑ 3.0 6 187

Index Only Scan using index_projects_on_namespace_id_and_id on public.projects (cost=0.43..2.48 rows=18 width=8) (actual time=0.015..0.021 rows=6 loops=187)

  • Index Cond: (projects.namespace_id = namespaces.id)
  • Heap Fetches: 41
  • Buffers: shared hit=1,581
17. 0.022 4.994 ↑ 3.2 34 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=210
18. 4.972 4.972 ↑ 3.2 34 1

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

  • Index Cond: (lfs_objects.file_store = 1)
  • Buffers: shared hit=210