Re: Postgres: Queries are too slow after upgrading to PG17 from PG15
Hi Team,
Following up on this thread. We have new, targeted benchmark data from a different product workload that highlights a persistent and severe performance regression on PostgreSQL 18.
Our tests compared two key queries—one is a complex, product-specific aggregation, and the other is a simple multi-join(a test case shared with you earlier). The results show a significant regression for both queries when using the IN clause pattern, which is completely resolved by switching to JOIN UNNEST.
Here is the full summary of our benchmark results:
+------------------------+---------------+----------------------+------------------------+--------------------------+ | Query Name | Metric | PG 16 with IN Clause | PG 18 with IN Clause | PG 18 with JOIN UNNEST | | | | (Baseline) | (Regression) | (Solution) | +------------------------+---------------+----------------------+------------------------+--------------------------+ | Complex Aggregation | Latency (ms) | 151.73 | 1031.35 | 199.92 | | (Product Specific) | TPS | 65.91 | 9.70 | 50.02 | +------------------------+---------------+----------------------+------------------------+--------------------------+ | Simple JOIN | Latency (ms) | 666 | 1382.13 | 538.42 | | (For Easy Analysis) | TPS | 16.08 | 7.24 | 18.57 | +------------------------+---------------+----------------------+------------------------+--------------------------+
The data shows a slowdown of ~580% for our complex query and ~122% for the simple one, proving the regression's impact. Crucially, the JOIN UNNEST pattern fixes this in both cases.
To provide the cleanest and most direct test case for your analysis, the EXPLAIN (ANALYZE) plans linked below are for the Simple JOIN Query only, as this should make it much easier to isolate the planner's behavior. The initial reproducer share with you earlier still holds good for yoru perusal.
- PG16 Plan (Good - Simple Join):
- PG18 Plan (Bad - Simple Join):
- PG18 with UNNEST Plan (Good - Simple Join):
To further aid in debugging, the initial minimal reproducer shared earlier in this thread is still valid and can be used to demonstrate this behaviour.
Given this new data, which demonstrates the regression on both a complex and a simple test case, our questions are:
- Is this significant performance difference between a large IN clause and JOIN UNNEST the expected behaviour for this type of query in PostgreSQL 18?
- With the issue now clearly isolated, would it be possible to reconsider the WIP patch?
A final piece of context: While we now have a clear workaround with JOIN UNNEST, the path to implementing this across our application is not always straightforward. Our application uses JPA extensively, and in many cases, converting an existing IN clause to JOIN UNNEST requires a significant and resource-intensive refactoring to switch from standard JPQL to a native query.
This means that while we can surgically fix our most critical views, a database-level improvement (like the patch) would provide a huge benefit to the wider ecosystem of applications like ours that rely heavily on these data access abstractions.
I've synced with Todd Cook on these findings, and he agrees they are worth sharing. Any insights you can provide would be greatly appreciated as we plan our upgrade path.
Best regards,
Вложения
В списке pgsql-bugs по дате отправления: