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

On Jun 20, 2016, at 1:36 PM, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com<mailto:david(dot)g(dot)johnston(at)gmail(dot)com>> wrote:

​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.

Thank you, David. My confusion originated from a lack of familiarity with the ANALYZE command. Your responses and Tom’s response have been very enlightening.

The head-scratcher for us is that our statistics became so out of date even though we have the autovacuum daemon enabled in RDS, and according to the docs that does run ANALYZE periodically. Now we know (thanks to your help) to check for this issue immediately when the planner is showing a large disparity between the estimated and actual cost.

Markus E.