Postgres 12 -> 13 any need to re-index?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

Postgres 12 -> 13 any need to re-index?

Wells Oliver-2
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.

Thanks.

--
Wells Oliver
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: Postgres 12 -> 13 any need to re-index?

Ron-2
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.


Reply | Threaded
Open this post in threaded view
|

Re: Postgres 12 -> 13 any need to re-index?

Wells Oliver-2
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 <[hidden email]> 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
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: Postgres 12 -> 13 any need to re-index?

Thomas Kellerer-4
In reply to this post by Wells Oliver-2
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.



Reply | Threaded
Open this post in threaded view
|

Re: Postgres 12 -> 13 any need to re-index?

Ron-2
In reply to this post by Wells Oliver-2
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 <[hidden email]> 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.
Reply | Threaded
Open this post in threaded view
|

Re: Postgres 12 -> 13 any need to re-index?

Wells Oliver-2
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 <[hidden email]> 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 <[hidden email]> 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
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: Postgres 12 -> 13 any need to re-index?

Ron-2
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 <[hidden email]> 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 <[hidden email]> 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.
Reply | Threaded
Open this post in threaded view
|

Re: Postgres 12 -> 13 any need to re-index?

Wells Oliver-2
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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: Postgres 12 -> 13 any need to re-index?

Wells Oliver-2
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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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
[hidden email]


--
Wells Oliver
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: Postgres 12 -> 13 any need to re-index?

David G Johnston
On Tuesday, February 9, 2021, Wells Oliver <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: Postgres 12 -> 13 any need to re-index?

Ron-2
In reply to this post by Wells Oliver-2

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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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
[hidden email]


--
Wells Oliver
[hidden email]

--
Angular momentum makes the world go 'round.
Reply | Threaded
Open this post in threaded view
|

Re: Postgres 12 -> 13 any need to re-index?

Fernando Hevia-2
In reply to this post by Wells Oliver-2


El mar, 9 de feb. de 2021 a la(s) 15:50, Wells Oliver ([hidden email]) 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.