Laurenz Albe <laurenz.albe@xxxxxxxxxxx> 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;
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 |
-------------------------------------------------------------------------------------------------------------------------------------------------+
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 :-(
