Re: [GENERAL] 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 cr...@postnewspapers.com.auwrote: 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
Re: [GENERAL] libpq performance
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/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] libpq performance
Hi, I have a sql that only takes 0.3 ms to run when using psql with explain analyze. However, when I execute it using PQexec, it takes 12ms for PGexec. Does anyone know why it is that slow? My db server is in the internal network, so there should not be any latency issue. Any suggestion will be greatly appreciated. Thanks, JB
Re: [GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
