On Fri, 2022-02-04 at 10:11 +0100, Les wrote: > 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..488.02 rows=20 width=26) (actual time=713.419..25414.969 rows=45 > loops=1) | > Output: 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 > > | > Planning Time: 0.361 ms > > | > Execution Time: 25415.088 ms > > | > > 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"); Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
