Les schrieb am 04.02.2022 um 10:11:

> My task is to write a query that tells if a folder has any active file inside 
> it - directly or in subfolders. Here is the query for that:
>
> EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
>
> select id, title,
> (exists (select f2.id <http://f2.id> from media.oo_file f2 where f2.relpath 
> like f.relpath || '%')) as has_file
> from media.oo_folder f where f.parent_id is null
>
> QUERY PLAN                                                                    
>                                                                               
>   |
> --------------------------------------------------------------------------------------------------------------------------------------------------------------+
> Index Scan using oo_folder_idx_parent on media.oo_folder f  
> (cost=0.42..488.02 rows=20 width=26) (actual time=713.419..25414.969 rows=45 
> loops=1)             |
>   Output: f.id <http://f.id>, f.title, (SubPlan 1)                            
>                                                                               
>                 |
>   Index Cond: (f.parent_id IS NULL)                                           
>                                                                               
>   |
>   Buffers: shared hit=7014170                                                 
>                                                                               
>   |
>   SubPlan 1                                                                   
>                                                                               
>   |
>     ->  Index Only Scan using oo_file_idx_relpath on media.oo_file f2  
> (cost=0.55..108499.27 rows=5381 width=0) (actual time=564.756..564.756 rows=0 
> loops=45)|
>           Filter: (f2.relpath ~~ (f.relpath || '%'::text))                    
>                                                                               
>   |
>           Rows Removed by Filter: 792025                                      
>                                                                               
>   |
>           Heap Fetches: 768960                                                
>                                                                               
>   |
>           Buffers: shared hit=7014130                                         
>                                                                               
>   |

In addition to the collation tweaks, I wonder if using a lateral join might 
result in a more efficient plan:

    select id, title, c.id is not null as has_path
    from media.oo_folder f
      left join lateral (
        select f2.id
        from media.oo_file f2
        where f2.relpath like f.relpath || '%'
        limit 1
      ) c on true
    where f.parent_id is null



Reply via email to