Re: [PERFORM] Slow query after 9.3 to 9.6 migration

On Tue, Dec 27, 2016 at 5:50 PM, Flávio Henrique <yoshimit@gmail.com> wrote:

> I can see some buffers written that tells me
> that something is wrong.

Try running VACUUM FREEZE ANALYZE on all tables involved in the
query (or just run it as a superuser on the whole database).  Do
*not* use the FULL option.  Among other things, this will ensure
that you have somewhat current statistics, and that all hint bits
are set.  (I remember my surprise the first time I converted a
table to PostgreSQL, ran SELECT count(*) on it to make sure all
rows made it, saw a very long run time with disk writes as the
bottleneck.  That's when I learned about hint bits.)

You should also make sure that autovacuum is aggressive enough on
the new cluster.  Without that, any performance benefit from the
above will slowly disappear.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company