Kenneth Marshall <ktm@xxxxxxxx> 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 = 2048MB # min 128kB > work_mem = 128MB # min 64kB > maintenance_work_mem = 1024MB # min 1MB > effective_io_concurrency = 8 # 1-1000; 0 disables prefetching > max_parallel_workers_per_gather = 0 # taken from max_worker_processes > effective_cache_size = 24GB > default_statistics_target = 500 # range 1-10000 > from_collapse_limit = 30 > join_collapse_limit = 30 # 1 disables collapsing of explicit > seq_page_cost = 1.0 # measured on an arbitrary scale > random_page_cost = 4.0 # same scale as above > NEW: > shared_buffers = 12GB # min 128kB > work_mem = 128MB # min 64kB > maintenance_work_mem = 2GB # min 1MB > effective_io_concurrency = 200 # 1-1000; 0 disables prefetching > max_worker_processes = 24 # (change requires restart) > max_parallel_workers_per_gather = 4 # taken from max_parallel_workers > max_parallel_workers = 24 # maximum number of max_worker_processes that > seq_page_cost = 1.0 # measured on an arbitrary scale > random_page_cost = 1.1 # same scale as above for SSDs > effective_cache_size = 36GB > default_statistics_target = 500 # range 1-10000 > from_collapse_limit = 30 > join_collapse_limit = 30 # 1 disables collapsing of explicit 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
