On Sun, Apr 1, 2012 at 6:11 PM, Andrew Dunstan
<andrew@dunslane.net> wrote:
On 04/01/2012 08:29 PM, Claudio Freire wrote:
On Sun, Apr 1, 2012 at 8:54 PM, Andrew Dunstan<andrew@dunslane.net> wrote:
You could try using Unix domain socket and see if the performance
improves. A relevant link:
He said Windows. There are no Unix domain sockets on Windows. (And please
don't top-post)
Windows supports named pipes, which are functionally similar, but I
don't think pg supports them.
But suggesting moving away from TCP/IP with no actual evidence that it is network overhead that is the problem is a little premature, regardless. What, exactly, are the set of operations that each update is performing and is there any way to batch them into fewer statements within the transaction. For example, could you insert all 60,000 records into a temporary table via COPY, then run just a couple of queries to do bulk inserts and bulk updates into the destination tble via joins to the temp table? 60,000 rows updated with 25 columns, 1 indexed in 3ms is not exactly slow. That's a not insignificant quantity of data which must be transferred from client to server, parsed, and then written to disk, regardless of TCP overhead. That is happening via at least 60,000 individual SQL statements that are not even prepared statements. I don't imagine that TCP overhead is really the problem here. Regardless, you can reduce both statement parse time and TCP overhead by doing bulk inserts (COPY) followed by multi-row selects/updates into the final table. I don't know how much below 3ms you are going to get, but that's going to be as fast as you can possibly do it on your hardware, assuming the rest of your configuration is as efficient as possible.