Re: Terribly slow query with very good plan?

On Fri, 4 Feb 2022 at 10:09, Les <nagylzs@gmail.com> wrote:
>
> Oh I see, the query planner does not know that there will be no % characters in file and folder names.
>
> But what is the solution then? It just seems wrong that I can speed up a query 1000 times by replacing it with a
nestedloop in a pl/sql function :(
 

You don't need a nested loop, doing it in two stages in pl/pgsql would
be enough I think, first get the folder name and then construct a new
query using it as a constant.

I'd use SELECT FOR SHARE when getting the folder name, so that no
other process can change it underneath you before you run your second
query.

With the ^@ operator, my guess is that because the planner knows
nothing about the folder name value it could be the empty string,
which would be a prefix of everything.