CYBERTEC PostgreSQL Logo

EXPLAIN (GENERIC_PLAN): New in PostgreSQL 16

04.2023 / Category: / Tags: |
A generic_plan with houses, a street and a river - not very useful
© 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 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.

8 responses to “EXPLAIN (GENERIC_PLAN): New in PostgreSQL 16”

  1. Question around JDBC driver: when we execute any query using jdbc namedTemplate, does it always uses PreparedStatement under the hood?
    If yes, then I think the query plan can switch to a generic plan for an API call that internally uses JDBC namedTemplate to fetch results.

    Thanks for the article.

    • I have never heard of a namedTemplate in JDBC, so there is little I can say. Are you sure that you are talking about JDBC?

  2. Oh, actually I was referring to NamedParameterJdbcTemplate class. Hope that helps. (I think my question went out of the scope of this blog, sorry for that)

  3. Sure, thanks for looking into it. I love your blogs. They are always so clear, understandable and covering every important detail.


  4. You can only use parameters instead of constants (literals).

    Meaning that a prepared statement with prepared statements and constants is forbidden? If so, disappointing, but better than not having EXPLAIN (GENERIC_PLAN).

    • Sure you can have both constants and parameters. If you look at the context, my statement means that you can use parameters instead of constants, but not instead of identifiers or keywords. Try for example EXPLAIN (GENERIC_PLAN) SELECT relfilenode FROM pg_class WHERE relname = 'pg_class' AND relnamespace = $1;

Leave a Reply

Your email address will not be published. Required fields are marked *

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    ©
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram