StormaticsStormatics

PostgreSQL Generic vs Custom Plans

Key Takeaways

  • PostgreSQL can run a prepared statement with either a generic or a custom plan, and the choice affects query performance.
  • A custom plan is created using the actual parameter value, so it can adapt to the data, but it costs planning time on every execution.
  • A generic plan is reused across parameter values, which reduces planning overhead but cannot adapt when values return very different amounts of data.
  • Data distribution is the main reason a prepared query is fast for some values and slow for others.
  • With plan_cache_mode = auto, PostgreSQL decides whether reusing a generic plan is worth it.
  • Compare both plan types with EXPLAIN EXECUTE and plan_cache_mode before adding indexes or forcing custom plans.

A query can be fast with one parameter value and unexpectedly slow with another, even when the SQL query itself has not changed. This can become confusing when the query is executed through a prepared statement, because PostgreSQL may not always use the same type of execution plan. In this situation, PostgreSQL can use either a custom plan or a generic plan. Understanding the difference between them is important when investigating queries that perform well for some parameter values but poorly for others.

Understanding the Problem

Consider a prepared query that searches for orders using a customer_id. Some customers may have only a few orders, while others may have thousands or millions. For a customer with only a few rows, PostgreSQL may find an index scan more efficient, while for a customer with a very large number of matching rows, scanning a larger part of the table may be more efficient. The SQL query is the same in both cases, but the amount of data returned can be very different. This is where the choice between a custom and generic plan becomes important.

For example, a prepared statement might look like this:

PREPARE customer_orders(int) AS
SELECT * FROM orders WHERE customer_id = $1;

The important part is not the query itself, but how PostgreSQL decides to execute it for different customer_id values.

PostgreSQL generic vs custom plans for a prepared statement: a custom plan adapts to each customer_id, a generic plan is reused and may be slow for large customers
PostgreSQL generic vs custom plans for a prepared statement: a custom plan adapts to each customer_id, a generic plan is reused and may be slow for large customers

What Is a Custom Plan?

A custom plan is created using the actual parameter value provided when the query is executed. For example, if the query is executed for a customer who has only a small number of orders, PostgreSQL can see that parameter value and create a plan that is suitable for that specific case. This allows PostgreSQL to take the actual data distribution into account. If one customer has 10 rows and another has 10 million rows, PostgreSQL does not have to assume that both queries will behave in the same way.

The main advantage of a custom plan is that it can adapt to the parameter value. The downside is that PostgreSQL has to spend time planning the query. If the same prepared statement is executed thousands of times, repeatedly creating custom plans can add noticeable planning overhead.

What Is a Generic Plan?

A generic plan is a reusable execution plan that does not depend on one specific parameter value. Instead of creating a new plan based on every parameter, PostgreSQL can create a plan that is considered reasonable for repeated executions of the prepared statement. This can reduce planning overhead, especially when the query is executed frequently.

However, there is a trade-off. If different parameter values produce very different amounts of data, one reusable plan may not be efficient for every execution. A plan that works well for a customer with a small number of orders may not be ideal for a customer with millions of orders.

Generic Plan vs Custom Plan

The main difference comes down to how PostgreSQL creates and uses the execution plan. With a custom plan, PostgreSQL considers the actual parameter value when planning the query. This gives the planner more information about the expected result and allows it to choose a plan that better fits that particular execution.

With a generic plan, PostgreSQL creates a reusable plan that can be used for different parameter values. This reduces repeated planning work, but the plan cannot adapt specifically to each parameter value.

Custom plans are therefore more useful when parameter values have very different data characteristics, while generic plans can work well when the query behaves similarly across different executions.

Why Data Distribution Matters

Data distribution is one of the main reasons this issue appears. Suppose a table contains customer orders. Most customers may have only a few orders, but a small number of customers may have millions. If PostgreSQL knows the actual customer ID, it can create a plan based on the expected number of matching rows. But if it uses a generic plan, the same plan may be reused for customers with completely different amounts of data.

This is sometimes called a parameter-sensitive workload because the performance of the query depends heavily on the value supplied to the parameter. The SQL itself has not changed. What has changed is the amount and distribution of data associated with the parameter.

Why PostgreSQL Does Not Always Use Custom Plans

At first, using a custom plan for every execution may sound like the best approach because PostgreSQL gets the actual parameter value each time. But planning also consumes CPU and time. If a query is executed very frequently and the optimal plan is almost the same for every parameter value, repeatedly planning the query may waste resources.

In that situation, reusing a generic plan can reduce planning overhead and make the overall workload more efficient. PostgreSQL therefore has to balance planning cost against execution cost. A custom plan may take more time to create but execute faster, while a generic plan may take less planning time but sometimes result in slower execution.

How PostgreSQL Handles Generic and Custom Plans

PostgreSQL normally uses plan_cache_mode = auto. With this setting, PostgreSQL can decide whether a prepared statement should use custom or generic plans based on the planning and execution characteristics of the query. PostgreSQL does not simply choose one approach for every prepared statement. The decision depends on whether reusing a generic plan is considered beneficial compared with continuing to create custom plans.

This is important because forcing one approach globally can sometimes solve one query while making other queries worse.

How to Investigate the Problem

When a prepared query suddenly becomes slow, the first step should be to check whether different parameter values produce different execution plans. Look at the values being passed to the query and compare how much data each value is expected to return. If one parameter produces a few rows while another produces a very large result set, there may be a strong reason for PostgreSQL to prefer different plans.

The execution plan should also be examined. Pay attention to whether PostgreSQL is using an index scan, sequential scan, or another access method. Estimated rows, actual rows, execution time, and buffer usage can help show whether the selected plan matches the actual workload.

For example, you can use EXPLAIN EXECUTE to inspect a prepared statement:

EXPLAIN EXECUTE customer_orders(42);

You can then test another parameter value and compare the plans. The goal is not simply to find a “bad” plan. The goal is to understand why PostgreSQL selected that plan and whether the same plan is being reused for parameter values with very different data characteristics.

Checking Generic and Custom Plan Usage

PostgreSQL also provides information about prepared statements through pg_prepared_statements. This can help identify whether a prepared statement has been using generic plans, custom plans, or a combination of both.

For a production investigation, this information can be useful because it gives a better picture of what PostgreSQL is actually doing, so you do not have to rely only on the SQL text. It is also important to remember that plan behavior can change when statistics, indexes, table contents, or PostgreSQL configuration change.

Testing the Difference

When investigating a suspected plan problem, PostgreSQL allows you to test both behaviors using plan_cache_mode. For example, you can temporarily force a custom plan with:

SET plan_cache_mode = force_custom_plan;

You can also test the generic plan:

SET plan_cache_mode = force_generic_plan;

This makes it easier to compare both approaches directly. If the custom plan is consistently much faster for certain parameter values, that is a useful signal that the query may be sensitive to parameter values. If both approaches perform similarly, forcing custom plans may provide little benefit while adding unnecessary planning overhead.

A Practical Production Scenario

Imagine an application that frequently searches customer orders using a prepared statement. During development, the test database may contain relatively uniform data, so the query performs well with a generic plan. After deployment, the production database may have a very different distribution. A few customers could have extremely large numbers of orders while most customers have only a small number.

Now the same prepared statement may behave differently depending on which customer ID is supplied. Some executions may remain fast, while others become noticeably slower. In this situation, simply looking at the SQL query may not reveal the problem. The important part is understanding the relationship between the parameter value, data distribution, selected execution plan, and planning cost.

Seeing this pattern in production? Our Forward Deployed Engineers compare plans against your real workload and data distribution to find why some executions slow down. See PostgreSQL Performance Optimization and Scaling.

What Should You Avoid?

A common mistake is to immediately add an index when a prepared query becomes slow. An index may help, but it does not automatically solve a generic-versus-custom-plan problem. The real issue may be that PostgreSQL is reusing a plan that is suitable for some parameter values but inefficient for others.

Another common approach is to immediately force custom plans. That can improve execution time for parameter-sensitive queries, but it also increases planning work. If the query runs very frequently, that additional planning cost can become significant. The better approach is to first understand the workload and compare the actual behavior of both plan types.

Seen a quick fix hide a slow query instead of solving it? Join our live webinar on 25 November, Killing the Cron Job: Rescuing a Production Odoo Workload on PostgreSQL, to see how a team measured production traffic to find the real cause.

The Main Idea

The concept is relatively simple. A custom plan considers the current parameter value when creating the execution plan, allowing PostgreSQL to adapt the plan to the specific execution. A generic plan is designed to be reused across different parameter values, which can reduce planning overhead but may not be ideal when different parameter values produce very different results.

Neither approach is automatically better in every situation. If parameter values have similar data characteristics, a generic plan may work well and reduce planning overhead. If parameter values produce very different amounts of data, custom plans may provide better execution performance for those cases. The important part is finding the right balance between planning time and execution time.

Final Thoughts

Generic and custom plans are an important part of understanding PostgreSQL prepared statements and query performance. A query that becomes slow for only certain parameter values may not have a problem with the SQL itself. The issue can come from how PostgreSQL chooses and reuses its execution plan.

When investigating this type of problem, look at the parameter values, data distribution, execution plans, planning time, execution time, and buffer usage together. The main lesson is simple: do not judge a prepared query only by its SQL text. Look at how different parameter values affect the plan and the actual execution.

Understanding this behavior can make it much easier to diagnose performance problems in production PostgreSQL systems without immediately changing indexes, rewriting queries, or forcing a particular plan type.

Not sure which queries are holding your database back? A PostgreSQL Architecture and Performance Health Check shows where plans, statistics and indexes are costing you time, before it turns into an incident.

Leave A Comment