On Wed, Sep 15, 2010 at 6:52 PM, Arjen Nienhuis <a.g.nienhuis@gmail.com> wrote:
> On Thu, Sep 16, 2010 at 12:37 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
>> On Wed, Sep 15, 2010 at 6:18 PM, Arjen Nienhuis <a.g.nienhuis@gmail.com>
>> wrote:
>> > Hi,
>> > Inserting many of rows is almost always IO bound. Converting ints and
>> > floats
>> > to text is CPU bound and really fast anyway. To speed things up first
>> > look
>> > at things like indexes, how often you need to COMMIT or using COPY. Only
>> > then look at prepared statements and binary transfer modes. Else it's
>> > simply
>> > not worth the headache.
>>
>> That's an awfully broad statement, and untrue...many applications are
>> cpu bound. It's easier to scale storage than cpu after a point. Also,
>> solid state storage is going to become increasingly common moving
>> forwards.
>>
>> Not all type receiving parsing is trivial as you claim; timestamps and
>> bytea for example are significantly cheaper to send in binary wire
>> format. Anyways, libpqtypes gives you all the advantages without all
>> the fuss. If you are really looking to shave cycles we allow you to
>> prepare the format string as well as prepare the statement before
>> sending it. We wrote this interface for a reason: I'd say on average
>> it cuts down query time around 20% on average in addition to the other
>> advantages it provides.
>>
>> merlin
>
> I think we can agree on one thing: trying to do it without libpqtypes is a
> bad idea.
> Groeten, Arjen
Yeah, except in the special case of bytea, so you can avoid
escaping/encoding and the fairly large efficiency hit. The
performance benefit of sending ints in binary vs text is barely
measurable. Timestamps are the biggest win of the common types,
especially if you are starting from something that is itself a binary
time representation.
Another reason a lot of people try and eek every cycle out of the
query they can (preparing the statement and such) is to try and cut
down query execution time as much as possible even in cases where it
doesn't make much difference in terms of overall system load. This
can happen for example when you have generated sql that crawls the
server record by record. We all know this is often not the best way,
but when working under those constraints the method of query execution
becomes quite important. exec prepared in binary vs exec params in
text can result in a greater than 50% reduction in query time in such
cases (some of that benefit is on the client). If you're firing off
big queries (in terms of running time), obviously all this matters
very little.
merlin