Re: Terribly slow query with very good plan?

On Fri, 4 Feb 2022 at 09:11, Les <nagylzs(at)gmail(dot)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.