Hi, For this problem, what if I use prepared statement? if I use prepared statement directly from libpq, would it help?
Thanks, JB On Mon, Aug 24, 2009 at 9:59 AM, Craig Ringer <cr...@postnewspapers.com.au>wrote: > On Mon, 2009-08-24 at 00:08 +0800, Juan Backson wrote: > > > I have a sql that only takes 0.3 ms to run when using psql with > > "explain analyze". > > "explain analyze" reports server-side execution time. > > > However, when I execute it using PQexec, it takes 12ms for PGexec. > > Does anyone know why it is that slow? > > ... and if you're timing PQexec you're probably taking the client-side > time, ie the time from start of PQexec call to the time the PQexec call > returns. > > That means that network latency *is* a factor, albeit a small one. I > don't think EXPLAIN ANALYZE will report any delays due to lock > acquisition or anything like that either. However, most of the > difference probably comes from the time taken to parse and plan the > statement. It'd help if you actually provided the EXPLAIN ANALYZE output > and the statement in question so there was less guesswork involved. > > > My db server is in the internal network, so there should not be any > > latency issue. > > Rather than assuming that, I'd recommend measuring it: > > - Run the test program on the DB server with a connection over the > loopback interface (127.0.0.1); and > - if the DB server is UNIX based, run the test program on the DB server > with a connection over a UNIX socket; and > - Use Wireshark to examine the actual network traffic to see how big a > gap there is between request and response > > However, as I said above I personally expect the difference is mostly in > parsing and planning time. > > There are ways to reduce planning time (at the cost of potentially > inferior query plans) - but if you're really that worried about query > execution time, might you perhaps be executing a huge number of tiny > queries in a situation where one or two bigger queries can get the job > done more quickly? > > -- > Craig Ringer > > >