Re: Queries are taking way longer in 9.6 than 9.5

On 01/19/2017 06:21 AM, Merlin Moncure wrote:
>
...
>
> yes. Either way, I would like to very much understand how server is
> preferring 3m cost plan to 2.6k cost plan or is otherwise unable to
> access the cheap plan.
>

Perhaps it doesn't even see the 2.6k cost - it may not generate the plan
for some reason, or it arrives to different estimates.

The OP was repeatedly asked for details of the configuration, I've even
sent a query for doing that a few days back. It's difficult to help when
such basic info is not provided, though.

It'd also be interesting to see the statistics for the tables involved,
so something like

SELECT * FROM pg_stats WHERE tablename IN (... tables ...)

and

SELECT relname, relpages, reltuples, relallvisible
FROM pg_class WHERE relame IN (... tables ...)

might shed some light on what the planner assumes about the data.

Of course, there are other things we might need to know. For example if
there are foreign keys between the tables, 9.6 is using that for the
estimates (unlike 9.5). And so on.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services