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
