Hi,
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.
CREATE TABLE ways (
id bigint NOT NULL,
version int NOT NULL,
user_id int NOT NULL,
tstamp timestamp without time zone NOT NULL,
changeset_id bigint NOT NULL,
tags hstore,
nodes bigint[]
);
Imported ways data from a file and added a primary key.
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?
Regards
