Hello,
did you run ananlyze on your db?
Le dim. 24 nov. 2019 à 13:53, Mariel Cherkassky <mariel.cherkassky@xxxxxxxxx> a écrit :
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 ram8 cpuregular 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 = 16116MBin 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_prI 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 mspostgres=# select count(*) from test1;
count
-------
10000
(1 row)
Time: 4.819 ms9.6v :postgres=# create table test1 as select generate_series(1,10000);
SELECT 10000
Time: 19.962 mspostgres=# select count(*) from test1;
count
-------
10000
(1 row)
Time: 1.541 msAny 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.
