Re: performance degredation after upgrade from 9.6 to 12

Hi there -
I have same feelings. Try set max_parallel_workers_per_gather to zero. I don't think that comparison non-parallel and parallel versions is correct (don't say anything about parallel in 9.6 pls)
What explain says? I suppose you will have different exec plans. Optimizer stranges of 11 and 12 ver have been discussed. Look thru the archive, but I didn't remember the problem status - resolved or not.
Andrew.

24 ноября 2019 г. 15:53:19 GMT+03:00, Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com> пишет:
>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.

------------------
С уважением,
Андрей Захаров