Re: Difference in query performance when made from C/C++ client vs. psql client.

Aditya Rastogi <adirastogi(at)outlook(dot)com> writes:
> I am running a postgreSQL database to which multiple C/C++ clients send queries via the libpq interface. On an expensive query doing multiple levels of aggregation on a large dataset, I observe that the CPU usage of the postmaster process goes up to 100 % and the query never completes. However, If I connect to the database via the psql terminal client and execute the same query on the same data set, it takes only a few milliseconds to complete (with the top command showing only a momentary spike in the CPU usage of the postmaster process), irrespective of the size of the underlying data set. Moreover , for a very small data set in the database , the same query never hangs when made through the libpq interface. I am puzzled by this difference in performance. Doesn't the psql client use the same libpq interface to 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