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.
