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 <[hidden email]> wrote: On 2/9/21 12:00 PM, Wells Oliver wrote: Wells Oliver [hidden email] |
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. |
In reply to this post by Wells Oliver-2
On 2/9/21 12:13 PM, Wells Oliver wrote:
Did you run an ANALYZE after the conversion?
--
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 <[hidden email]> wrote:
Wells Oliver [hidden email] |
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}
--
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 <[hidden email]> wrote:
Wells Oliver [hidden email] |
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:
Wells Oliver [hidden email] |
On Tuesday, February 9, 2021, Wells Oliver <[hidden email]> wrote:
Read the documentation?
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. |
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:
--
Angular momentum makes the world go 'round. |
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ó:
It didn't rebuild indexes. It scanned rows to update statistics thereby improving the planner's strategy to execute queries. |
Free forum by Nabble | Edit this page |