Re: BUG #17540: Prepared statement: PG switches to a generic query plan which is consistently much slower

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: BUG #17540: Prepared statement: PG switches to a generic query plan which is consistently much slower
Дата
Msg-id CAKFQuwZDyjnCRGfvbQJrhfOiCx5z5=khZr4QJWUMEcEcwx4s9A@mail.gmail.com
обсуждение исходный текст
Ответ на BUG #17540: Prepared statement: PG switches to a generic query plan which is consistently much slower  (PG Bug reporting form <noreply@postgresql.org>)
Ответы Re: BUG #17540: Prepared statement: PG switches to a generic query plan which is consistently much slower  (Andres Freund <andres@anarazel.de>)
Список pgsql-bugs
Дерево обсуждения
On Wed, Jul 6, 2022 at 2:41 PM PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:

Bug reference:      17540
Logged by:          William Duclot
Email address:      william.duclot@gmail.com
PostgreSQL version: 14.4
Operating system:   GNU/Linux (Red Hat 8.5.0)
Description:       
 
This means that the query planner does not realise that the actual
parameter value matters a lot, and that the parameters used _in practice_
result in a faster plan than the generic plan (100% of the first 5
executions), and that therefore it shouldn't stick to the generic plan.

I mean, it is the planner and so, no, it doesn't understand that the executor encountered an issue.


It is particularly insidious as actually I wasn't even aware I was using
prepared statements. Like most applications I use a database driver (pgx, in
Go) which I learnt uses `PQexecPrepared` under the hood, which creates a
sort of "unnamed prepared statement" behaving the same as this minimal
reproduction without me ever being aware that prepared statements are
involved anywhere between my code and the database.

Yep, and the core project pretty much says that if you don't like this you need to complain to the driver writer and ask them to provide you an interface to the unnamed parse-bind-execute API which lets you perform parameterization without memory, just safety.

PostgreSQL has built the needed tools to make this less problematic, and has made solid attempts to improve matters in the current state of things.  There doesn't seem to be a bug here.  There is potentially room for improvement but no one presently is working on things in this area.

David J.

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Ronan Dunklau
Дата:
Сообщение: Re: index cost estimation
Следующее
От: David Rowley
Дата:
Сообщение: Re: BUG #17540: Prepared statement: PG switches to a generic query plan which is consistently much slower