On Fri, 4 Feb 2022 at 10:09, Les <nagy...@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 nested loop 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.
