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

On Wed, Jul 6, 2022 at 2:41 PM PG Bug reporting form <noreply(at)postgresql(dot)org>
wrote:

> The following bug has been logged on the website:
>
> Bug reference: 17540
> Logged by: William Duclot
> Email address: william(dot)duclot(at)gmail(dot)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.