Small but mighty optimizations: how pgpro_planner rescues struggling queries

Hey there! I'm Alena Rybakina, and I've been working as a developer at Postgres Professional for four years now, contributing to vanilla PostgreSQL along the way. In this article, I'll walk you through the pgpro_planner extension that helps the standard optimizer find better execution plans when it typically stumbles.

Working with optimizer code, I frequently encounter queries whose execution time jumps from milliseconds to several minutes or even hours. While I call them "small but mighty optimizations" in talks, each one represents real production pain.

The pgpro_planner extension grew directly from such stories. At Postgres Professional, we use pgpro_planner as an experimental playground for planner improvements: we test ideas in Postgres Pro Enterprise, and push the most successful solutions upstream to vanilla PostgreSQL. This already happened with the IN (VALUES ...) optimization in PostgreSQL 18, which I'll discuss shortly.

What does the optimizer do and why does it struggle?

Every PostgreSQL query starts as human-readable text like SELECT * FROM users;, but inside the engine it quickly transforms into a query tree. Gone are the asterisks and SELECTs — instead, you get nodes with specific tables, columns, conditions, and join operations.

Next, the planner takes over, attempting to choose the optimal (in its terms—cheapest) plan among all possibilities. The cardinality estimator needs to figure out how many rows are expected at each step, from simple table scans to joining two relations. This determines how expensive an operation will be.

Obviously, fewer rows to process means cheaper operations. But underestimating the workload is equally problematic.

Imagine expecting a delivery of one burger, but the courier shows up with a refrigerator full of them. You'd need to figure out where to store everything (additional monetary costs) and who to share the burgers with (additional energy expenditure).

Cost (a conditional metric of operation difficulty) consists of two parts: startup and execution. For example, Nested Loop needs no preparatory operations—it's just nested loops. For each tuple from one dataset, we search for matching tuples in another, calculating total cost. This can be laborious for large datasets, but small ones process instantly.

Hash Join works differently: since it stores unique values from one dataset as a hash table, you simply look up matching values by key. Here you'll spend startup cost creating the hash table, as it requires both memory for storage and time for construction. This investment pays off when datasets stretch to millions of values. The total cost represents executing the main algorithm—checking which values from the dataset exist in the hash table.

It's like cooking dinner: first you need to enter the kitchen, get ingredients, and prepare your workspace—only then can you peel, chop, and fry.

The optimizer works similarly: startup cost handles data structure preparation, like hash tables for Hash Join, while total cost sums preparation and actual execution.

If you underestimate cardinality and cost, the optimizer picks a plan that seems "cheap" from its perspective. In reality, this turns into Nested Loop where Hash Join would be better for huge datasets, or Seq Scan instead of Index Scan for small volumes.

And if the query tree is organized poorly, some potentially efficient algorithms aren't even considered—the necessary conditions are "hidden" in forms the optimizer can't process effectively.

These are exactly the cases I'm trying to "fix" in pgpro_planner: the extension rewrites optimizer-unfriendly chunks of the query tree into more digestible forms before the main planner chooses a plan.

IN (VALUES ...) breaks plans

The first pgpro_planner function emerged from enterprise application queries that heavily use IN (VALUES ...) expressions. In the real case, this was a query against a table with tens of millions of rows and several field filters using IN (VALUES (...), (...)) for bytea and numeric types.

Before updating statistics, the query executed in under a millisecond. After the update—about 7.5 seconds. Looking at the plan through EXPLAIN (ANALYZE, BUFFERS), you immediately see that most time goes into Nested Loop Semi Join with materialization of small temporary tables built from VALUES.

Nested Loop Semi Join (cost=0.56..3,048,376.96   rows=7,712,449 actual time=7,463.781..7,463.782   rows=0   loops=1)
  Join Filter: t1._fld89915 = "*VALUES*".column1
  -> Nested Loop Semi Join (cost=0.56..2,007,196.24   rows=8,676,505 actual time=7,463.780..7,463.781   rows=0   loops=1)
       Join Filter: t1._fld91907rref = "*VALUES*_1".column1
       -> Index Scan using _inforg89913_1x1 on t1 (cost=0.56..1,616,753.47   rows=13,014,758 actual time=10.031..4,963.070   rows=13,015,820   loops=1)
       -> Materialize (cost=0.00..0.04   rows=2   width=32  actual time=0.000..0.000   rows=2   loops=13,015,820)
       -> Values Scan on "*VALUES*_1" (cost=0.00..0.03   rows=2   width=32 actual time=0.008..0.010   rows=2   loops=1)
  -> Materialize (cost=0.00..0.14   rows=8   width=32)  (never executed)
  -> Values Scan on "*VALUES*" (cost=0.00..0.10   rows=8   width=32)  (never executed)
--------------------
Planning Time: 1.046 ms
Execution Time: 7,463.840 ms

The problem is PostgreSQL treats T1._Fld89915 IN (VALUES (CAST(6 AS NUMERIC)), ...) as a condition requiring temporary table construction and Semi Join execution between the main table and this VALUES table. For each value list, a separate Values Scan is created, then materialized. The index on T1._Fld89915 provides almost no benefit: comparison happens inside the join filter, not in the index condition.

It's like searching for people using two sticky notes in a massive archive: instead of opening the alphabetical index to immediately find needed names, you're forced to go through all 13 million records, checking each one for matches.

The solution Andrey Lepikhov and I found for pgpro_planner turned out beautifully simple: collect all constant VALUES into an array and rewrite the predicate as x = ANY(array).

Original query:

SELECT T1._Fld89914, ... FROM _InfoRg89913X1 T1
WHERE T1._Fld89915 IN (VALUES (CAST(6 AS NUMERIC)), ...)
  AND T1._Fld91907RRef IN (VALUES ('\\202...'::bytea), ...);

Transformed to:

SELECT T1._Fld89914, ... FROM _InfoRg89913X1 T1
WHERE T1._Fld89915 = ANY('{6,7,8,9,10,11,12,15}'::numeric[])
  AND T1._Fld91907RRef = ANY('{"\\x5c...","\\x5c..."}'::bytea[]);

After this transformation, the optimizer recognizes the familiar = ANY(array) expression and can build clean Index Scan or Bitmap Index Scan on the corresponding field. The plan loses Nested Loop Semi Join and temporary VALUES tables, the index appears in Index Cond, and execution time drops from 7 seconds to milliseconds.

This functionality eventually got committed to PostgreSQL 18.

Limitations for IN (VALUES ...) → = ANY(array)

Like any query tree transformation, IN (VALUES ...) converts to = ANY(array) only under certain conditions.

Transformation doesn't apply if VALUES contains:

1. Volatile functions.

2. Non-constant expressions or main table columns — this could cause more repeated calculations or change semantics, so better keep the original join.
   

   SELECT ten FROM onek t WHERE unique1 IN (VALUES (0), (unique2));
   

3. Complex types where correctly building an array isn't straightforward.
   

   SELECT * FROM onek
   WHERE (unique1,ten) IN (VALUES (1,1), (20,0), (99,9), (17,99))
   

4. No suitable equality operator between column type and array type.

5. NULL among constants in the value list.
   

   SELECT ten FROM onek WHERE sin(two)+four IN (VALUES (sin(0.5)), (NULL), (2));
   

6. LIMIT, OFFSET, or ORDER BY in the expression containing IN (VALUES ...) — such constructs can affect row order and count, while conversion to ANY(array) changes the execution model.

Simple x + 0 breaks indexes

The second pgpro_planner function handles expressions like x + 0, x * 1, x / 1, and x - 0. Obviously these all equal x, but PostgreSQL's optimizer doesn't need to think so—meaning it might not use indexes.

Consider the query:

EXPLAIN ANALYZE SELECT * FROM t WHERE x + 0 > 900;

Without additional logic, the planner sees condition x + 0 > 900 and doesn't understand it hides a simple range on x. Result: Seq Scan on table t with row-level filtering, no index usage. The plan shows sequential scanning with hundreds of rows removed by filter.

But reformulating the condition as x > 900 enables Index Only Scan, reading only needed data from the index without touching the table itself.

Filtering shifts from row level to index access level, and execution time drops dramatically, especially on large tables.

My colleague Vlada Pogolezhskaya added a step to pgpro_planner for simplifying trivial arithmetic expressions before the main optimizer chooses a plan. Sometimes it's just "unpacking" conditions: understanding that x + 0 equals x lets us rewrite the filter and allow the planner to use indexes as if the original expression was written without "noisy" arithmetic.

Limitations

This optimization has constraints too:

1. Presence of volatile functions where repeated evaluation might yield different values.

2. Complex constructs returning 0 like x + 100 - 100—though humans clearly see the result is x again, implementing such simplification would be disproportionately complex compared to gains
   

select id from data where a + 100 - 100 = b;
-----------------------------------
Seq Scan on data
Filter: (((a + 100) - 100) = b)

Correlated subqueries and memoize: escaping routine

The third pgpro_planner function relates to correlated subqueries and the Memoize operator. First, let's understand correlated subqueries.

Example:

The plan looks like: first Bitmap Heap Scan on main_tbl for m.thousand range, then for each main_tbl row, SubPlan executes doing Seq Scan on sub_tbl and aggregating sum(s.x) for condition s.x = m.x.

If m.x values frequently repeat, the subquery with identical parameters executes again and again. PostgreSQL without additional optimizations honestly scans sub_tbl every time, even if it calculated sum(s.x) for the same value yesterday or a minute ago. Watching this in plans hurts.

Essentially, our subquery (SELECT sum(s.x) FROM sub_tbl s WHERE s.x=m.x) logically depends on the outer query row (m.x parameter), but factually different rows get identical parameters (condition s.x=m.x, s.x is unique).

Memoize solves this through caching. The plan shows a Memoize node with key m.x: for each new m.x value, its result saves to a hash table, and repeated accesses with same m.x just grab the subquery result from cache without execution.

We add Memoize node in pgpro_planner only where truly beneficial. This requires checking several conditions:

• Subquery must be correlated: no dependencies on main query means no point in caching
• No min or max aggregates inside
• Cache must support through suitable hash operator
• Plan cost with Memoize should be lower than without caching

In the last condition, we estimate how many times the subquery gets called with different parameters and what one such call costs, then compare this with hash table creation and usage costs. If startup_cost + calls * per_call_cost without Memoize exceeds cost with caching, we add Memoize. Otherwise the subquery stays as is.

Andrey Lepikhov and Petr Petrov worked on this functionality with me.

Correlated subquery optimization example in Join Order Benchmark

Consider an example where a bad plan leads to tens of millions of repeated calculations and multiplicative execution time growth.

The query tries finding movies where dates in different columns (info and note) don't match. We're looking for movies where the year extracted from the info field equals the maximum year from the note field of the movie_companies table for the same movie:

EXPLAIN ANALYZE
SELECT t.title
  FROM movie_info mi
  JOIN title t ON
    t.id = mi.movie_id
  WHERE substring(mi.info from '\d{4}')::int = (
    SELECT max(substring(mc.note from '\d{4}')::int)
    FROM movie_companies mc
    WHERE mc.movie_id = mi.movie_id);

Key moment is the expression:

WHERE substring(mi.info from '\d{4}')::int = (
    SELECT max(substring(mc.note from '\d{4}')::int)
    FROM movie_companies mc
    WHERE mc.movie_id = mi.movie_id
)

Original query plan (without pgpro_planner):

Nested Loop (actual time=9.859..496524.556 rows=685213 loops=1)
  -> Seq Scan on movie_info mi
      (actual time=0.00..316888.153 rows=685213 loops=1)
      Filter: ( (substring(info, '\d{4}'::text))::integer = (SubPlan 1) )
      Rows Removed by Filter: 14150507
      SubPlan 1
      -> Aggregate
            (actual time=0.028..0.028 rows=1 loops=14835720)
        -> Index Scan on movie_companies mc
              (actual time=0.010..0.014 rows=5 loops=14835720)
              Index Cond: (movie_id = mi.movie_id)
  -> Index Scan using title_pkey on title t
      (actual time=0.011..0.011 rows=1 loops=685213)
      Index Cond: (id = mi.movie_id)

Planning Time: 1.693 ms
Execution Time: 496690.239 ms

The mi.movie_id repeats very frequently. We're searching for the same value repeatedly—redundantly scanning by index. This shows in a key metric: the same subquery executes 14,835,720 times. This isn't a bug; it's how the regular planner works.

Nested Loop goes through movie_info and for each row "pulls" the movie_companies index, ultimately getting 14 million repetitions later to Execution Time: 496690 ms—that's half a second of redundant work, nearly 0.5s just because repeated calculations aren't cached.

The optimizer didn't recognize repeated calculations; for it, the subquery is an operator needing execution at each access regardless of whether this value appeared before. The reason—absence of a memoization mechanism.

pgpro_planner introduces a Memoize node eliminating the fundamental problem of repeated calculations. Memoize—a small but mighty Postgres optimization—saves subquery execution results for each unique parameter and returns them from cache when parameters repeat.

Query plan with pgpro_planner:

Nested Loop  (actual time=1.155..115353.599 rows=685213 loops=1)
  -> Seq Scan on movie_info mi
      (actual time=1.086..106624.266 rows=685213 loops=1)
      Filter: ( (substring(info, '\d{4}'::text))::integer = (SubPlan 1) )
      SubPlan 1
      -> Memoize
            (actual time=0.003..0.003 rows=1 loops=14835720)
            Cache Key: mi.movie_id  Cache Mode: binary
            Hits: 12366894  Misses: 2468826  Evictions: 2385405  Overflows: 0  Memory Usage: 8193kB
        -> Aggregate
              (actual time=0.014..0.014 rows=1 loops=2468826)
          -> Index Scan on movie_companies mc
                (actual time=0.009..0.011 rows=1 loops=2468826)
                Index Cond: (movie_id = mi.movie_id)
  -> Index Scan using title_pkey on title t
      (actual time=0.011..0.011 rows=1 loops=685213)
      Index Cond: (id = mi.movie_id)

Planning Time: 1.886 ms
Execution Time: 115528.841 ms

Memoize sits before the subquery: key is mi.movie_id, value is subquery result (aggregate max(...)). Cache stores in a small hash table (8 MB in example). On first access to specific movie_id, Memoize runs SubPlan and saves result in cache. Subsequent accesses with same key return result instantly, without repeated Index Scan.

What we get:

• Hits: 12,366,894 times subquery didn't execute
• Misses: 2,468,826 times system accessed index
• Evictions: 2,385,405 values evicted due to cache size limits, but this didn't affect final correctness

Thanks to Memoize, total time dropped more than four-fold: from 496,690 ms to 115,528 ms.

How to enable pgpro_planner without breaking production

From a user perspective, pgpro_planner is a regular PostgreSQL optimizer extension. Supported in Postgres Pro Enterprise starting from version 16 and vanilla PostgreSQL of the same versions.

You can load pgpro_planner two ways:

• Run command LOAD pgpro_planner inside a session (version 16+) and enable extension with pgpro_planner.enable = true. No server restart required: the extension connects as a planner hook and starts intercepting queries in current connection.
• Add pgpro_planner to shared_preload_libraries with server restart and enable extension with pgpro_planner.enable = true.

The extension is configurable via hooks—enabling/disabling functions inside:

• enable_values_transformation — enables/disables VALUES to ANY conversion
• enable_simplify_trivials — simplifies trivial arithmetic expressions
• memoize_subplan — controls subquery caching

This configuration provides flexibility: in production you can keep only the functionality piece you truly need enabled. For example, keep Memoize for heavy reporting queries while disabling VALUES transformation if auto-generated queries rarely use them. Or disable trivial expression simplification if you deliberately insert them to force Seq Scan during debugging.

Where pgpro_planner helps and where it doesn't

All three functions—IN (VALUES ...) transformation, trivial expression simplification, and Memoize—solve specific problem classes our team encountered in real queries:

• In enterprise application queries, pgpro_planner removes redundant Nested Loop Semi Join and lets the planner use indexes via familiar = ANY(array) form. This directly cuts heavy reporting query execution time, especially on tables with tens of millions of rows.
• In auto-generated queries with trivial arithmetic, converting x + 0 etc. to x removes visual noise, restores ability to build index plans, and improves cardinality estimation.
• Memoize wins where correlated subqueries access identical parameter values. Instead of dozens of identical Seq Scans on subtables, you get one fetch and many cheap cache accesses. pgpro_planner decides based on cost: if subquery runs once, Memoize makes no sense.

All three optimizations have limitations though: volatile functions, complex types, missing suitable equality operators, LIMIT and ORDER BY on paths to transformable expressions.

If your projects have queries that "stall" for no good reason, write about them in comments—maybe our planner can crack the secret of their sluggishness.