Frits, When you use the copy command, are you doing anything special to get the run time that you are indicating?
On Fri, Jun 9, 2017 at 10:39 AM, Frits Jalvingh <j...@etc.to> wrote: > Hi all, > > Thanks a lot for the many responses! > > About preparing statements: this is done properly in Java, and pgsql does > it by itself. So that cannot be done better ;) > > I tried the copy command, and that indeed works quite brilliantly: > Inserted 24000000 rows in 22004 milliseconds, 1090710.7798582076 > <(779)%20858-2076> rows per second > > That's faster than Oracle. But with a very bad interface I have to say for > normal database work.. I will try to make this work in the tooling, but it > needs some very special code to format all possible values properly, and to > manage the end of the copy, so it is not usable in general which is a pity, > I think. > > So, I am still very interested in getting normal inserts faster, because > that will gain speed for all work.. If Oracle can do it, and Postgres is > able to insert fast with copy- where lies the bottleneck with the insert > command? There seems to be quite a performance hit with the JDBC driver > itself (as the stored procedure is a lot faster), so I can look into that. > But even after that there is quite a gap.. > > Regards, > > Frits > > On Fri, Jun 9, 2017 at 4:33 PM Scott Marlowe <scott.marl...@gmail.com> > wrote: > >> On Fri, Jun 9, 2017 at 7:56 AM, Frits Jalvingh <j...@etc.to> wrote: >> > Hi Kenneth, Andreas, >> > >> > Thanks for your tips! >> > >> > I increased shared_buffers to 8GB but it has no measurable effect at >> all. I >> > think that is logical: shared buffers are important for querying but >> not for >> > inserting; for that the speed to write to disk seems most important- no >> big >> > reason to cache the data if the commit requires a full write anyway. >> > I also changed the code to do only one commit; this also has no effect >> I can >> > see. >> > >> > It is true that Oracle had more memory assigned to it (1.5G), but unlike >> > Postgres (which is completely on a fast SSD) Oracle runs on slower disk >> > (ZFS).. >> > >> > I will try copy, but I first need to investigate how to use it- its >> > interface seems odd to say the least ;) I'll report back on that once >> done. >> >> I you want an example of copy, just pg_dump a table: >> >> pg_dump -d smarlowe -t test >> >> (SNIP) >> COPY test (a, b) FROM stdin; >> 1 abc >> 2 xyz >> \. >> (SNIP) >> >
