© Laurenz Albe 2023
A while ago, I wrote about how difficult it is to get an execution plan for a parameterized query. The method suggested in that article works, but is still somewhat complicated. So I wrote a patch to support an
GENERIC_PLAN, which provides native support for that. My patch got committed by Tom Lane in 3c05284d83, which means the new option will be available from v16 on. This article describes how the new feature works and how it can be useful.
The syntax of
You can only use the new option in a parenthesized options list, like this:
EXPLAIN (GENERIC_PLAN) SELECT ...;
Many people are still used to the old, simple way of writing options, like
EXPLAIN VERBOSE SELECT ...;
However, this way of writing options is obsolete and only supported for compatibility reasons. It will not work for new options like “
GENERIC_PLAN”. Compare what the documentation has to say about that:
VERBOSEoptions can be specified, and only in that order, without surrounding the option list in parentheses. Prior to PostgreSQL 9.0, the unparenthesized syntax was the only one supported. It is expected that all new options will be supported only in the parenthesized syntax.
Note that you cannot use
GENERIC_PLAN together with
ANALYZE, since you cannot execute a query if the parameter values are not known.
You can use
GENERIC_PLAN to get an execution plan for a query that contains parameter placeholders such as “
EXPLAIN (GENERIC_PLAN) SELECT * FROM pg_class WHERE relname = $1; QUERY PLAN ═════════════════════════════════════════════════════════════════════════════════════════════ Index Scan using pg_class_relname_nsp_index on pg_class (cost=0.28..8.29 rows=1 width=606) Index Cond: (relname = $1) (2 rows)
Without the option
GENERIC_PLAN, PostgreSQL would complain about the missing parameter values:
EXPLAIN SELECT * FROM pg_class WHERE relname = $1; ERROR: there is no parameter $1 LINE 1: EXPLAIN SELECT * FROM pg_class WHERE relname = $1; ^
EXPLAIN (GENERIC_PLAN) useful?
Two use cases were already mentioned in my previous article:
- parameterized statements you find in the PostgreSQL log
- parameterized statements you find in
For such statements,
EXPLAIN (GENERIC_PLAN) can show an execution plan, which gives you an idea how the statement might perform. For a full analysis, you normally want
EXPLAIN (ANALYZE, BUFFERS) output, but that requires that you find and substitute appropriate parameter values. This can be cumbersome if the statement has many parameters, and perhaps the generic plan is good enough for a first analysis.
EXPLAIN (GENERIC_PLAN) can be useful if you want to examine the generic plan itself. This can help to analyze the performance of a prepared statement. With a prepared statement, the first five executions will use a custom plan, so you have to
EXPLAIN the statement six times to see what the generic plan would be (if PostgreSQL switches to a generic plan at all).
EXPLAIN (GENERIC_PLAN) shows the generic plan with less effort.
You cannot use parameters everywhere
You can use parameter placeholders like
$1 instead of an unknown or variable value. But there are certain restrictions:
- You can use parameters only with the statements
- You can only use parameters instead of constants (literals). You can’t use parameters instead of identifiers (object names) or keywords, among other things.
The following statement is fine:
SELECT date_part($1, d) FROM generate_series($2, current_timestamp, $3) AS d LIMIT $4;
But these statements are not valid:
-- parameters cannot replace identifiers SELECT col FROM $1; -- parameters cannot replace the WHERE clause SELECT col FROM tab $1;
The lack of data type information can make statements ambiguous
Since parameter placeholders don’t have a data type, some valid constructions can become ambiguous:
EXPLAIN (GENERIC_PLAN) SELECT * FROM generate_series($1, $2, $3); ERROR: function generate_series(unknown, unknown, unknown) is not unique LINE 2: SELECT * FROM generate_series($1, $2, $3); ^ HINT: Could not choose a best candidate function. You might need to add explicit type casts.
Here, PostgreSQL cannot deduce which of the functions named
generate_series you mean, even though the following is correct:
PREPARE stmt(integer, integer, integer) AS SELECT * FROM generate_series($1, $2, $3);
In such a case, you should add an explicit type cast to get rid of the ambiguity:
EXPLAIN (GENERIC_PLAN) SELECT * FROM generate_series($1::integer, $2, $3); QUERY PLAN ════════════════════════════════════════════════════════════════════════ Function Scan on generate_series (cost=0.00..10.00 rows=1000 width=4) (1 row)
From PostgreSQL v16 on, consider using
EXPLAIN (GENERIC_PLAN) if you need a quick look at the execution plan of a parameterized statement.
In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.