| > > It also returns 45 rows, but in 25 seconds which is unacceptable.
You should create an index that supports LIKE; for example
CREATE INDEX ON media.oo_file (relpath COLLATE "C");
CREATE INDEX test ON media.oo_file (relpath COLLATE "C");
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
select id, title, (exists (select 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..459.38 rows=20 width=26) (actual time=772.566..24081.820 rows=45 loops=1)| Output: f.id, f.title, (SubPlan 1) | Index Cond: (f.parent_id IS NULL) | Buffers: shared hit=6672274 | SubPlan 1 | -> Index Only Scan using test on media.oo_file f2 (cost=0.55..100756.64 rows=5379 width=0) (actual time=535.113..535.113 rows=0 loops=45) | Filter: (f2.relpath ~~ (f.relpath || '%'::text)) | Rows Removed by Filter: 777428 | Heap Fetches: 736418 | Buffers: shared hit=6672234 | Planning Time: 0.338 ms | Execution Time: 24082.152 ms |