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');
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 forLockCurrentExecutionsJoinExecutionscan searchexecutionsusing only(shard_id, namespace_id, workflow_id). It appliesrun_idafterward 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:
The generic plan contains:
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.sqlinto a disposable PostgreSQL database, then run the following.force_generic_planmakes the regression reproducible without depending on when automatic plan selection switches from custom to generic.Synthetic SQL reproduction
Specifications
mainat172d1b409fddba6f24c38b631c32352dbd342d53. The workflow query is also present in v1.29.6.postgres12_pgxwith prepared statement caching.