libpq performance

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

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@postnewspapers.com.au>wrote:

Juan Backson wrote:

Hi,

For this problem, what if I use prepared statement? if I use prepared
statement directly from libpq, would it help?

It will possibly change the way postgres plans the query, so you may get
a different execution time.

http://www.postgresql.org/docs/current/static/sql-prepare.html

See the "Notes" section.

--
Postgresql & php tutorials
http://www.designmagick.com/