Re: Recursive query performance issue

On Tue, Oct 20, 2015 at 12:34 PM, Jamie Koceniak
<jkoceniak(at)mediamath(dot)com> wrote:
> Version:
>
> -----------------------------------------------------------------------------------------------
>
> PostgreSQL 9.1.14 on x86_64-unknown-linux-gnu, compiled by gcc (Debian
> 4.7.2-5) 4.7.2, 64-bit
>
> Query Plan
>
> http://explain.depesz.com/s/4s37
>
> Normally, this query takes around 200-300 ms to execute.
>
> However when several queries are run concurrently, query performance drops
> to 30-60 seconds.

Please define 'several'. Essential information here is a capture of
'top' and possibly 'perf top'. Also if the problem is storage related
iostat can be very useful (or vmstat in a pinch)

FYI you can use pgbench with -f mode to measure concurrency
performance of any query.

The very first thing to rule out is a storage bottleneck via measured
iowait. Assuming that's the case, this problem is interesting if:
*) Scaling is much worse than it should be
*) You can confirm this on more modern postgres (interesting problems
are only interesting if they are unsolved)

merlin