2

The Prepare and Execute combination in PostgreSQL permit the use of bound parameters. However, Prepare does not produce a plan optimized for one set of parameter bindings that can be reused with a different set of parameters bindings. Does anybody have pointers on implementing such functionality? With this, the plan would be optimized for the given set of parameter bindings but could be reused for another set. The plan might not be efficient for the subsequent set, but if the plan cost was recomputed using the new parameter bindings, it might be found to be efficient.

Reading and using parameter binding values for cardinality estimation is called "parameter sniffing" in SQL Server and "bind peeking" in Oracle. Basically, has anybody done anything similar in PostgreSQL.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Campbell Fraser
  • 106
  • 5
  • 1
    See `plan_cache_mode` in the Postgres-12 docs: https://www.postgresql.org/docs/12/runtime-config-query.html#GUC-PLAN-CACHE_MODE – wildplasser Jul 20 '20 at 17:25

1 Answers1

5

PostgreSQL uses a heuristic to decide whether to do "bind peeking". It does peeking the first 5 times (I think it is) that a prepared statement is executed, and if none of those lead to better (expected-to-be-better) plans than the generic plan was, it stops checking in the future.

Starting in v12, you can change this heuristic by setting plan_cache_mode.

Note that some drivers implement their own heuristics--just because you call the driver's prepare method doesn't mean it actually transmits this to the server as a PREPARE. It might instead stash the statement text away, wait until you execute, and then quote/escape your parameters and bundle them up with your previously pseudo-prepared statement and send them to the server in one packet. That is, they might treat the prepare/execute separation simply as a way to prevent SQL injections, not as a way to increase performance.

jjanes
  • 37,812
  • 5
  • 27
  • 34