PostgreSQL Tutorial: EXPLAIN parameterized query

Summary: In this tutorial, you will learn how to use the EXPLAIN statement to display the execution plan of a parameterized statement.

Table of Contents

The syntax of EXPLAIN (GENERIC_PLAN)

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:

Only the ANALYZE and VERBOSE options 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.

Using EXPLAIN (GENERIC_PLAN)

You can use GENERIC_PLAN to get an execution plan for a query that contains parameter placeholders such as “$1”:

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;
                                                       ^

When is EXPLAIN (GENERIC_PLAN) useful?

There are two use cases:

• parameterized statements you find in the PostgreSQL log
• parameterized statements you find in pg_stat_statements

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.

Moreover, 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.

Limitations of EXPLAIN (GENERIC_PLAN)

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 SELECT, INSERT, UPDATE, DELETE and VALUES.
• 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)

Conclusion

From PostgreSQL v16 on, consider using EXPLAIN (GENERIC_PLAN) if you need a quick look at the execution plan of a parameterized statement.