explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gyZE

Settings
# exclusive inclusive rows x rows loops node
1. 0.416 34,716.889 ↓ 481.0 481 1

Nested Loop (cost=26,997.71..26,999.97 rows=1 width=148) (actual time=34,714.143..34,716.889 rows=481 loops=1)

2.          

CTE children

3. 0.213 34,707.818 ↓ 23.7 497 1

Recursive Union (cost=0.57..26,935.38 rows=21 width=48) (actual time=0.021..34,707.818 rows=497 loops=1)

4. 0.021 0.021 ↑ 1.0 1 1

Index Scan using file_pkey on file file_1 (cost=0.57..2.79 rows=1 width=48) (actual time=0.020..0.021 rows=1 loops=1)

  • Index Cond: (id = '2265356042'::bigint)
  • Filter: (deleted_at IS NULL)
5. 0.928 34,707.584 ↓ 62.0 124 4

Nested Loop (cost=896.54..2,693.22 rows=2 width=48) (actual time=1,664.300..8,676.896 rows=124 loops=4)

6. 0.152 0.152 ↓ 41.3 124 4

WorkTable Scan on children children_1 (cost=0.00..0.22 rows=3 width=48) (actual time=0.001..0.038 rows=124 loops=4)

  • Filter: (level < 32)
7. 0.994 34,706.504 ↑ 1.0 1 497

Bitmap Heap Scan on file file_2 (cost=896.54..897.65 rows=1 width=39) (actual time=69.831..69.832 rows=1 loops=497)

  • Recheck Cond: ((parent_id = children_1.id) AND (deleted_at IS NULL) AND (owner_id = children_1.owner_id))
  • Heap Blocks: exact=342
8. 145.026 34,705.510 ↓ 0.0 0 497

BitmapAnd (cost=896.54..896.54 rows=1 width=0) (actual time=69.830..69.830 rows=0 loops=497)

9. 1.988 1.988 ↑ 3,568.0 1 497

Bitmap Index Scan on ix_file_cmp_dnd (cost=0.00..144.54 rows=3,568 width=0) (actual time=0.004..0.004 rows=1 loops=497)

  • Index Cond: ((parent_id = children_1.id) AND (deleted_at IS NULL))
10. 34,558.496 34,558.496 ↓ 783.8 33,553,682 16

Bitmap Index Scan on ix_file_owner_id (cost=0.00..751.75 rows=42,810 width=0) (actual time=2,159.906..2,159.906 rows=33,553,682 loops=16)

  • Index Cond: (owner_id = children_1.owner_id)
11. 0.346 34,715.992 ↓ 481.0 481 1

Nested Loop (cost=62.19..64.43 rows=1 width=120) (actual time=34,714.101..34,715.992 rows=481 loops=1)

12. 0.573 34,714.155 ↓ 497.0 497 1

Sort (cost=61.62..61.63 rows=1 width=116) (actual time=34,714.065..34,714.155 rows=497 loops=1)

  • Sort Key: children.level
  • Sort Method: quicksort Memory: 154kB
13. 0.368 34,713.582 ↓ 497.0 497 1

Nested Loop (cost=1.15..61.61 rows=1 width=116) (actual time=0.053..34,713.582 rows=497 loops=1)

  • Join Filter: (children.id = file.id)
14. 0.520 34,711.226 ↓ 497.0 497 1

Nested Loop (cost=0.57..59.27 rows=1 width=52) (actual time=0.045..34,711.226 rows=497 loops=1)

15. 34,708.221 34,708.221 ↓ 23.7 497 1

CTE Scan on children (cost=0.00..0.42 rows=21 width=44) (actual time=0.023..34,708.221 rows=497 loops=1)

16. 2.485 2.485 ↑ 1.0 1 497

Index Scan using file_access_pkey on file_access (cost=0.57..2.80 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=497)

  • Index Cond: (file_id = children.id)
  • Filter: ((((acl ->> '0'::text))::integer & 1) = 1)
17. 1.988 1.988 ↑ 1.0 1 497

Index Scan using file_pkey on file (cost=0.57..2.33 rows=1 width=80) (actual time=0.004..0.004 rows=1 loops=497)

  • Index Cond: (id = file_access.file_id)
18. 1.491 1.491 ↑ 1.0 1 497

Index Scan using storage_file_pkey on storage_file (cost=0.57..2.79 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=497)

  • Index Cond: (id = file.storage_file_id)
19. 0.481 0.481 ↑ 1.0 1 481

Index Scan using storage_pkey on storage (cost=0.14..0.16 rows=1 width=548) (actual time=0.001..0.001 rows=1 loops=481)

  • Index Cond: (id = storage_file.storage_id)