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.cherkas...@gmail.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. ------------------ С уважением, Андрей Захаров
