From: | Nick Cleaton <nick(at)cleaton(dot)net> |
---|---|
To: | Les <nagylzs(at)gmail(dot)com> |
Cc: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re: Terribly slow query with very good plan? |
Date: | 2022-02-04 10:57:13 |
Message-ID: | CAFgz3ksppcheYMdTO7ag7+d4+MoDJth=zEamx0yuCpwa+=rZtw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Fri, 4 Feb 2022 at 10:09, Les <nagylzs(at)gmail(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Les | 2022-02-04 12:27:28 | Re: Terribly slow query with very good plan? |
Previous Message | Les | 2022-02-04 10:38:53 | Re: Terribly slow query with very good plan? |