Hello, did you run ananlyze on your db? Le dim. 24 nov. 2019 à 13:53, Mariel Cherkassky <mariel.cherkas...@gmail.com> 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 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. >
