Re: unstable SELECT performance under load

Piotr Foremniak <piotr(dot)foremniak(at)gmail(dot)com> writes:
> When executing some quite complex select queries our 8.3.5 Postgres
> often hangs. The query that we used to reproduce this problem on our
> isolated test environment is a quite complex join of ca.15 tables.
> When executed only once, it always takes ca. 500ms to finish. When
> executed by a JMeter stress test running a couple of threads
> simultaneously, ca. 95% of queries also take 500ms to finish. In the
> remaining 5% cases something weird happens and the same query finishes
> after several minutes, with the postgres process generating gigabytes
> of temp files and consuming 100% processor resources.

Hmm, are you *sure* you have geqo = off? Because this sure sounds like
geqo search sometimes getting lost in the weeds and picking a bad plan.
If it's really exactly the same query each time then you should be
getting the same plan and more or less the same execution time each
time.

Another possibility is that when you say "same query" you are ignoring
changes in parameter values that could result in the plan being
inappropriate for the new values.

regards, tom lane