Re: [PATCHES] 8.2 features?

Tom Lane wrote:
> Joe Conway <mail(at)joeconway(dot)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