On Fri, 2019-11-29 at 13:04 +0300, Eugene Podshivalov wrote: > I'm using PostgreSQL on Windows for Planet OSM database and have > noticed considirable decrease in performance when upgrading from v10 > to 11 or 12. Here are the details of the experiment I conducted trying > to figure out what is causing the issue. > > Installed PostgreSQL 10 from scratch. Created a database and a table. > [...] > SET synchronous_commit TO OFF; > COPY ways FROM 'E:\ways.txt'; > ALTER TABLE ONLY ways ADD CONSTRAINT pk_ways PRIMARY KEY (id); > > The file is 365GB in size. > > The copy operation took 3.5h and the resulting table size is 253GB. > The primary key operation took 20 minutes and occuped 13GB of disk > space. > > Then I unstalled PostgreSQL v10, deleted the data directory and > installed v11 from scratch. Created the same kind of database and > table. v11 is not able to handle large files, so the I piped the data > through the cmd type command, and then added the primary key with the > same command as above. synchronous_commit turned off beforehand as > above. > > COPY ways FROM PROGRAM 'cmd /c "type E:\ways.txt"'; > > The copy operation took 7 hours and adding primary key took 1h 40m ! > The resulting table and pk sizes are the same as in v10. Also very > high load on disk drive (quite often at 100%) was observed. > > v12 performs the same as v11. > > Here are the changes in v11 default postgresql.conf file compared to > v10 one. Differences in Authentication, Replication and Logging > sections are skipped. > > -#replacement_sort_tuples = 150000 > +#max_parallel_maintenance_workers = 2 > +#parallel_leader_participation = on > ~max_wal_size = 1GB (in v10 is commented out) > ~min_wal_size = 80MB (in v10 is commented out) > +#enable_parallel_append = on > +#enable_partitionwise_join = off > +#enable_partitionwise_aggregate = off > +#enable_parallel_hash = on > +#enable_partition_pruning = on > +#jit_above_cost = 100000 > +#jit_inline_above_cost = 500000 > +#jit_optimize_above_cost = 500000 > +#jit = off > +#jit_provider = 'llvmjit' > +#vacuum_cleanup_index_scale_factor = 0.1 > > Any ideas pleaes on what is trapping the performance? Seems like you have a very weak I/O subsystem. For the COPY, try doing it the same way in both cases (without the "type"). For the index creation, perhaps set "max_parallel_maintenance_workers = 0" so that your system doesn't get overloaded. Is "maintenance_work_mem" set to the same value in both cases? Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
