On Tue, Apr 3, 2012 at 10:38 AM, Claudio Freire
<klaussfreire@gmail.com> wrote:
You perform 8 roundtrips minimum per event, so that's 375us per query.
It doesn't look like much. That's probably Nagle and task switching
time, I don't think you can get it much lower than that, without
issuing less queries (ie: using the COPY method).
I may be missing something stated earlier, but surely there are options in between 7 individual statements and resorting to COPY and temp tables.
I'm thinking of a set of precompiled queries / prepared statements along the lines of "SELECT FOR UPDATE WHERE foo in (?, ?, ?, .... ?)" that handle e.g. 500-1000 records per invocation. Or what about a stored procedure that updates one record, performing the necessary 7 steps, and then calling that in bulk?
I agree with the assessment that 375us per statement is pretty decent, and that going after the communication channel (TCP vs local pipe) is chasing pennies when there are $100 bills lying around waiting to be collected.