Nick Cleaton <nick@xxxxxxxxxxx> ezt írta (időpont: 2022. febr. 4., P, 11:00):
In the fast case the 'Felhasználók%' part is known at query planning
time, so it can be a prefix search.
In the slow case, the planner doesn't know what that value will be, it
could be something that starts with '%' for example.
First of all, it CANNOT start with '%'. This is a fact and this fact can be determined by analyzing the query. Something that the query planner should do, right?
Second argument: the same query is also slow with the ^@ operator...
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
select id, title,
(exists (select f2.id from
media.oo_file f2
where f2.relpath ^@ 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..449.38 rows=20 width=26) (actual time=1652.624..61636.232 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..98067.11 rows=5379 width=0) (actual time=1369.665..1369.665 rows=0 loops=45) |
Filter: (f2.relpath ^@ f.relpath) |
Rows Removed by Filter: 777428 |
Heap Fetches: 736418 |
Buffers: shared hit=6672234 |
Planning Time: 0.346 ms |
Execution Time: 61636.319 ms |
select id, title,
(exists (select f2.id from
media.oo_file f2
where f2.relpath ^@ 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..449.38 rows=20 width=26) (actual time=1652.624..61636.232 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..98067.11 rows=5379 width=0) (actual time=1369.665..1369.665 rows=0 loops=45) |
Filter: (f2.relpath ^@ f.relpath) |
Rows Removed by Filter: 777428 |
Heap Fetches: 736418 |
Buffers: shared hit=6672234 |
Planning Time: 0.346 ms |
Execution Time: 61636.319 ms |
Also your logic looks a bit unsafe, the query you have would include
files under all top-level folders with names starting with
Felhasználók, so you could accidentally merge in files in folders
called Felhasználókfoo and Felhasználókbar for example.
Forgive me, I typed in these examples for demonstration. The actual code uses relpath || '/%' and it avoids those cases.
