Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1ZpHBs-00042U-6M for pgsql-performance@arkaria.postgresql.org; Thu, 22 Oct 2015 14:50:04 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1ZpHBr-00069n-LD for pgsql-performance@arkaria.postgresql.org; Thu, 22 Oct 2015 14:50:03 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1ZpHAE-0004Jf-35 for pgsql-performance@postgresql.org; Thu, 22 Oct 2015 14:48:22 +0000 Received: from lb1-smtp-cloud6.xs4all.net ([194.109.24.24]) by makus.postgresql.org with esmtps (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84) (envelope-from ) id 1ZpHAB-00009m-1R for pgsql-performance@postgresql.org; Thu, 22 Oct 2015 14:48:20 +0000 Received: from [192.168.22.84] ([217.149.135.167]) by smtp-cloud6.xs4all.net with ESMTP id YEoC1r00c3csQft01EoETq; Thu, 22 Oct 2015 16:48:14 +0200 Subject: Re: Recursive query performance issue To: pgsql-performance@postgresql.org References: From: vincent elschot Message-ID: <5628F72E.2000005@xs4all.nl> Date: Thu, 22 Oct 2015 16:48:14 +0200 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101 Thunderbird/38.3.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------050907090606080002060504" X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org This is a multi-part message in MIME format. --------------050907090606080002060504 Content-Type: text/plain; charset=windows-1252; format=flowed Content-Transfer-Encoding: 7bit On 20-10-15 19:34, Jamie Koceniak 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. > Is the concurrency the cause or the result of the slowdown? Are you executing the same query with the same parameters or do the parameters differ, perhaps making PostgreSQL choose different queryplan? --------------050907090606080002060504 Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: 8bit

On 20-10-15 19:34, Jamie Koceniak 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.

Is the concurrency the cause or the result of the slowdown?
Are you executing the same query with the same parameters or do the parameters differ, perhaps making PostgreSQL
choose different queryplan?
--------------050907090606080002060504--