Lists: | pgsql-bugs |
---|
From: | "Pavel Golub" <pavel(at)microolap(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #4079: libpq.dll very slow (unusable) |
Date: | 2008-04-01 14:03:22 |
Message-ID: | 200804011403.m31E3MSD058056@wwwmaster.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
The following bug has been logged online:
Bug reference: 4079
Logged by: Pavel Golub
Email address: pavel(at)microolap(dot)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.
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Pavel Golub" <pavel(at)microolap(dot)com> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #4079: libpq.dll very slow (unusable) |
Date: | 2008-04-02 18:38:33 |
Message-ID: | 12337.1207161513@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
"Pavel Golub" <pavel(at)microolap(dot)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
From: | Pavel Golub <pavel(at)microolap(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #4079: libpq.dll very slow (unusable) |
Date: | 2008-04-03 09:13:04 |
Message-ID: | 171232314.20080403121304@gf.microolap.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
Hello, Tom.
Thank a lot Tom.
Just curious, what was the reason?
Regards
You wrote:
TL> "Pavel Golub" <pavel(at)microolap(dot)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(at)gf(dot)microolap(dot)com
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Pavel Golub <pavel(at)gf(dot)microolap(dot)com> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #4079: libpq.dll very slow (unusable) |
Date: | 2008-04-03 14:26:53 |
Message-ID: | 29322.1207232813@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
Pavel Golub <pavel(at)microolap(dot)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