Re: Regression in query optimizer when upgrading from 9.4.5 to 9.5.2?

On Fri, Jun 17, 2016 at 5:05 PM, Erdmann, Markus @ Bellevue <
Markus(dot)Erdmann(at)cbre(dot)com> wrote:

> Thank you Tom and David for your very helpful replies. We dumped and
> restored the RDS staging database on a local installation of pg and were
> not able to reproduce the issue in 9.5.2, which led us to try running a
> VACUUM ANALYZE and recreating indexes. After this we no longer saw a
> discrepancy between the query plan in the separate environments. Is this
> what you meant, Tom, by making sure to ANALYZE? Or did you mean including
> ANALYZE in EXPLAIN ANALYZE?
>
>
​Please don't top-post.

Yes, using "[VACUUM] ANALYZE" is what was meant (the VACUUM doesn't hurt -
and possibly helps - but wasn't required). ANALYZE recomputes the
statistics for your database. The apparent problem was that those
statistics were wrong which causes the planner to choose the wrong plan.

EXPLAIN ANALYZE doesn't help in that scenario - the explain and planner
both end up using the bad statistics. The addition of ANALYZE to EXPLAIN
simply tells the system to not only explain your query but to execute it as
well (but discard the results). Aside from sharing the same 7 characters
the two words have nothing in common.

I'm not sure how one would "...includ[e] ANALYZE in EXPLAIN ANALYZE" - you
only get to explain one statement at a time.

David J.