pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgres 12 -> 13 any need to re-index?
12+ messages / 5 participants
[nested] [flat]
* Postgres 12 -> 13 any need to re-index?
@ 2021-02-09 18:00 Wells Oliver <wells.oliver@gmail.com>
0 siblings, 2 replies; 12+ messages in thread
From: Wells Oliver @ 2021-02-09 18:00 UTC (permalink / raw)
To: pgsql-admin
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
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Postgres 12 -> 13 any need to re-index?
@ 2021-02-09 18:06 Ron <ronljohnsonjr@gmail.com>
parent: Wells Oliver <wells.oliver@gmail.com>
1 sibling, 1 reply; 12+ messages in thread
From: Ron @ 2021-02-09 18:06 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Postgres 12 -> 13 any need to re-index?
@ 2021-02-09 18:13 Wells Oliver <wells.oliver@gmail.com>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Wells Oliver @ 2021-02-09 18:13 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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 <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Postgres 12 -> 13 any need to re-index?
@ 2021-02-09 18:14 Thomas Kellerer <shammat@gmx.net>
parent: Wells Oliver <wells.oliver@gmail.com>
1 sibling, 0 replies; 12+ messages in thread
From: Thomas Kellerer @ 2021-02-09 18:14 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Postgres 12 -> 13 any need to re-index?
@ 2021-02-09 18:17 Ron <ronljohnsonjr@gmail.com>
parent: Wells Oliver <wells.oliver@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Ron @ 2021-02-09 18:17 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
> <mailto: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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Postgres 12 -> 13 any need to re-index?
@ 2021-02-09 18:18 Wells Oliver <wells.oliver@gmail.com>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Wells Oliver @ 2021-02-09 18:18 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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 <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Postgres 12 -> 13 any need to re-index?
@ 2021-02-09 18:25 Ron <ronljohnsonjr@gmail.com>
parent: Wells Oliver <wells.oliver@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Ron @ 2021-02-09 18:25 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
> <mailto: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
>> <mailto: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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Postgres 12 -> 13 any need to re-index?
@ 2021-02-09 18:27 Wells Oliver <wells.oliver@gmail.com>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Wells Oliver @ 2021-02-09 18:27 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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 <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Postgres 12 -> 13 any need to re-index?
@ 2021-02-09 18:49 Wells Oliver <wells.oliver@gmail.com>
parent: Wells Oliver <wells.oliver@gmail.com>
0 siblings, 3 replies; 12+ messages in thread
From: Wells Oliver @ 2021-02-09 18:49 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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 <wellsoliver@gmail.com>
>
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Postgres 12 -> 13 any need to re-index?
@ 2021-02-09 18:54 David G. Johnston <david.g.johnston@gmail.com>
parent: Wells Oliver <wells.oliver@gmail.com>
2 siblings, 0 replies; 12+ messages in thread
From: David G. Johnston @ 2021-02-09 18:54 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Postgres 12 -> 13 any need to re-index?
@ 2021-02-09 19:19 Ron <ronljohnsonjr@gmail.com>
parent: Wells Oliver <wells.oliver@gmail.com>
2 siblings, 0 replies; 12+ messages in thread
From: Ron @ 2021-02-09 19:19 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
> <mailto: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
> <mailto: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
>> <mailto: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
>>> <mailto: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 <mailto:wellsoliver@gmail.com>
>
>
>
> --
> Wells Oliver
> wells.oliver@gmail.com <mailto:wellsoliver@gmail.com>
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Postgres 12 -> 13 any need to re-index?
@ 2021-02-09 20:12 Fernando Hevia <fhevia@gmail.com>
parent: Wells Oliver <wells.oliver@gmail.com>
2 siblings, 0 replies; 12+ messages in thread
From: Fernando Hevia @ 2021-02-09 20:12 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
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.
>
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2021-02-09 20:12 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-02-09 18:00 Postgres 12 -> 13 any need to re-index? Wells Oliver <wells.oliver@gmail.com>
2021-02-09 18:06 ` Ron <ronljohnsonjr@gmail.com>
2021-02-09 18:13 ` Wells Oliver <wells.oliver@gmail.com>
2021-02-09 18:17 ` Ron <ronljohnsonjr@gmail.com>
2021-02-09 18:18 ` Wells Oliver <wells.oliver@gmail.com>
2021-02-09 18:25 ` Ron <ronljohnsonjr@gmail.com>
2021-02-09 18:27 ` Wells Oliver <wells.oliver@gmail.com>
2021-02-09 18:49 ` Wells Oliver <wells.oliver@gmail.com>
2021-02-09 18:54 ` David G. Johnston <david.g.johnston@gmail.com>
2021-02-09 19:19 ` Ron <ronljohnsonjr@gmail.com>
2021-02-09 20:12 ` Fernando Hevia <fhevia@gmail.com>
2021-02-09 18:14 ` Thomas Kellerer <shammat@gmx.net>
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