explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rFFN

Settings
# exclusive inclusive rows x rows loops node
1. 0.399 30,655.080 ↓ 481.0 481 1

Nested Loop (cost=27,643.09..27,645.34 rows=1 width=148) (actual time=30,652.553..30,655.080 rows=481 loops=1)

2.          

CTE children

3. 0.176 30,647.399 ↓ 23.7 497 1

Recursive Union (cost=0.57..27,580.75 rows=21 width=48) (actual time=0.027..30,647.399 rows=497 loops=1)

4. 0.019 0.019 ↑ 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.018..0.019 rows=1 loops=1)

  • Index Cond: (id = '2265356042'::bigint)
  • Filter: (deleted_at IS NULL)
5. 1.042 30,647.204 ↓ 62.0 124 4

Nested Loop (cost=918.05..2,757.75 rows=2 width=48) (actual time=1,459.116..7,661.801 rows=124 loops=4)

6. 0.148 0.148 ↓ 41.3 124 4

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

  • Filter: (level < 32)
7. 0.497 30,646.014 ↑ 1.0 1 497

Bitmap Heap Scan on file file_2 (cost=918.05..919.16 rows=1 width=39) (actual time=61.661..61.662 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. 106.250 30,645.517 ↓ 0.0 0 497

BitmapAnd (cost=918.05..918.05 rows=1 width=0) (actual time=61.661..61.661 rows=0 loops=497)

9. 1.491 1.491 ↑ 3,780.0 1 497

Bitmap Index Scan on ix_file_cmp_dnd (cost=0.00..146.95 rows=3,780 width=0) (actual time=0.003..0.003 rows=1 loops=497)

  • Index Cond: ((parent_id = children_1.id) AND (deleted_at IS NULL))
10. 30,537.776 30,537.776 ↓ 740.3 33,575,830 16

Bitmap Index Scan on ix_file_owner_id (cost=0.00..770.85 rows=45,357 width=0) (actual time=1,908.611..1,908.611 rows=33,575,830 loops=16)

  • Index Cond: (owner_id = children_1.owner_id)
11. 0.150 30,654.200 ↓ 481.0 481 1

Nested Loop (cost=62.19..64.43 rows=1 width=120) (actual time=30,652.511..30,654.200 rows=481 loops=1)

12. 0.483 30,652.559 ↓ 497.0 497 1

Sort (cost=61.62..61.63 rows=1 width=116) (actual time=30,652.479..30,652.559 rows=497 loops=1)

  • Sort Key: children.level
  • Sort Method: quicksort Memory: 154kB
13. 0.413 30,652.076 ↓ 497.0 497 1

Nested Loop (cost=1.15..61.61 rows=1 width=116) (actual time=0.084..30,652.076 rows=497 loops=1)

  • Join Filter: (children.id = file.id)
14. 0.419 30,650.172 ↓ 497.0 497 1

Nested Loop (cost=0.57..59.27 rows=1 width=52) (actual time=0.076..30,650.172 rows=497 loops=1)

15. 30,647.765 30,647.765 ↓ 23.7 497 1

CTE Scan on children (cost=0.00..0.42 rows=21 width=44) (actual time=0.043..30,647.765 rows=497 loops=1)

16. 1.988 1.988 ↑ 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.004..0.004 rows=1 loops=497)

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

Index Scan using file_pkey on file (cost=0.57..2.33 rows=1 width=80) (actual time=0.003..0.003 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)