Re: SELECT over partitioned table with LIMIT 1 performance regression issue in PostgreSQL 17 and 18

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