Re: libpq performance

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
<craig(at)postnewspapers(dot)com(dot)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
>
>
>