Thread: Forcing analyze on DB after upgrading?
I just upgraded from 9.6 to 12, and am noticing some queries are slow.
I did this kind of query:
SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_all_tables
WHERE schemaname = 'myschema';
FROM pg_stat_all_tables
WHERE schemaname = 'myschema';
And noticed null values for everything, including last analyze date.
Does this mean the planner won't be able to optimize for these tables? Do I need to do something to prep my DB for planning after upgrade?
On Tue, 2019-10-29 at 22:11 -0700, Wells Oliver wrote: > I just upgraded from 9.6 to 12, and am noticing some queries are slow. > > I did this kind of query: > > SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze > FROM pg_stat_all_tables > WHERE schemaname = 'myschema'; > > And noticed null values for everything, including last analyze date. > > Does this mean the planner won't be able to optimize for these tables? > Do I need to do something to prep my DB for planning after upgrade? You used pg_upgrade, right? pg_upgrade won't migrate any table statistics. You'll have to run ANALYZE to collect new ones. This is even mentioned in the output of pg_upgrade, and it writes a convenient shell script that does the ANALYZE for you. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Thanks! Unfortunately I can't find the shell script it created in /var/log/postgresql -- however, I ran vacuumdb --full --verbose --analyze-in-stages and the DB performs much, much better.
As I can't seem to find the script, anything else a good thing to do after doing pg_upgrade to prime the DB?
On Tue, Oct 29, 2019 at 10:38 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Tue, 2019-10-29 at 22:11 -0700, Wells Oliver wrote:
> I just upgraded from 9.6 to 12, and am noticing some queries are slow.
>
> I did this kind of query:
>
> SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
> FROM pg_stat_all_tables
> WHERE schemaname = 'myschema';
>
> And noticed null values for everything, including last analyze date.
>
> Does this mean the planner won't be able to optimize for these tables?
> Do I need to do something to prep my DB for planning after upgrade?
You used pg_upgrade, right?
pg_upgrade won't migrate any table statistics.
You'll have to run ANALYZE to collect new ones.
This is even mentioned in the output of pg_upgrade,
and it writes a convenient shell script that does the
ANALYZE for you.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
--
Wells Oliver
wells.oliver@gmail.com
wells.oliver@gmail.com
On Tue, 2019-10-29 at 22:41 -0700, Wells Oliver wrote: > Thanks! Unfortunately I can't find the shell script it created in /var/log/postgresql -- however, > I ran vacuumdb --full --verbose --analyze-in-stages and the DB performs much, much better. Ouch. --full might hurt. > As I can't seem to find the script, anything else a good thing to do after doing pg_upgrade to prime the DB? pg_upgrade recommends vacuumdb --all --analyze-in-stages which runs three analyze passes with different "default_statistics_target" to get up and running as fast as possible, but if you can wait a little, vacuumdb --all --analyze-only will be faster. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com