On 23/2/26 10:41, Laurenz Albe wrote:
On Mon, 2026-02-23 at 10:37 +0100, Attila Soki wrote:
When upgrading from PostgreSQL 14.4, I noticed that one of my somewhat complex
analytical queries sometimes gets an inefficient plan under PostgreSQL 16, 17,
and 18.
Under 14.4, the query runs with a stable plan and completes in 19 to 22 seconds.
In newer versions, the plan seems to be unstable, sometimes the query completes
in 17 to 20 seconds, sometimes it runs for 5 to 18 minutes with the inefficient
plan.
This also happens even if the data is not significantly changed.
This is very likely owing to a bad estimate.
Could you turn on "track_io_timing" and send us the EXPLAIN (ANALYZE, BUFFERS)
output
for both the good and the bad plan?
Since PG16, the optimiser changed a lot. So, there are plenty of
possibilities that might happen - table statistics update, for example.
So, we need at least EXPLAIN ANALYSE for 'good' and 'bad' cases to begin
a discussion.
--
regards, Andrei Lepikhov,
pgEdge
