performance degredation after upgrade from 9.6 to 12

Поиск
Список
Период
Сортировка
Hey all,
I'm testing performance of two identical machines one in 9.6 and the second one is in 12. The second machine is a clone of the first one + db upgrade to 12 beta 3 (Yes I'm aware 12.1 was released).

machine stats : 
32gb ram
8 cpu
regular hd (not ssd)

my postgresql.confg settings: 

max_wal_size = 2GB
min_wal_size = 1GB
wal_buffers = 16MB
checkpoint_completion_target = 0.9
checkpoint_timeout = 30min
log_checkpoints = on
log_lock_waits = on
log_temp_files = 1024
log_min_duration_statement = 1000
log_autovacuum_min_duration = 5000
autovacuum_max_workers = 4
autovacuum_vacuum_cost_limit = 800
autovacuum_vacuum_cost_delay = 10ms
standard_conforming_strings = off
max_locks_per_transaction = 5000
max_connections = 500
log_line_prefix = '%t %d %p  '
random_page_cost = 2.0
deadlock_timeout = 5s
shared_preload_libraries = 'pg_stat_statements'
track_activity_query_size = 32764
maintenance_work_mem = 250MB
work_mem = 32MB
shared_buffers = 8058MB
effective_cache_size = 16116MB

in 12v I also added the following settings : 
log_directory = 'pg_log'
enable_partitionwise_join = on
enable_partitionwise_aggregate = on
max_worker_processes = 8               # (change requires restart)
max_parallel_workers_per_gather = 4    # taken from max_parallel_workers
max_parallel_workers = 8               # maximum number of max_worker_pr

I tested a few applications flows and I saw that the 9.6 version is faster. I also did a few simple tests (enabled \timing) : 

12v : 
postgres=# create table test1 as select generate_series(1,10000);
SELECT 10000
Time: 35.099 ms

postgres=# select count(*) from test1;
 count
-------
 10000
(1 row)

Time: 4.819 ms

9.6v : 
postgres=# create table test1 as select generate_series(1,10000);
SELECT 10000
Time: 19.962 ms

postgres=# select count(*) from test1;
 count
-------
 10000
(1 row)

Time: 1.541 ms

Any idea what can cause it ? What can I check? 
This degredation is visible in many queries that we use ..

After the upgrade to 12v version I run analyze on all tables..

Thanks.

В списке pgsql-admin по дате отправления: