On Fri, 4 Feb 2022 at 09:11, Les <nagy...@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) (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
> -> 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 1000 times 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.
