On Wed, Sep 15, 2010 at 6:52 PM, Arjen Nienhuis <a.g.nienhuis@xxxxxxxxx> wrote: > On Thu, Sep 16, 2010 at 12:37 AM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote: >> On Wed, Sep 15, 2010 at 6:18 PM, Arjen Nienhuis <a.g.nienhuis@xxxxxxxxx> >> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
