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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
