pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedForcing analyze on DB after upgrading?
4+ messages / 2 participants
[nested] [flat]
* Forcing analyze on DB after upgrading?
@ 2019-10-30 05:11 Wells Oliver <wells.oliver@gmail.com>
2019-10-30 05:38 ` Re: Forcing analyze on DB after upgrading? Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 4+ messages in thread
From: Wells Oliver @ 2019-10-30 05:11 UTC (permalink / raw)
To: pgsql-admin
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?
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Forcing analyze on DB after upgrading?
2019-10-30 05:11 Forcing analyze on DB after upgrading? Wells Oliver <wells.oliver@gmail.com>
@ 2019-10-30 05:38 ` Laurenz Albe <laurenz.albe@cybertec.at>
2019-10-30 05:41 ` Re: Forcing analyze on DB after upgrading? Wells Oliver <wells.oliver@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Laurenz Albe @ 2019-10-30 05:38 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Forcing analyze on DB after upgrading?
2019-10-30 05:11 Forcing analyze on DB after upgrading? Wells Oliver <wells.oliver@gmail.com>
2019-10-30 05:38 ` Re: Forcing analyze on DB after upgrading? Laurenz Albe <laurenz.albe@cybertec.at>
@ 2019-10-30 05:41 ` Wells Oliver <wells.oliver@gmail.com>
2019-10-30 05:48 ` Re: Forcing analyze on DB after upgrading? Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 4+ messages in thread
From: Wells Oliver @ 2019-10-30 05:41 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-admin
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 <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Forcing analyze on DB after upgrading?
2019-10-30 05:11 Forcing analyze on DB after upgrading? Wells Oliver <wells.oliver@gmail.com>
2019-10-30 05:38 ` Re: Forcing analyze on DB after upgrading? Laurenz Albe <laurenz.albe@cybertec.at>
2019-10-30 05:41 ` Re: Forcing analyze on DB after upgrading? Wells Oliver <wells.oliver@gmail.com>
@ 2019-10-30 05:48 ` Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 4+ messages in thread
From: Laurenz Albe @ 2019-10-30 05:48 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2019-10-30 05:48 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-10-30 05:11 Forcing analyze on DB after upgrading? Wells Oliver <wells.oliver@gmail.com>
2019-10-30 05:38 ` Laurenz Albe <laurenz.albe@cybertec.at>
2019-10-30 05:41 ` Wells Oliver <wells.oliver@gmail.com>
2019-10-30 05:48 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox