Hello Ilya,
I am writing to confirm the regression you described and to add a
second,simpler query pattern that triggers the same bad plan in PostgreSQL
17.10:a CROSS JOIN LATERAL with LIMIT 1 and a correlated predicate (no
OR-edranges). Both patterns produce a Seq Scan across all partitions in
PG17where PG16 correctly chose Bitmap Index Scan.
I also believe your hypothesis about commit a8a968a82 ("Consider cheapstartup
paths in add_paths_to_append_rel") is correct. I have EXPLAINFORMAT JSON output
that shows exactly how the cost formula produces thewrong result.
Environment-----------PostgreSQL 17.10 (Ubuntu 17.10-1.pgdg24.04+1),
x86_64-pc-linux-gnuNo regression on PostgreSQL 16.14 (Ubuntu
16.14-1.pgdg24.04+1) with thesame schema and identical server configuration.
Schema (simplified, generic names)----------------------------------- CREATE
TABLE asset (id integer PRIMARY KEY);
CREATE TABLE event_log ( id bigserial, asset_id integer
NOT NULL, event_time timestamp NOT NULL ) PARTITION BY RANGE (event_time);
-- ~66 weekly range partitions, 300K to 20M rows each -- Composite index on
every partition: CREATE INDEX idx_event_log_<partition>_asset_event ON
event_log_<partition> (asset_id, event_time);
-- Key statistics (pg_stats for one representative partition): --
asset_id: n_distinct ≈ 60, correlation ≈ 0.026 (very low) -- event_time:
n_distinct < 0, correlation ≈ 0.99
Query----- SELECT t1.id FROM asset a JOIN LATERAL ( SELECT id FROM
event_log WHERE asset_id = a.id AND event_time >= '2023-01-01
00:00:00' LIMIT 1 ) t1 ON true WHERE a.id IN
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25) ORDER BY
t1.id LIMIT 5;
Results------- Version enable_seqscan Plan inside LATERAL subquery
Exec time -------- -------------- --------------------------------
---------- PG 16.14 ON (default) Bitmap Index Scan per partition ~758
ms PG 16.14 OFF Index Scan per partition ~967 ms PG
17.10 ON (default) Seq Scan — ALL 66 partitions ~241,892 ms PG 17.10
OFF Index Scan per partition ~72 ms
Server settings (production — same hardware and config for both versions):
work_mem = 438660kB, random_page_cost = 1.1, effective_cache_size =
58488008kB, max_parallel_workers = 0, jit_optimize_above_cost = 1e+07,
seq_page_cost = 1.0
Why the plan is wrong — EXPLAIN FORMAT JSON
evidence-----------------------------------------------------The critical node
is the Limit wrapping the LATERAL subquery Append.PostgreSQL costs a Limit with:
Limit cost = startup + (limit_rows / plan_rows) * (total_cost - startup)
PG16 — Bitmap Index Scan chosen (correct): startup_cost = 75.83 Append
total_cost = 551,427.93, plan_rows = 575,782 => Limit total cost = 75.83 +
(1/575782) * (551427.93 - 75.83) = 76.79
PG17 — Seq Scan chosen (incorrect): startup_cost = 0.00 Append total_cost =
10,333,250.92, plan_rows = 614,698 => Limit total cost = 0.00 + (1/614698) *
10,333,250.92 = 16.81
PG17 chose Seq Scan because 16.81 < 76.79.
Per-partition comparison (event_log_p2025_w19, ~306K rows, ~5,189 matching):
Bitmap Heap Scan: startup=75.83, total=2,993.94 Seq Scan:
startup=0.00, total=7,757.86
Bitmap Index Scan is demonstrably cheaper per partition (2,993 vs 7,757)yet
PG17 chose Seq Scan for every partition. This means PG17 is applyingthe Limit
discount during or before per-partition access method selectionrather than
after. The Seq Scan's zero startup cost lets it "win" throughthe global
discount even when it is the worse per-partition choice.
Connection to commit a8a968a82-------------------------------Commit a8a968a82
("Consider cheap startup paths in add_paths_to_append_rel",David Rowley,
2023-10-05) builds an AppendPath from the cheapest-startuppath of each child
when consider_startup is set. Seq Scan has startup_cost=0so it wins as the
cheapest startup path for each partition. This AppendPathis then considered by
the Limit node, and the formula above yields anartificially small cost (0 +
total/N = 16.81) that beats the Bitmap path(75.83 + delta = 76.79).
The Limit discount is semantically wrong for Seq Scan with a
low-correlationfilter: finding 1 matching row for a specific asset_id requires
scanningroughly reltuples/plan_rows ≈ 306191/5189 ≈ 59 rows through the
firstpartition on average, not 1/614698 of the entire Append. The discount
doesnot account for the filter selectivity of the correlated predicate.
Additional confirmation — statistics and settings are not the
cause--------------------------------------------------------------------I
verified the following for our case:
- pg_stats: n_distinct and correlation values are virtually identical
between PG16 and PG17 for the same partitions. Both had fresh ANALYZE. -
pg_class: reltuples and relpages are consistent between versions for the
shared older partitions. - Production PG16 and PG17 ran on the same hardware
with identical configuration. Work_mem, effective_cache_size,
random_page_cost are not variables. - The larger effective_cache_size on
PG17 should make index access appear *cheaper*, not more expensive — it
works against the observed behavior, further confirming the regression is in
planner logic.
Workaround---------- SET enable_seqscan = off; -- or permanently: ALTER
DATABASE <dbname> SET enable_seqscan = off;
I am happy to share full EXPLAIN FORMAT JSON outputs, pg_stats, andpg_class
data if they would help.
Best regards,
Abrahim
