[GENERAL] Should PQconsumeInput/PQisBusy be expensive to use?

[Michael Clark]

Hello everyone.

I have been investigating the PG async calls and trying to determine whether
I should go down the road of using them.

In doing some experiments I found that using
PQsendQueryParams/PQconsumeInput/PQisBusy/PQgetResult produces slower
results than simply calling PQexecParams.
Upon some investigation I found that not calling PQconsumeInput/PQisBusy
produces results in line with PQexecParams (which PQexecParams seems to be
doing under the hood).

I profiled my test and found this calling stack:
(This is OS X 10.6)

lo_unix_scall
   recvfrom$UNIX2003
      recv$UNIX2003
         pqsecure_read
            pqReadData
               PQconsumeInput
                  .....


This showed up as the hottest part of the execution by far.  This was a
pretty simple test of fetching 6000+ rows.

If I remove the PQconsumeInput/PQisBusy calls, which essentially makes the
code blocking this hot spot goes away.

Fetching 1000 rows goes from <.5 seconds to >3 seconds when I have the
PQconsumeInput/PQisBusy calls in.


I was wondering if maybe I am doing something wrong, or if there is a
technique that might help reduce this penalty?

Thanks in advance for any suggestions,
Michael.

P.S. here is a code snippet of what I am doing basically:
(please keep in mind this is just test code and rather simplistic...)

    int send_result = PQsendQueryParams(self.db,
                                        [sql UTF8String],
                                        i,
                                        NULL,
                                        (const char *const *)vals,
                                        (const int *)lens,
                                        (const int *)formats,
                                        kTextResultFormat);
    int consume_result = 0;
    int is_busy_result = 0;

    while ( ((consume_result = PQconsumeInput(self.db)) == 1) &&
((is_busy_result = PQisBusy(self.db)) == 1) )
        ;

    if (consume_result != 1)
        NSLog(@"Got an error in PQconsumeInput");

    PGresult* res = PQgetResult(self.db);
    while (PQgetResult(self.db) != NULL)
        NSLog(@"Oops, seems we got an extra response?");