Skip to content
Swingback, home

BlogEngineering

Postgres generic plans: fast five times, slow on the sixth

Why a PostgreSQL prepared statement turns slow on its sixth run: generic plans, optional filters estimated at one row, and the setting that fixed it.

By Elie Baier · · Updated · 7 min read

If a query is fast the first five times a connection runs it and slow from the sixth, PostgreSQL has most likely switched to a generic plan: a plan made once, without your parameters' values, and kept for every call after. With optional filters, such as $2 IS NULL OR board_id = $2, that plan expects a single row, and the cheapest plan for one row can be the worst one for thirty thousand. The fix that worked for me is one setting on every connection, plan_cache_mode = force_custom_plan. The rest of this post shows where the sixth run comes from, how to see it in your own database, and a second planner trap I found the same day, which that setting did not fix.

Where the sixth run comes from#

A prepared statement is parsed once and run many times with different values. Before PostgreSQL runs one, it has two choices: plan it again with this call's values (a custom plan), or reuse a plan made without them (a generic plan). By default (plan_cache_mode = auto), the PostgreSQL docs describe the rule: the first five executions get custom plans, then PostgreSQL makes a generic plan, and if its estimated cost isn't much higher than the custom plans' average, it keeps the generic one from then on.

You may be preparing statements without knowing it. In Go, pgx v5 does it by default: its default mode, QueryExecModeCacheStatement, prepares every query and caches the statement on the connection (512 of them, unless you change it). On a busy pool, every query in your application reaches its sixth run within seconds of a deploy. Any driver that caches prepared statements gets there too.

Nothing about this is a bug. The generic plan saves planning time, and for a query whose best plan doesn't depend on its values, it's the right choice. The trouble starts when the plan does depend on them.

Why optional filters come out as one row#

Swingback's boards list posts in several orders (trending, top, newest), with filters that are all optional: a board, a set of statuses, tags, a company, an author. Writing one query per sort, with the filters as parameters that may be null, keeps it to a few queries instead of one per combination. Simplified, a list looks like this:

SQL
SELECT p.id, p.title, a.name, b.name
FROM posts p
JOIN authors a ON a.workspace_id = p.workspace_id AND a.id = p.author_id
JOIN boards b ON b.workspace_id = p.workspace_id AND b.id = p.board_id
WHERE p.workspace_id = $1
  AND ($2::int IS NULL OR p.board_id = $2)
  AND ($3::text IS NULL OR p.status = $3)
  AND ($4::int IS NULL OR p.author_id = $4)
ORDER BY p.trending_score DESC, p.id DESC
LIMIT 20;

A custom plan knows that $1 is the largest workspace and that the other filters are off. A generic plan knows neither. It estimates the rows of an average workspace, then multiplies by a guess for each $n IS NULL OR …, since it can't tell which filters a call will use. You can ask PostgreSQL 16 or later for that plan directly with EXPLAIN (GENERIC_PLAN). On a test database with one workspace of 36,000 posts beside ten thousand small ones:

Text
EXPLAIN (GENERIC_PLAN) SELECT … WHERE p.workspace_id = $1 AND ($2::int IS NULL OR …) …

 Limit  (cost=0.42..27.79 rows=1 width=12)
   ->  Index Scan using posts_trending on posts p  (cost=0.42..27.79 rows=1 width=12)
         Index Cond: (workspace_id = $1)
         Filter: ((($2 IS NULL) OR (board_id = $2)) AND (($3 IS NULL) OR (status = $3)) AND …)

EXPLAIN SELECT … WHERE p.workspace_id = 1 …

 Limit  (cost=0.42..1.17 rows=20 width=12)
   ->  Index Only Scan using posts_trending on posts p  (cost=0.42..1357.16 rows=36157 width=12)

One row expected, 36,157 there. In that small test the generic plan still happened to walk the right index. In Swingback's real lists, with more joins and more indexes to choose from, the plan made for one row went through every post of the workspace, joined each one to its author, and sorted them all to return twenty.

What it cost#

I found it in a load test on staging, with ten thousand generated workspaces. On a workspace of 36,000 posts, the trending list took 155 ms a page instead of 1.3 ms, and that one list took 60% of the database's time. The tests had never seen it: a test's workspace is small, and most tests don't run the same statement six times on one connection.

The signature is worth remembering: a query that is fast in psql and slow in the application. psql sends the query with its values written in, so you always see the custom plan. Check what your application's connections are doing instead: from PostgreSQL 14 on, pg_prepared_statements counts the generic and custom plans each statement got.

SQL
-- On the application's connection, or in a session that PREPAREs the same statement:
SELECT name, generic_plans, custom_plans FROM pg_prepared_statements;

The fix: plans made with the values#

plan_cache_mode (PostgreSQL 12 and later) takes auto, force_custom_plan or force_generic_plan. With force_custom_plan, every execution is planned with its own values, and you keep everything else prepared statements give you: one parse, typed parameters, no SQL injection. I set it on every connection the application opens. With pgx:

Go
// Every pool, the API's, the worker's and the tests', goes through this.
func Configure(cfg *pgx.ConnConfig) {
	if cfg.RuntimeParams == nil {
		cfg.RuntimeParams = map[string]string{}
	}
	// A connection string that names the setting keeps its own value.
	if _, set := cfg.RuntimeParams["plan_cache_mode"]; !set {
		cfg.RuntimeParams["plan_cache_mode"] = "force_custom_plan"
	}
}

The same works without code: ?plan_cache_mode=force_custom_plan in a pgx connection string (unknown parameters are sent as run-time settings), or ALTER ROLE app SET plan_cache_mode = force_custom_plan for every session of that role.

What it costs is planning each execution: a fraction of a millisecond for queries like these. If you have statements whose planning is expensive, such as joins across many partitions, set it per session or role rather than everywhere, and measure.

I considered two other ways. One was to rewrite each list in steps the planner can't reorder, as Swingback's search already is: eight queries to keep that way, and every query written later still left to chance. The other was to stop preparing statements, which pgx can do (QueryExecModeExec), at the price of its checks of the parameters' types. One setting on every connection was simpler than both.

A test that fails if it comes back#

A setting is easy to lose: a new pool made by hand, a connection string copied from elsewhere. So a test runs each list ten times on one connection, inside one transaction, and counts the rows PostgreSQL read from the posts table:

SQL
SELECT coalesce(sum(coalesce(seq_tup_read, 0) + coalesce(idx_tup_fetch, 0)), 0)
FROM pg_stat_xact_user_tables
WHERE relname = 'posts';

pg_stat_xact_user_tables counts for the current transaction only, so the difference before and after the ten calls is exactly what they read. The test fails when that's more than twice the rows the pages returned, and it checks SHOW plan_cache_mode first, so the failure says why. The count suits lists that read each post's row, as these do for its title and text: an Index Only Scan that answers from the index alone adds nothing to idx_tup_fetch, so for such a query, read idx_tup_read in pg_stat_user_indexes, which isn't kept per transaction.

The same day: a left join counted as one row#

The second trap came from a sweep that finds posts without an embedding, so that a job can compute them. It was first written as a left join:

SQL
-- Expected one row once some embeddings existed.
SELECT p.workspace_id, p.id
FROM posts p
LEFT JOIN post_embeddings e
  ON e.workspace_id = p.workspace_id AND e.post_id = p.id
WHERE e.post_id IS NULL OR e.model <> $1 OR e.dimensions <> $2;

As soon as embeddings of the current model existed, PostgreSQL expected this to return one post: it can't estimate how many posts have no matching embedding, and every embedding there was had the current model. Planned for one row, it then checked each post's workspace (whether it was deleted) by reading the ten thousand workspaces once per post. On staging's million posts, the sweep ran into its ten-minute limit six times an hour, was canceled each time, and kept a CPU of the primary busy day and night, with under 3,000 posts embedded.

I first took it for the same generic plan, and force_custom_plan changed nothing: this plan was the same with the values as without. Worse, my check that morning had come out right for the wrong reason. I had asked a standby for the plan with a model name that wasn't quite the server's, so every embedding looked like another model's. Asked with the name the application really sends, it expected one row. Written as NOT EXISTS, the query gets an honest estimate: the posts less the embeddings, a million rows instead of one:

SQL
SELECT p.workspace_id, p.id
FROM posts p
WHERE p.deleted_at IS NULL
  AND NOT EXISTS (SELECT 1 FROM post_embeddings e
                  WHERE e.workspace_id = p.workspace_id AND e.post_id = p.id
                    AND e.model = $1 AND e.dimensions = $2)
  AND NOT EXISTS (SELECT 1 FROM workspaces w
                  WHERE w.id = p.workspace_id AND w.deleted_at IS NOT NULL)
ORDER BY p.created_at, p.id
LIMIT $3;

A test's database is too small for the bad plan to be chosen, so its test asks PostgreSQL what it expects instead: it runs EXPLAIN (FORMAT JSON) on the query as the code has it, with the values the application sends, and fails if the top node's Plan Rows is under half the posts it knows are missing.

Both traps have the same lesson: the planner plans for the rows it expects, so test the expectation, not only the speed on a small database. And ask for a plan with the values your application really sends.

Where this came from#

These are Swingback's board lists and its AI sweep. A board lists its posts by trending, top or newest, with filters and search your users can combine, and each page has to stay fast in a workspace of tens of thousands of posts. If you'd rather not run that yourself, you can start a board for free.