Re: SELECT over partitioned table with LIMIT 1 performance regression issue in PostgreSQL 17 and 18 - Mailing list pgsql-hackers
| From | abrahim abrahao |
|---|---|
| Subject | Re: SELECT over partitioned table with LIMIT 1 performance regression issue in PostgreSQL 17 and 18 |
| Date | |
| Msg-id | 922349715.487861.1783967925130@mail.yahoo.com Whole thread |
| In response to | SELECT over partitioned table with LIMIT 1 performance regression issue in PostgreSQL 17 and 18 (Илья Сербин <serbin.ilia@gmail.com>) |
| List | pgsql-hackers |
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-ed
ranges). Both patterns produce a Seq Scan across all partitions in PG17
where PG16 correctly chose Bitmap Index Scan.
I also believe your hypothesis about commit a8a968a82 ("Consider cheap
startup paths in add_paths_to_append_rel") is correct. I have EXPLAIN
FORMAT JSON output that shows exactly how the cost formula produces the
wrong result.
Environment
-----------
PostgreSQL 17.10 (Ubuntu 17.10-1.pgdg24.04+1), x86_64-pc-linux-gnu
No regression on PostgreSQL 16.14 (Ubuntu 16.14-1.pgdg24.04+1) with the
same 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 applying
the Limit discount during or before per-partition access method selection
rather than after. The Seq Scan's zero startup cost lets it "win" through
the 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-startup
path of each child when consider_startup is set. Seq Scan has startup_cost=0
so it wins as the cheapest startup path for each partition. This AppendPath
is then considered by the Limit node, and the formula above yields an
artificially 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-correlation
filter: finding 1 matching row for a specific asset_id requires scanning
roughly reltuples/plan_rows ≈ 306191/5189 ≈ 59 rows through the first
partition on average, not 1/614698 of the entire Append. The discount does
not 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, and
pg_class data if they would help.
Best regards,
pgsql-hackers by date: