On Fri, 4 Feb 2022 at 09:11, Les <nagylzs@gmail.com> wrote:
|
> -> Index Only Scan using oo_file_idx_relpath on media.oo_file f2 (cost=0.55..108499.27 rows=5381 width=0)
(actualtime=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
> -> Seq Scan on media.oo_file of2 (cost=0.00..144714.70 rows=86960 width=0) (actual time=0.044..0.044 rows=1
loops=1)|
> Filter: (of2.relpath ~~ 'Felhasználók%'::text)
|
> Rows Removed by Filter: 15
|
> Buffers: shared hit=2
|
> Planning Time: 0.290 ms
|
> Execution Time: 0.076 ms
|
>
> In other words, I could write a pl/sql function with a nested loop instead of the problematic query, and it will be
1000times faster.
>
> What am I missing?
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.
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.