How can I execute an Explain statement with a prepared statement in PostgrSQL that has bind options?
I would like to be able to follow the explain statement for the request with the binding parameters. For example:
EXPLAIN SELECT * FROM metasyntax WHERE id = $1;
When I try this I get the following error:
ERROR: bind message supplies 0 parameters, but prepared statement "" requires 1
I understand this is telling me that he wants me to give a value for the query. However, I may not necessarily know the answer to this question. In other dialects of SQL like Oracle, it will generate an explain plan without requiring me to give a value for the parameter.
Is it possible to get an outline of an explanation without reference to the actual value? Thank!
Not.
The optimizer is allowed to modify the query plan based on the parameter. Imagine if the parameter was null
- obviously no rows will be returned, so the DB can return an empty rowset immediately.
Just use a representative value.
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);
Typically PostgreSQL will switch to a generic plan for the sixth run, and you will see a plan containing a placeholder $1
.