Clever Geek Handbook

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!

+3


source to share


2 answers


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.

+1


source


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

.

+1


source






More articles:


All Articles