From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kenneth Marshall <ktm@rice.edu>
cc: pgsql-performance@lists.postgresql.org
Subject: Re: PostgreSQL performance problem moving from 9.6.17 to 12.3
In-reply-to: <20200528155659.GI13462@aart.rice.edu>
References: <20200528155659.GI13462@aart.rice.edu>
Comments: In-reply-to Kenneth Marshall <ktm@rice.edu>
	message dated "Thu, 28 May 2020 10:56:59 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <7006.1590684164.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 28 May 2020 12:42:44 -0400
Message-ID: <7007.1590684164@sss.pgh.pa.us>
Precedence: bulk

Kenneth Marshall <ktm@rice.edu> writes:
> I have a system that was running version 9.6.17 running on a system with
> 48gb of memory and spinning disks front-ed by a HW RAID controller with
> NVRAM cache. We moved to a new box running version 12.3 on a system with
> 64gb of memory and NVME SSD drives. Here are the system config options:

> OLD:
> shared_buffers =3D 2048MB                 # min 128kB
> work_mem =3D 128MB                        # min 64kB
> maintenance_work_mem =3D 1024MB           # min 1MB
> effective_io_concurrency =3D 8            # 1-1000; 0 disables prefetchi=
ng
> max_parallel_workers_per_gather =3D 0     # taken from max_worker_proces=
ses
> effective_cache_size =3D 24GB
> default_statistics_target =3D 500         # range 1-10000
> from_collapse_limit =3D 30
> join_collapse_limit =3D 30                # 1 disables collapsing of exp=
licit
> seq_page_cost =3D 1.0			# measured on an arbitrary scale
> random_page_cost =3D 4.0			# same scale as above

> NEW:
> shared_buffers =3D 12GB                   # min 128kB
> work_mem =3D 128MB                        # min 64kB
> maintenance_work_mem =3D 2GB              # min 1MB
> effective_io_concurrency =3D 200          # 1-1000; 0 disables prefetchi=
ng
> max_worker_processes =3D 24               # (change requires restart)
> max_parallel_workers_per_gather =3D 4     # taken from max_parallel_work=
ers
> max_parallel_workers =3D 24               # maximum number of max_worker=
_processes that
> seq_page_cost =3D 1.0                     # measured on an arbitrary sca=
le
> random_page_cost =3D 1.1                  # same scale as above for SSDs
> effective_cache_size =3D 36GB
> default_statistics_target =3D 500         # range 1-10000
> from_collapse_limit =3D 30
> join_collapse_limit =3D 30                # 1 disables collapsing of exp=
licit

Maybe you should be changing fewer variables at one time ...

In particular, decreasing random_page_cost as you've done here is
going to encourage the planner to rely on nestloop-with-inner-indexscan
joins.  Does undoing that change improve matters?

I personally think that v12 is way too enthusiastic about invoking
JIT compilation, too.  You might want to play with the parameters
for that as well.

			regards, tom lane