How can I execute an "Explain" statement with a prepared query in PostgrSQL that has bind parameters?

Question

I would like to be able to execute an explain statement on a query that has bind parameters. For example:

EXPLAIN SELECT * FROM metasyntax WHERE id = $1;

When I attempt to execute this, I get the following error:

ERROR: bind message supplies 0 parameters, but prepared statement "" requires 1

I understand it's telling me that it wants me to supply a value for the query. However, I may not necessarily know the answer to that. In other SQL dialects such as Oracle, it will generate an explain plan without needing me to supply a value for the parameter.

Is it possible to get an explain plan without binding to an actual value? Thanks!

Answer 1

Assuming the parameter is an integer:

PREPARE x(integer) AS SELECT * FROM metasyntax WHERE id = $1;

Then run the following six times, where “42” is a representative value:

EXPLAIN (ANALYZE, BUFFERS) EXECUTE x(42);

Normally PostgreSQL will switch to a generic plan for the sixth run, and you will see a plan that contains the placeholder $1.

Answer 2

No.

The optimiser is allowed to change the query plan based on the parameter. Imagine if the parameter was null - it's obvious that no rows will be returned, so the DB may return an empty rowset instantly.

Just use a representative value.