It sounds strange but the "type" is indeed impacting the overall
performance somehow.
I've just tried to execute the following sequence of commands on a
fresh new database with PostreSQL v10 and both the copy and primary
key commands performed as slow as in v11 and 12.

SET synchronous_commit TO OFF;
SET client_encoding TO 'UTF8';
COPY ways FROM program 'cmd /c "type D:\ways.txt"';
ALTER TABLE ONLY ways ADD CONSTRAINT pk_ways PRIMARY KEY (id);

Regards

пт, 29 нояб. 2019 г. в 15:22, Eugene Podshivalov <yauge...@gmail.com>:
>
> Laurenz,
> There is no way to run copy without the "type" on v11. See this thread
> https://www.postgresql.org/message-id/CAKHmqNCfTMM6%3DPqc6RUMEQ_2BPfo5KGGG-0fzRXZCVooo%3DwdNA%40mail.gmail.com
>
> My machine is running on NVMe disks, so the I/O subsystem very strong.
> The 100% overload is not constant but periodical, as if there are some
> kind of dumps for recovery performed in the background.
>
> maintenance_work_mem is the same in both cases.
>
> Regards
>
> пт, 29 нояб. 2019 г. в 15:04, Laurenz Albe <laurenz.a...@cybertec.at>:
> >
> > 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
> >


Reply via email to