Re: Postgres 9.4 using primary key index in almost all queries leading to degraded performance

Rahul Sharma <rahul(dot)sharma1(at)oyorooms(dot)com> 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