Message-ID: <44BF0382.2010501@joeconway.com>
Date: Wed, 19 Jul 2006 21:16:02 -0700
From: Joe Conway <mail@joeconway.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.7.13) Gecko/20060501 Fedora/1.7.13-1.1.fc5
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: Christopher Kings-Lynne <chris.kings-lynne@calorieking.com>,
	"Hackers (PostgreSQL)" <pgsql-hackers@postgresql.org>,
	Andrew Dunstan <andrew@dunslane.net>, Bernd Helmle <mailings@oopsware.de>,
	Peter Eisentraut <peter_e@gmx.net>,
	Susanne Ebrecht <susanne.ebrecht@credativ.de>
Subject: Re: [PATCHES] 8.2 features?
References: <44B69778.5050104@dunslane.net>
	<50461.84.63.113.55.1152832991.squirrel@pezone.net>
	<ECCAAC0A3182895D3D12B0E8@[172.26.14.247]>
	<44B7ADFC.90205@dunslane.net> <44BC0432.3040509@joeconway.com>
	<44BC661F.80609@joeconway.com> <44BCD06C.3060409@calorieking.com>
	<44BCF1D4.3010807@dunslane.net> <5842.1153246741@sss.pgh.pa.us>
	<44BD8854.9060506@calorieking.com> <15212.1153279954@sss.pgh.pa.us>
	<44BDB2F5.7080003@joeconway.com> <44BEA710.6060700@joeconway.com>
	<23198.1153367887@sss.pgh.pa.us>
In-Reply-To: <23198.1153367887@sss.pgh.pa.us>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Tom Lane wrote:
> Joe Conway <mail@joeconway.com> writes:
> 
>>I did some testing today against mysql and found that it will easily 
>>absorb insert statements with 1 million targetlists provided you set 
>>max_allowed_packet high enough for the server. It peaked out at about 
>>600MB, compared to my test similar last night where it was using about 
>>3.8 GB when I killed it.
> 
>>So the question is, do we care?
> 
> What's the performance like relative to mysql?  It seems hard to believe
> that we can afford the overhead of a separate INSERT statement per row
> (duplicating all the work of parse analysis, rewrite, planning, executor
> start/stop) ... at least not without looking mighty bad.

I don't have the exact numbers handy, but not too great.

As I recall, with last night's patch we did 100K inserts in about 4 
seconds, and today mysql did 100K in about 1 second. We never finished 
the 1 million insert test due to swapping (I killed it after quite a 
while), and mysql did 1 million in about 18 seconds (we did 300K in 13 
seconds). The hardware was not identical between last night's test and 
today's on mysql, but very similar (similar CPUs and memory, although 
the machine I did the mysql tests on had scsi drives, while the pg test 
was done on sata).

The difficulty is finding a way to avoid all that extra work without a 
very ugly special case kludge just for inserts. I've been banging my 
head on that on-and-off for a few days now, and every idea looks uglier 
than the last. One suggestion I got off list was to figure out a way to 
build a tuplestore and use it to feed the executor. That's starting to 
sound better and better to me.

Any ideas or guidance would be greatly appreciated.

Joe