Rahul Sharma <rahul.sharma1@xxxxxxxxxxxx> writes: > We're currently testing out upgrade of our Postgres database from 9.3.14 to > 9.4.9. We are using Amazon RDS. We've encountered an issue in testing > phase where after the upgrade, CPU utilization hovers around 100%. We dug > deep to find that the queries which executed in a few milliseconds on > Postgres 9.3.14 are taking a lot of time to complete on 9.4.9. We've > noticed a pattern under which the query planner (in almost all queries) is > using primary key index for scanning the table while ignoring other > alternatives like explicit sorting or using another efficient index present > on the table. In all the examples you show, the rowcount estimates are substantially different in 9.3 and 9.4 --- and at least in the non-LIMITed queries, where it's possible to verify the estimates, 9.3 is reasonably on-target but 9.4 is awful. So your problem is not with the costs but with the selectivity estimates. Has the 9.4 database been ANALYZEd at all? Maybe you disabled auto-analyze? Maybe the old DB has some special statistics target settings that you neglected to port forward? regards, tom lane
