Subject: Re: performance drop after upgrade (9.6 > 10)
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: Pavel Stehule <pavel.stehule@gmail.com>,
 "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
References: 
 <CA++JNSeCW1BSRVS7N--OmVOMDiMVA61_DFMA24fFbM2R7JdQjQ@mail.gmail.com>
 <CAFj8pRDnW+bD5OmWjawxryv_=BctsE8HF5Y1n=oxgQ+xwrVqUQ@mail.gmail.com>
 <CA++JNScUoxYKBhURxbG_DzoUfgKJup4a4TXUTiG1FFjuq8X-VQ@mail.gmail.com>
 <CA++JNSfTEifSiBq73zpGCQuDNGr9yYcjmkx_=LtAbnadKLY_vg@mail.gmail.com>
 <20171024151803.GH21735@telsasoft.com>
From: =?UTF-8?Q?Johannes_Gra=c3=abn?= <johannes@selfnet.de>
Message-ID: <e0592313-750d-64ea-84cf-699471956729@selfnet.de>
Date: Tue, 24 Oct 2017 17:57:31 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.4.0
MIME-Version: 1.0
In-Reply-To: <20171024151803.GH21735@telsasoft.com>
Content-Type: text/plain; charset=utf-8
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 2017-10-24 17:18, Justin Pryzby wrote:
> You could (re)install PG96 alongside PG10 and run a copy of the DB (even from
> your homedir, or on a difference server) and pg_dump |pg_restore the relevant
> tables (just be sure to specify the alternate host/port/user/etc as needed for
> the restore invocation).

I considered that but it is far too expensive just for getting the old
query plan. The database is more than 1 TB big and replaying it from a
dump to another server took us several days, primarily due to the heavy
use of materialized views that are calculated over all rows of some
large tables. As long as there is no safe pg_downgrade --link I'd rather
keep trying to improve query performance on the current version.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance