Greetings -
I am really love Postgres and do enjoy hacking around it but I just
met a libpq performance issue that I would like to get your help with.
I have Postgres 8.0.1 running on Linux version 2.6.10-1.771_FC2.
I have an application that makes queries to Postgres. I want to keep
the database code in this application as simple as possible because
the application is doing lots of other complex things (it is an IP
Telephony server). So for the sake of simplicity, I do not "prepare"
SQL statements before submitting them to database.
Now I have 2 ways to access Postgres - one through unixODBC, the
other through libpq. I found that libpq performance is nearly 2 times
slower than performance of unixODBC. Specifically, I have a
multithreaded
test program (using pthreads), where I can run a specified number of
simple queries like this:
SELECT * FROM extensions WHERE
(somecolumn IS NOT NULL)
AND (id IN (SELECT extension_id FROM table 2))
The test program can submit the queries using unixODBC or libpq.
With 1 execution thread I have these performance results:
ODBC: does 200 queries in 2 seconds (100.000000 q/s)
libpq: does 200 queries in 3 seconds (66.666667 q/s)
With 2 threads the results are:
ODBC: does 200 queries in 3 seconds (66.666667 q/s)
Libpq: does 200 queries in 6 seconds (33.333333 q/s)
With 3 threads:
ODBC: does 200 queries in 5 seconds (40.000000 q/s)
Libpq: 200 queries in 9 seconds (22.222222 q/s)
Obviously libpq is slower.
Do you have any ideas why libpq is so much slower than unixODBC?
Where do you think the libpq bottleneck is? Are there any libpq
options (compile time or runtime) that can make it faster?
Respectfully
Constantine