Aditya Rastogi <adirastogi@outlook.com> writes:
> I am running a postgreSQL database to which multiple C/C++ clients send queries via the libpq interface. On an
expensivequery doing multiple levels of aggregation on a large dataset, I observe that the CPU usage of the postmaster
processgoes up to 100 % and the query never completes. However, If I connect to the database via the psql terminal
clientand execute the same query on the same data set, it takes only a few milliseconds to complete (with the top
commandshowing only a momentary spike in the CPU usage of the postmaster process), irrespective of the size of the
underlyingdata set. Moreover , for a very small data set in the database , the same query never hangs when made through
thelibpq interface. I am puzzled by this difference in performance. Doesn't the psql client use the same libpq
interfaceto send the queries to the database ? Any help on where to start would be appreciated.
When we hear this sort of report, it always turns out that it's *not* in
fact the exact same query, or there's some other difference in the precise
execution context. Perhaps the programmatic use of the query is using
parameters, prepared statements, cursors, something like that? A straight
PQexec() of the exact same query string should certainly give the same
results psql gets, because that's what psql does.
It's fairly common to hear of cases in which a query gets much slower when
parameters are used in the WHERE/JOIN clauses, because the planner fails
to make some deduction that it does make when simple constants are written
instead. PG 9.2 and up have largely resolved this problem, I believe,
but if you're using an older version it's a hazard to be aware of.
regards, tom lane