From: | <wespvp(at)syntegra(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Slow network retrieves |
Date: | 2004-05-09 17:43:16 |
Message-ID: | BCC3D7E4.8181%wespvp@syntegra.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 5/9/04 9:32 AM, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Are you sure it is a network problem? What performance do you get
> if you run the same test program locally on the database machine?
> How about issuing the same sort of FETCH commands via a psql script?
Yes, it is definitely due to the network latency even though that latency is
very small. Here it is running locally:
05-09-2004.17:49:41 Records read: 10000
05-09-2004.17:49:41 Records read: 20000
05-09-2004.17:49:42 Records read: 30000
05-09-2004.17:49:42 Records read: 40000
05-09-2004.17:49:43 Records read: 50000
05-09-2004.17:49:43 Records read: 60000
05-09-2004.17:49:44 Records read: 70000
05-09-2004.17:49:45 Records read: 80000
05-09-2004.17:49:45 Records read: 90000
05-09-2004.17:49:46 Records read: 100000
05-09-2004.17:49:46 Records read: 110000
05-09-2004.17:49:47 Records read: 120000
05-09-2004.17:49:47 Records read: 130000
05-09-2004.17:49:48 Records read: 140000
My "outside looking in" observations seem to point to the fact that every
row has to be retrieved (or stored) with a separate request. Network
latency, however small, becomes an issue when the volume is very high.
A Pro*C program I recently ported from Oracle to PostgreSQL showed this
difference. In Pro*C you can load an array with rows to insert, then issue
a single INSERT request passing it the array. As far as I can tell, in
PostgreSQL ecpg (or other) you have to execute one request per record.
Is there some way to batch insert/fetch requests? How else can I improve
upon the performance? It appears that COPY works like this, but you can't
control what is returned and you have to know the column order.
Wes
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2004-05-09 18:58:46 | Re: Slow network retrieves |
Previous Message | Alvaro Herrera | 2004-05-09 16:09:49 | Re: Adding MERGE to the TODO list (resend with subject) |