feeding big script to psql

From: Havasvölgyi Ottó <h(dot)otto(at)freemail(dot)hu>
To: <pgsql-general(at)postgresql(dot)org>
Subject: feeding big script to psql
Date: 2005-08-02 01:11:22
Message-ID: 001101c596ff$18d227e0$8000a8c0@OTTO
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

A generated a big SQL script (about 20 Mb), and fed it to psql. I was very
surprised that within a minute psql became quite slow. There were areas,
where less than 10 row were inserted in a second.
This is on a WinXP machine with local server 8.0.3, and only I use it.
Looking at the log files of PG the commands are executed fast.
I have monitored psql with te TaskManager, and it was at 45-50% CPU (this is
Hyperthreaded, so 50% is the max for one thread), and the memory
allocation/deallocation was very active, even about +-2Mb/sec.
The command were simple create table and insert commands chunked into
several lines like this:

CREATE TABLE aeloleg(
vevo CHAR(6),
szallito INTEGER,
datum DATE,
hatarido DATE,
vevo_nev CHAR(30),
ir_szam INTEGER,
helyseg CHAR(20),
cim CHAR(20),
befizetes INTEGER,
terheles INTEGER,
hitel INTEGER,
rendeles INTEGER,
jel CHAR(1),
trans INTEGER,
szoveg TEXT,
storno BOOLEAN) WITHOUT OIDS;

The insert commands for one table were surrounded by BEGIN and COMMIT like
this:

CREATE
BEGIN
INSERT
INSERT
...
INSERT
COMMIT

I know it would be faster with COPY, but this is extremly slow, and the
bottleneck is psql.
What is the problem?

Regards,
Otto

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message hisatomo 2005-08-02 01:28:19 could not bind IPv6 socket
Previous Message Tom Lane 2005-08-02 00:32:17 Re: Check postgres compile-time options