Обсуждение: Postgres 12 -> 13 any need to re-index?
Hey guys, I just upgraded a DB from 12 to 13. In testing this seemed to go well, but it seems like most of my queries are not using the indexes after the upgrade, so everything is quite so.
Is there a general need to REINDEX after upgrading to 13, or might there be another issue? The configuration parameters are the same as they were persisted via pg_upgradecluster.
On 2/9/21 12:00 PM, Wells Oliver wrote: > Hey guys, I just upgraded a DB from 12 to 13. In testing this seemed to go > well, but it seems like most of my queries are not using the indexes after > the upgrade, so everything is quite so. > > Is there a general need to REINDEX after upgrading to 13, or might there > be another issue? The configuration parameters are the same as they were > persisted via pg_upgradecluster. Did you also change or upgrade the distro, or modify the collation? (That's an automatic need to rebuild indices.) -- Angular momentum makes the world go 'round.
Do you mean Ubuntu distro? I did not. My database collation is also still en_US.UTF-8, but everything has ground to a halt after the upgrade.
On Tue, Feb 9, 2021 at 10:07 AM Ron <ronljohnsonjr@gmail.com> wrote:
On 2/9/21 12:00 PM, Wells Oliver wrote:
> Hey guys, I just upgraded a DB from 12 to 13. In testing this seemed to go
> well, but it seems like most of my queries are not using the indexes after
> the upgrade, so everything is quite so.
>
> Is there a general need to REINDEX after upgrading to 13, or might there
> be another issue? The configuration parameters are the same as they were
> persisted via pg_upgradecluster.
Did you also change or upgrade the distro, or modify the collation? (That's
an automatic need to rebuild indices.)
--
Angular momentum makes the world go 'round.
Wells Oliver
wells.oliver@gmail.com
wells.oliver@gmail.com
Wells Oliver schrieb am 09.02.2021 um 19:00: > Hey guys, I just upgraded a DB from 12 to 13. In testing this seemed > to go well, but it seems like most of my queries are not using the > indexes after the upgrade, so everything is quite so. > > Is there a general need to REINDEX after upgrading to 13, or might > there be another issue? The configuration parameters are the same as > they were persisted via pg_upgradecluster. If you want to profit from the new de-duplication in indexes, you will need to reindex.
On 2/9/21 12:13 PM, Wells Oliver wrote:
Did you run an ANALYZE after the conversion?
Do you mean Ubuntu distro? I did not. My database collation is also still en_US.UTF-8, but everything has ground to a halt after the upgrade.
Did you run an ANALYZE after the conversion?
On Tue, Feb 9, 2021 at 10:07 AM Ron <ronljohnsonjr@gmail.com> wrote:On 2/9/21 12:00 PM, Wells Oliver wrote:
> Hey guys, I just upgraded a DB from 12 to 13. In testing this seemed to go
> well, but it seems like most of my queries are not using the indexes after
> the upgrade, so everything is quite so.
>
> Is there a general need to REINDEX after upgrading to 13, or might there
> be another issue? The configuration parameters are the same as they were
> persisted via pg_upgradecluster.
Did you also change or upgrade the distro, or modify the collation? (That's
an automatic need to rebuild indices.)
--
Angular momentum makes the world go 'round.
Angular momentum makes the world go 'round.
No, I didn't. I had done that in testing and wanted to get the thing back up and running. Would i just run 'analyze dbname' or something more verbose?
On Tue, Feb 9, 2021 at 10:17 AM Ron <ronljohnsonjr@gmail.com> wrote:
On 2/9/21 12:13 PM, Wells Oliver wrote:Do you mean Ubuntu distro? I did not. My database collation is also still en_US.UTF-8, but everything has ground to a halt after the upgrade.
Did you run an ANALYZE after the conversion?On Tue, Feb 9, 2021 at 10:07 AM Ron <ronljohnsonjr@gmail.com> wrote:On 2/9/21 12:00 PM, Wells Oliver wrote:
> Hey guys, I just upgraded a DB from 12 to 13. In testing this seemed to go
> well, but it seems like most of my queries are not using the indexes after
> the upgrade, so everything is quite so.
>
> Is there a general need to REINDEX after upgrading to 13, or might there
> be another issue? The configuration parameters are the same as they were
> persisted via pg_upgradecluster.
Did you also change or upgrade the distro, or modify the collation? (That's
an automatic need to rebuild indices.)--
Angular momentum makes the world go 'round.
Wells Oliver
wells.oliver@gmail.com
wells.oliver@gmail.com
On 2/9/21 12:18 PM, Wells Oliver wrote:
Tsk tsk.
Essentially. I'd do something like:
vacuumdb --analyze-only --jobs=$(nproc) -d ${YOUR_DB}
No, I didn't. I had done that in testing
Tsk tsk.
and wanted to get the thing back up and running. Would i just run 'analyze dbname' or something more verbose?
Essentially. I'd do something like:
vacuumdb --analyze-only --jobs=$(nproc) -d ${YOUR_DB}
On Tue, Feb 9, 2021 at 10:17 AM Ron <ronljohnsonjr@gmail.com> wrote:On 2/9/21 12:13 PM, Wells Oliver wrote:Do you mean Ubuntu distro? I did not. My database collation is also still en_US.UTF-8, but everything has ground to a halt after the upgrade.
Did you run an ANALYZE after the conversion?On Tue, Feb 9, 2021 at 10:07 AM Ron <ronljohnsonjr@gmail.com> wrote:On 2/9/21 12:00 PM, Wells Oliver wrote:
> Hey guys, I just upgraded a DB from 12 to 13. In testing this seemed to go
> well, but it seems like most of my queries are not using the indexes after
> the upgrade, so everything is quite so.
>
> Is there a general need to REINDEX after upgrading to 13, or might there
> be another issue? The configuration parameters are the same as they were
> persisted via pg_upgradecluster.
Did you also change or upgrade the distro, or modify the collation? (That's
an automatic need to rebuild indices.)
--
Angular momentum makes the world go 'round.
Angular momentum makes the world go 'round.
Yeah. Running that now. Honestly, I went from 9 to 10 to 11 to 12 without doing that in prod, and it was marvelous. 13 hit a wall, and I'm not sure why.
It's running now, though...
On Tue, Feb 9, 2021 at 10:25 AM Ron <ronljohnsonjr@gmail.com> wrote:
On 2/9/21 12:18 PM, Wells Oliver wrote:No, I didn't. I had done that in testing
Tsk tsk.and wanted to get the thing back up and running. Would i just run 'analyze dbname' or something more verbose?
Essentially. I'd do something like:
vacuumdb --analyze-only --jobs=$(nproc) -d ${YOUR_DB}On Tue, Feb 9, 2021 at 10:17 AM Ron <ronljohnsonjr@gmail.com> wrote:On 2/9/21 12:13 PM, Wells Oliver wrote:Do you mean Ubuntu distro? I did not. My database collation is also still en_US.UTF-8, but everything has ground to a halt after the upgrade.
Did you run an ANALYZE after the conversion?On Tue, Feb 9, 2021 at 10:07 AM Ron <ronljohnsonjr@gmail.com> wrote:On 2/9/21 12:00 PM, Wells Oliver wrote:
> Hey guys, I just upgraded a DB from 12 to 13. In testing this seemed to go
> well, but it seems like most of my queries are not using the indexes after
> the upgrade, so everything is quite so.
>
> Is there a general need to REINDEX after upgrading to 13, or might there
> be another issue? The configuration parameters are the same as they were
> persisted via pg_upgradecluster.
Did you also change or upgrade the distro, or modify the collation? (That's
an automatic need to rebuild indices.)--
Angular momentum makes the world go 'round.
Wells Oliver
wells.oliver@gmail.com
wells.oliver@gmail.com
OK, that seems to have made a huge difference. and ran very quickly, ~15 mins on a ~1TB database. Is there a way to better understand what it did? There's no way it rebuilt indexes in that amount of time. Also, as I said, I never did that going from 9->10->11->12, so it's interesting to me that it was so critical this time around.
On Tue, Feb 9, 2021 at 10:27 AM Wells Oliver <wells.oliver@gmail.com> wrote:
Yeah. Running that now. Honestly, I went from 9 to 10 to 11 to 12 without doing that in prod, and it was marvelous. 13 hit a wall, and I'm not sure why.It's running now, though...On Tue, Feb 9, 2021 at 10:25 AM Ron <ronljohnsonjr@gmail.com> wrote:On 2/9/21 12:18 PM, Wells Oliver wrote:No, I didn't. I had done that in testing
Tsk tsk.and wanted to get the thing back up and running. Would i just run 'analyze dbname' or something more verbose?
Essentially. I'd do something like:
vacuumdb --analyze-only --jobs=$(nproc) -d ${YOUR_DB}On Tue, Feb 9, 2021 at 10:17 AM Ron <ronljohnsonjr@gmail.com> wrote:On 2/9/21 12:13 PM, Wells Oliver wrote:Do you mean Ubuntu distro? I did not. My database collation is also still en_US.UTF-8, but everything has ground to a halt after the upgrade.
Did you run an ANALYZE after the conversion?On Tue, Feb 9, 2021 at 10:07 AM Ron <ronljohnsonjr@gmail.com> wrote:On 2/9/21 12:00 PM, Wells Oliver wrote:
> Hey guys, I just upgraded a DB from 12 to 13. In testing this seemed to go
> well, but it seems like most of my queries are not using the indexes after
> the upgrade, so everything is quite so.
>
> Is there a general need to REINDEX after upgrading to 13, or might there
> be another issue? The configuration parameters are the same as they were
> persisted via pg_upgradecluster.
Did you also change or upgrade the distro, or modify the collation? (That's
an automatic need to rebuild indices.)--
Angular momentum makes the world go 'round.--Wells Oliver
wells.oliver@gmail.com
Wells Oliver
wells.oliver@gmail.com
wells.oliver@gmail.com
On Tuesday, February 9, 2021, Wells Oliver <wells.oliver@gmail.com> wrote:
OK, that seems to have made a huge difference. and ran very quickly, ~15 mins on a ~1TB database.Is there a way to better understand what it did?
Read the documentation?
There's no way it rebuilt indexes in that amount of time. Also, as I said, I never did that going from 9->10->11->12, so it's interesting to me that it was so critical this time around.
I’m more inclined to think you did the analyze and just don’t remember - though its possible that the auto-vacuum daemon helped out behind the scenes.
David J.
This command is well documented:
vacuumdb --analyze-only --jobs=$(nproc) -d ${YOUR_DB}
On 2/9/21 12:49 PM, Wells Oliver wrote:
OK, that seems to have made a huge difference. and ran very quickly, ~15 mins on a ~1TB database. Is there a way to better understand what it did? There's no way it rebuilt indexes in that amount of time. Also, as I said, I never did that going from 9->10->11->12, so it's interesting to me that it was so critical this time around.On Tue, Feb 9, 2021 at 10:27 AM Wells Oliver <wells.oliver@gmail.com> wrote:Yeah. Running that now. Honestly, I went from 9 to 10 to 11 to 12 without doing that in prod, and it was marvelous. 13 hit a wall, and I'm not sure why.It's running now, though...On Tue, Feb 9, 2021 at 10:25 AM Ron <ronljohnsonjr@gmail.com> wrote:On 2/9/21 12:18 PM, Wells Oliver wrote:No, I didn't. I had done that in testing
Tsk tsk.and wanted to get the thing back up and running. Would i just run 'analyze dbname' or something more verbose?
Essentially. I'd do something like:
vacuumdb --analyze-only --jobs=$(nproc) -d ${YOUR_DB}On Tue, Feb 9, 2021 at 10:17 AM Ron <ronljohnsonjr@gmail.com> wrote:On 2/9/21 12:13 PM, Wells Oliver wrote:Do you mean Ubuntu distro? I did not. My database collation is also still en_US.UTF-8, but everything has ground to a halt after the upgrade.
Did you run an ANALYZE after the conversion?On Tue, Feb 9, 2021 at 10:07 AM Ron <ronljohnsonjr@gmail.com> wrote:On 2/9/21 12:00 PM, Wells Oliver wrote:
> Hey guys, I just upgraded a DB from 12 to 13. In testing this seemed to go
> well, but it seems like most of my queries are not using the indexes after
> the upgrade, so everything is quite so.
>
> Is there a general need to REINDEX after upgrading to 13, or might there
> be another issue? The configuration parameters are the same as they were
> persisted via pg_upgradecluster.
Did you also change or upgrade the distro, or modify the collation? (That's
an automatic need to rebuild indices.)--
Angular momentum makes the world go 'round.--Wells Oliver
wells.oliver@gmail.com--Wells Oliver
wells.oliver@gmail.com
--
Angular momentum makes the world go 'round.
Angular momentum makes the world go 'round.
El mar, 9 de feb. de 2021 a la(s) 15:50, Wells Oliver (wells.oliver@gmail.com) escribió:
OK, that seems to have made a huge difference. and ran very quickly, ~15 mins on a ~1TB database. Is there a way to better understand what it did? There's no way it rebuilt indexes in that amount of time. Also, as I said, I never did that going from 9->10->11->12, so it's interesting to me that it was so critical this time around.
It didn't rebuild indexes. It scanned rows to update statistics thereby improving the planner's strategy to execute queries.