Introducing Query Tuning Workbooks: Safely Tune Postgres Queries on Production with pganalyze

At some point, every engineering team finds itself grappling with the complexity of query optimization. One query may run perfectly well for a particular customer’s parameters, yet degrade performance for another’s dataset. A small tweak that improves latency in staging might have unforeseen consequences in production. Until now, the standard approach to experimentation—perhaps running EXPLAIN ANALYZE in a sandbox environment, copying and pasting results into an editor—hasn’t given developers or DBAs the full, real-world picture they need.

Today, we're excited to introduce the new Query Tuning Workbooks feature in pganalyze. Currently in beta, Query Tuning Workbooks let you benchmark a query that is slow, experiment with rewrites, planner settings, or planner hints, and compare the resulting EXPLAIN plans.

You can either upload EXPLAIN ANALYZE results to a workbook, or you can opt-in to running EXPLAIN ANALYZE through the pganalyze collector, making it easier to test different query variants on production, benchmark the same query with multiple different input parameters, and share the results with your team.

A dedicated environment for tuning Postgres queries

At a high level, the workflow for tuning Postgres queries can be split into three steps:

1. Record details of the execution of a particular query with a given set of input parameters using EXPLAIN (ANALYZE, BUFFERS)
2. Form a hypothesis on which parts of the query are slower than necessary, and how to tweak them
3. Try out a change, such as rewriting the query, or turning of a planner setting to see a different plan

Previously all of these steps required manual running of queries, recording notes in a notepad, and ultimately sharing results with the team that needed to implement the change.

Last year, the pganalyze team sat down, and asked ourselves: What if we designed a dedicated environment for query tuning work?

The resulting Query Tuning Workbook experience is more than just a tool to run EXPLAIN or EXPLAIN ANALYZE. It is a structured workflow where you can experiment with variations of a query, compare query plans for different sets of parameters, and measure real impacts on performance—without the risk of making a premature change and deploying an application change on a live system that wasn't tested sufficiently.

Rather than tossing a single query optimization against your production database and hoping for the best, a Workbook lets you codify best practices: test multiple plans, gather detailed performance metrics, and validate outcomes before you finalize changes.

Measuring the impact of different parameters on query plans

When Postgres comes up with a query plan for a given query, it uses multiple additional inputs to make its choice, and there can be many potential execution plans. It takes into account the size of the tables, the indexes that exist, and most importantly: how frequent a particular value is estimated to be in the given table, based on the column statistics data collected by ANALYZE.

Oftentimes changing the input parameters will lead to different plans — a carefully optimized query might bring significant improvements for customers running small datasets but inadvertently slow down queries for those with large or skewed data distributions.

Query Tuning Workbooks either let you specify parameter values manually, paste in a query that has values inline (for example recorded by your application monitoring tool), or lets you extract parameter values from recorded query samples in pganalyze:

As shown, we automatically pick the most interesting parameter sets from the samples, and let you quickly choose a handful of them to continue working with. These parameter sets will be used for both recording the baseline performance of the query, as well as any variants that modify the query text, or change settings.

One subtle yet critical design decision in the Workbook user experience involves parameter naming. You start out with a normalized version of the query, which might look like this:

SELECT * FROM users WHERE department_id = $1 AND company_id = $2

After you’ve pasted in the query, pganalyze automatically transforms the usual $1, $2, $3 placeholders into named parameters:

SELECT * FROM users WHERE department_id = $department_id AND company_id = $company_id

This might seem like a small quality-of-life improvement, but it makes an enormous difference when analyzing multiple variations of a query over time, or rewriting a query in a way that drops parameters, reorders them, or adds new ones.

Instead of trying to remember whether $3 was the customer_id parameter or something else entirely, you see an actual name that helps you quickly identify what changed between one query run and another. This enhanced readability significantly lowers the barrier to iterating on query variants and makes collaborative tuning sessions more productive.

After we’ve picked parameter sets to test, Query Tuning workbooks make it easy to run EXPLAIN ANALYZE for each of them.

Running EXPLAIN ANALYZE in a safe and structured manner

In many organizations, only a select few engineers have permission to run arbitrary queries in production environments. This is essential for security and stability, yet it often slows down query tuning workflows.

The new Workbook feature adds a layer that allows more members of the engineering or data teams to propose and test optimizations—such as join order changes or parameter adjustments—without granting them direct production access. Through the Collector Workflow in Query Tuning Workbooks, you can extend controlled experimentation capabilities to more team members, while preserving strict oversight and protecting sensitive data.

From the user's perspective, it's surprisingly simple: We take the chosen parameter values, for a query of our choice, and run EXPLAIN ANALYZE for each set of parameters:

Behind the scenes, pganalyze does the work and enables safe production testing of performance optimization. Instead of requiring engineers to manually log in to the production database, the pganalyze collector can now optionally run EXPLAIN ANALYZE for you, on-demand. If you’re interested in a deep dive on the security model, you can find all the details in our documentation.

Query Tuning Workbooks enable the whole engineering team to try rewriting queries, tweaking parameters, and observing actual performance data for individual queries. The result is a faster, more reliable workflow that leads to better-informed tuning decisions and fewer bad surprises.

Viewing plans, EXPLAIN insights, and comparing plans

After the initial baseline has been recorded, we can see an overview of all the different plans for each parameter set, as well as review each one of them. This utilizes the existing Plan Fingerprinting in pganalyze to highlight if different plans were in use, or the same plan was used for all parameter sets. In this example we can see 4 different plans being utilized:

To find out what the difference is, you can use the new Compare Plans feature to see even subtle differences between two plans at a glance. Perhaps the optimizer chose a different join ordering or switched join types, resulting in a completely different performance profile. By seeing these differences laid out clearly, you can then make a decision on what different variants to test.

Improving performance through query variants

Based on the data recorded in the baseline benchmark, we can use Query Tuning Workbooks to easily try out a hypothesis on how a query could be made faster.

One common debugging technique is to test out what happens when the Postgres planner is incentivized to not use certain plan nodes. This can be as simple as turning off sequential scans with SET enable_seqscan = off, or not using certain JOIN types, e.g. by utilizing SET enable_nestloop = off. We can do this directly through the Workbooks interface when creating a variant:

But, of course, the more interesting tests to run are those that modify the query text. Thanks to named parameters, it’s easy to take parts of the query, or change a WHERE condition to confirm whether that could improve performance. In this example, we’ve identified about a 45% performance improvement in some cases by rewriting the query to remove a duplicate function call:

Take a full tour of the new features

Want to see the full experience one more time? We put together a complete demo of tuning an example query:

Coming soon: Query Tuning Advisor

Of course, coming up with variants can be the hard part, and requires deep Postgres expertise beyond the simpler cases. We’ve talked about different problematic cases in the past, such as:

• Postgres Planner Quirks: JOIN Equivalence Classes and IN/ANY filters
• Postgres Planner Quirks: How to fix bad JSONB selectivity estimates
• Postgres Planner Quirks: The impact of ORDER BY + LIMIT on index usage
• Faster query plans with Postgres 16: Incremental Sorts, Anti-JOINs and more
• EXPLAIN (ANALYZE, BUFFERS) and interpreting shared buffers hit in Nested Loops

But what if we could automatically detect these cases and flag them for a test?

We're not ready to share more on this just yet, but are excited to soon introduce you to our new Query Tuning Advisor that utilizes Workbooks and makes it easy for you to detect and fix common Postgres query plan issues.

In summary

We’re thrilled to invite pganalyze users to get started with the Query Tuning Workbooks, available in beta starting today for current pganalyze Production, Scale and Enterprise Cloud customers.

By default, only Admin-level users have the new "Tune Queries" permission, enabling them to create workbooks and use the associated tuning features. Other users without this permission can still view any existing workbooks, allowing for collaborative review without granting full edit access.

To streamline the process of gathering query plans, you can set up the pganalyze collector to automatically run EXPLAIN ANALYZE for you—just ensure you’re on the latest supported collector version and follow our documentation to configure it securely. If you prefer a more manual approach, that option remains available, giving your team full flexibility in how they adopt and integrate Query Tuning Workbooks into your existing workflows.