public inbox for [email protected]
help / color / mirror / Atom feedFrom: Kevin Grittner <[email protected]>
To: Flávio Henrique <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Slow query after 9.3 to 9.6 migration
Date: Wed, 4 Jan 2017 08:25:12 -0600
Message-ID: <CACjxUsMzXSagK-WjpVO35Qa3=4_2DT7aBYFhqROEop_KKnAUaA@mail.gmail.com> (raw)
In-Reply-To: <CAOGex3nXTRPZTD-KeoSwD=bj62hQrMK+6h30u09srV71sePqUA@mail.gmail.com>
References: <CAOGex3nXTRPZTD-KeoSwD=bj62hQrMK+6h30u09srV71sePqUA@mail.gmail.com>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-performance>
On Tue, Dec 27, 2016 at 5:50 PM, Flávio Henrique <[email protected]> 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
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Slow query after 9.3 to 9.6 migration
In-Reply-To: <CACjxUsMzXSagK-WjpVO35Qa3=4_2DT7aBYFhqROEop_KKnAUaA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox