Re: Query performance with min and filter

"Yorwerth, Adam" <Adam(dot)Yorwerth(at)tesco(dot)com> writes:
> We seem to have found a situation where a query run using explain analyse or pgbench is incredibly fast, but run via Java under load performs very poorly – we’ve checked query performance metrics for our Postgres instance and can confirm that it’s the query running slowly, and not a Java problem.

Usually when people report something like this, the explanation is that
it's not actually the same query being tested in both cases.

In particular, if you're parameterizing the query in Java but substituting
constant values when running it manually, that can make a difference.

A further-afield possibility is that your application has been set up to
run with custom values of work_mem, planner cost constants, etc, which
you're failing to duplicate in the manual tests.

If all else fails, try installing auto_explain so that you can capture
the actual query plan used when the application executes this query.

regards, tom lane