Collected from the PG bugs email list.
|Reported by||Jeremy Evans|
Body of first available message related to this bug follows.
The following bug has been logged on the website: Bug reference: 15722 Logged by: Jeremy Evans Email address: (redacted) PostgreSQL version: 11.2 Operating system: Linux Description: For one complex query, we are seeing a huge performance regression starting with PostgreSQL 10. We noticed this regression starting with an upgrade from 9.3 to 11. We then tested on PostgreSQL 9.3, 9.4, 9.5, 9.6, 10, and 11 with this query by restoring a database dump and running the query. On PostgreSQL 9.3-9.6, this query runs in a few seconds. In PostgreSQL 10 and 11 it takes multiple hours. We tried manually running VACUUM and ANALYZE commands before running the query, with no significant effect. As the restoration of the database dump was done just before running this query, there should be no need to REINDEX. This analysis was repeated on a separate physical server with a different hardware configuration but similar PostgreSQL configuration, also on 9.3, 9.4, 9.5, 9.6, 10, and 11, with the same results. The cause of this problem seems to be PostgreSQL underestimating the number of rows needed by a hash join by almost 9 million times. On PostgreSQL 10-11, the expensive part of the query is a Nested Loop Inner Join of a Hash Inner Join and a Index Scan. On PostgreSQL 9.3-9.6, this part of the query uses a Nested Loop Semi Join of the Hash Inner Join and Index Scan. The following files are available at https://gist.github.com/jeremyevans/8a9693b1d3f14c94f635b1bf4275b2be * Raw SQL Query * Formatted SQL Query * Database Schema Dump * EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON) output for: * 9.3 * 9.4 * 9.5 * 9.6 * 10 * 11 Due to the consistently good behavior in 9.3-9.6, and the severity of the performance decrease in 10-11, I think this is most likely a regression in PostgreSQL's query planner or optimizer, which is why I'm posting this as a bug report. If this is not considered a bug, please let me know and I can post to pgsql-performance instead. Any and all help greatly appreciated. If more information would be helpful, please let me know. Thanks, Jeremy
|2019-03-29 01:14:11+00||PG Bug reporting form||BUG #15722: 9000x performance regression on query starting PostgreSQL 10 due to bad query plan|
|2019-04-02 17:10:48+00||Jeff Janes||Re: BUG #15722: 9000x performance regression on query starting PostgreSQL 10 due to bad query plan|
|2019-04-04 06:16:13+00||Jeremy Evans||Re: BUG #15722: 9000x performance regression on query starting PostgreSQL 10 due to bad query plan|