Re: Terribly slow query with very good plan?

Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> ezt írta (időpont: 2022. febr. 4.,
P, 10:18):

> |
> >
> > 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
|

Not helping :-(