EXPLAIN (GENERIC_PLAN): New in PostgreSQL 16

 

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 EXPLAIN option 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 EXPLAIN (GENERIC_PLAN)

You can only use the new option in a parenthesized options list, like this:

Many people are still used to the old, simple way of writing options, like

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”:

Without the option GENERIC_PLAN, PostgreSQL would complain about the missing parameter values:

When is 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 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:

But these statements are not valid:

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:

Here, PostgreSQL cannot deduce which of the functions named generate_series you mean, even though the following is correct:

In such a case, you should add an explicit type cast to get rid of the ambiguity:

Conclusion

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.