Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1l9YXf-0002iL-Lo for pgsql-admin@arkaria.postgresql.org; Tue, 09 Feb 2021 19:19:19 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1l9YXe-0002iQ-Fp for pgsql-admin@arkaria.postgresql.org; Tue, 09 Feb 2021 19:19:18 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1l9YXe-0002hz-0a for pgsql-admin@lists.postgresql.org; Tue, 09 Feb 2021 19:19:18 +0000 Received: from mail-ot1-x332.google.com ([2607:f8b0:4864:20::332]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1l9YXa-0000JA-JK for pgsql-admin@lists.postgresql.org; Tue, 09 Feb 2021 19:19:16 +0000 Received: by mail-ot1-x332.google.com with SMTP id y11so18535478otq.1 for ; Tue, 09 Feb 2021 11:19:14 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=j3YL5M/+LnMLx85/fqcCP5lLaOk/IucUUZLsNE2YJyY=; b=rzyMBfDhKHqxFn2k+EBnKz2mRqJzIeStuPGhDNBovltT+JLCYa7axmHylxRbae3wZB mTlAVAaQO48I8MCCnvzrXS5Y1123T1X6KRVrLBdeBiuPnkBfl3KNJqYUpwNtw5Yh6g+B 4v8f4LV2ouXpqQFLtQ7+hFZslozEhn5/B527op0IewTG2uxARf39thNlsGNiIPvrxxr4 NZTaFTBDukWkI0cUFR+uIYyEu73/0GoKuw3i8SBnI/MfYF3+axqB4vWKoWC59wb8KMDt +swwSGgG+HPrdx8kXBNkQG8nW+fdFKasbeJTnCCWZ9lab02hwgq4KhXqATOVl9R27H7a z3lw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=j3YL5M/+LnMLx85/fqcCP5lLaOk/IucUUZLsNE2YJyY=; b=nb+/n5LViteso7VvevmP/gGhC5LqE1ApnpwVZd3UB6w2qFvQfMabFp18W1adMNLKef yru67xGoJ/pKaOropID3p8qRhGCcQPg07QW/s9SoBBep9SeyyE2iFifLULSowGjtl8NP IQZ4x/djnWeVoszGIu4AydRUV/ZRgSkX5VXGjeoD2O+dV6Psh3DNZxBPxuQNJwMvCwFY sMYIZdu8NwCve6ZPSHccCRmvkHew1mlk3bGC/qmVmGJT4BF9UO12LRgzDqQlb7RAuUC7 JxwJ0J+tPUg8a4aVauXb+PEUGcJCGnyQZZS2qaeqGIOnKS75wQkZoJ+d2v/lXKHIaNrt 6/fQ== X-Gm-Message-State: AOAM533qOYirCZybt7m+RuaSvt2uFcGvBv2LAhP6d4KwnUj93RFXxfNj ErCyaXRlV8tjvA5OF7UO06ji8dCINcjIQQ== X-Google-Smtp-Source: ABdhPJwdEyd5LbmJlCFiwzH90bOQHcDshkehWqqXu8uKSgre3KW9PhNOnvStxRn9lkL0/Nh4B6Nrrg== X-Received: by 2002:a9d:7a54:: with SMTP id z20mr10968301otm.233.1612898353082; Tue, 09 Feb 2021 11:19:13 -0800 (PST) Received: from [192.168.88.10] (ip174-67-141-204.no.no.cox.net. [174.67.141.204]) by smtp.googlemail.com with ESMTPSA id e72sm146566ote.26.2021.02.09.11.19.11 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 09 Feb 2021 11:19:12 -0800 (PST) Subject: Re: Postgres 12 -> 13 any need to re-index? To: pgsql-admin@lists.postgresql.org References: <8f24ff9a-059e-ee95-38bf-07e5b852d226@gmail.com> <4c1d3278-b5cc-c432-c2c5-12b3c453ab79@gmail.com> <5cdc65bb-a095-a161-06d6-cfd73faeaf3b@gmail.com> From: Ron Message-ID: Date: Tue, 9 Feb 2021 13:19:11 -0600 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101 Thunderbird/68.10.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------A3099549A0B02E8E4B386BEC" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------A3099549A0B02E8E4B386BEC Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit 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 > 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 > 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 > > 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 >> > 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. --------------A3099549A0B02E8E4B386BEC Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit
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.


--


--

--
Angular momentum makes the world go 'round.
--------------A3099549A0B02E8E4B386BEC--