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.
