Re: Slow query after 9.3 to 9.6 migration

On Tue, Dec 27, 2016 at 5:50 PM, Flávio Henrique <yoshimit(at)gmail(dot)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