Thread: BUG #4079: libpq.dll very slow (unusable)

The following bug has been logged online:

Bug reference:      4079
Logged by:          Pavel Golub
Email address:      pavel@microolap.com
PostgreSQL version: 8.3.1
Operating system:   Windows XP SP2
Description:        libpq.dll very slow (unusable)
Details:

Comparing 8.3.1 to 8.2.5 release INSERT of >1000 rows is very slow.

CREATE TABLE numbers
(
  numberint integer NOT NULL,
  numberstr character varying(80),
  CONSTRAINT numbers_pkey PRIMARY KEY (numberint)
);

Inserts:
INSERT INTO numbers (numberint, numberstr) VALUES (1, 'One');
INSERT INTO numbers (numberint, numberstr) VALUES (2, 'Two');
INSERT INTO numbers (numberint, numberstr) VALUES (3, 'Three');
...
INSERT INTO numbers (numberint, numberstr) VALUES (1000, 'One thousand');

Program written on Delphi. Using libpq.dll 8.3.1.8075, and libpq 8.2.5.7260.
Both of them give the same results.

procedure Main(Db: TPSQLDatabase; SQL: string);
var
  conninfo: string;
  Conn: PGConn;
  Res: PGResult;
  cStart, cStop: cardinal;
begin
  with Db do
   Conninfo := Format('host = ''%s'' dbname = ''%s'' user = ''%s'' password
= ''%s'' port=''%d''',
          [Host, DatabaseName, UserName, UserPassword, Port]);

  Conn := PQConnectdb(PChar(ConnInfo));

  If PQStatus(Conn) <> CONNECTION_OK then
   begin
    ShowMessage('CAN''T START connection!');
    PQFinish(Conn);
    Exit;
   end;

   cStart:=GetTickCount();

   Res := PQExec(conn,PChar(SQL));
   if PQResultStatus(res) <> PGRES_COMMAND_OK then
       ShowMessage('CAN''T EXECUTE INSERTS');

  cStop:=GetTickCount();
  InfoMemo.Lines.Add('Execution time: '+IntToStr(cStop-cStart)+'ms');

  PQClear(res);
  PQFinish(Conn);
end;

Results with 10000 INSERTS:
- 984ms on PostgreSQL 8.2.7
- 25953ms on PostgreSQL 8.3.1

Result with 50000 INSERTS:
- 5063ms on PosgreSQL 8.2.7
- 26 minutes on PosgreSQL 8.3.1 (with 100% CPU usage)

The same behavior for pgAdmin III.

But psql works well when executing the request on both 8.2 and 8.3.

Tests were made under Windows XP only.

"Pavel Golub" <pavel@microolap.com> writes:
> Comparing 8.3.1 to 8.2.5 release INSERT of >1000 rows is very slow.

> INSERT INTO numbers (numberint, numberstr) VALUES (1, 'One');
> INSERT INTO numbers (numberint, numberstr) VALUES (2, 'Two');
> INSERT INTO numbers (numberint, numberstr) VALUES (3, 'Three');
> ...
> INSERT INTO numbers (numberint, numberstr) VALUES (1000, 'One thousand');

Fixed for 8.3.2.  In the meantime, you might consider modifying your
application to do this as a single insert instead:

INSERT INTO numbers (numberint, numberstr) VALUES
   (1, 'One'),
   (2, 'Two'),
   (3, 'Three'),
   ...
   (1000, 'One thousand')

A quick test says that this way is between three and four times faster,
even without considering the impact of the problem you're complaining
about.

            regards, tom lane

Hello, Tom.

Thank a lot Tom.

Just curious, what was the reason?

Regards

You wrote:

TL> "Pavel Golub" <pavel@microolap.com> writes:
>> Comparing 8.3.1 to 8.2.5 release INSERT of >1000 rows is very slow.

>> INSERT INTO numbers (numberint, numberstr) VALUES (1, 'One');
>> INSERT INTO numbers (numberint, numberstr) VALUES (2, 'Two');
>> INSERT INTO numbers (numberint, numberstr) VALUES (3, 'Three');
>> ...
>> INSERT INTO numbers (numberint, numberstr) VALUES (1000, 'One thousand');

TL> Fixed for 8.3.2.  In the meantime, you might consider modifying your
TL> application to do this as a single insert instead:

TL> INSERT INTO numbers (numberint, numberstr) VALUES
TL>    (1, 'One'),
TL>    (2, 'Two'),
TL>    (3, 'Three'),
TL>    ...
TL>    (1000, 'One thousand')

TL> A quick test says that this way is between three and four times faster,
TL> even without considering the impact of the problem you're complaining
TL> about.

TL>                         regards, tom lane


--
With best wishes,
 Pavel                          mailto:pavel@gf.microolap.com

Pavel Golub <pavel@microolap.com> writes:
> Just curious, what was the reason?

http://archives.postgresql.org/pgsql-hackers/2008-04/msg00044.php
http://archives.postgresql.org/pgsql-committers/2008-04/msg00007.php

            regards, tom lane