Re: performance degredation after upgrade from 9.6 to 12

Hello,
did you run ananlyze on your db?

Le dim. 24 nov. 2019 à 13:53, Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)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.
>