PostgreSQL current-execution lookup can scan previous runs with a generic plan

Expected Behavior

Looking up and locking a workflow's current execution should use its complete primary key, including run_id, without scanning previous runs of the same workflow ID.

Actual Behavior

With PostgreSQL and postgres12_pgx, a cached generic plan for LockCurrentExecutionsJoinExecutions can search executions using only (shard_id, namespace_id, workflow_id). It applies run_id afterward as a join filter. The work then grows with the number of retained runs for that workflow ID.

In a synthetic PostgreSQL 16.4 reproduction with 100,000 single-use workflow IDs and 30,000 runs of one recurring ID:

Plan	Execution rows scanned	Shared buffers
Generic	30,000	3,075
Custom	1	10

The generic plan contains:

Index Cond: ((shard_id = $1) AND (namespace_id = $2) AND (workflow_id = $3))
Join Filter: (e.run_id = ce.run_id)
Rows Removed by Join Filter: 29999

The equivalent CHASM lookup has the same problem. Whether PostgreSQL chooses this plan depends on the data distribution, so it need not affect every deployment.

Steps to Reproduce the Problem

Load schema/postgresql/v12/temporal/schema.sql into a disposable PostgreSQL database, then run the following. force_generic_plan makes the regression reproducible without depending on when automatic plan selection switches from custom to generic.

Synthetic SQL reproduction

INSERT INTO executions
SELECT i%512, decode(repeat('01',16),'hex'), 'single-use-'||i,
       decode(lpad(to_hex(i),32,'0'),'hex'), 2, 1,
       decode(repeat(md5(i::text),32),'hex'), 'Proto3', decode('01','hex'), 'Proto3', 1
FROM generate_series(1,100000) AS i;

INSERT INTO executions
SELECT 7, decode(repeat('01',16),'hex'), 'recurring-workflow',
       decode(lpad(to_hex(i),32,'0'),'hex'), 2, 1,
       decode(repeat(md5(i::text),32),'hex'), 'Proto3', decode('01','hex'), 'Proto3', 1
FROM generate_series(1,30000) AS i;

INSERT INTO current_executions
(shard_id, namespace_id, workflow_id, run_id, create_request_id, state, status,
 start_time, last_write_version, data, data_encoding)
SELECT shard_id, namespace_id, workflow_id, run_id, 'request', 2, 1,
       now(), last_write_version, state, state_encoding
FROM executions
WHERE workflow_id LIKE 'single-use-%'
   OR run_id=decode(lpad(to_hex(30000),32,'0'),'hex');

ANALYZE executions;
ANALYZE current_executions;

PREPARE current_execution AS
SELECT ce.shard_id, ce.namespace_id, ce.workflow_id, ce.run_id,
       ce.create_request_id, ce.state, ce.status, ce.start_time,
       e.last_write_version, ce.data, ce.data_encoding
FROM current_executions ce
INNER JOIN executions e
  ON e.shard_id=ce.shard_id AND e.namespace_id=ce.namespace_id
 AND e.workflow_id=ce.workflow_id AND e.run_id=ce.run_id
WHERE ce.shard_id=$1 AND ce.namespace_id=$2 AND ce.workflow_id=$3
FOR UPDATE;

SET plan_cache_mode=force_generic_plan;
EXPLAIN (ANALYZE, BUFFERS) EXECUTE current_execution(
    7, decode(repeat('01',16),'hex'), 'recurring-workflow');

SET plan_cache_mode=force_custom_plan;
EXPLAIN (ANALYZE, BUFFERS) EXECUTE current_execution(
    7, decode(repeat('01',16),'hex'), 'recurring-workflow');

Specifications

• Temporal: reproduced on main at 172d1b409fddba6f24c38b631c32352dbd342d53. The workflow query is also present in v1.29.6.
• PostgreSQL: 16.4.
• Persistence plugin: postgres12_pgx with prepared statement caching.