Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Yfd55-00063Q-0o for pgsql-performance@arkaria.postgresql.org; Tue, 07 Apr 2015 23:38:55 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Yfd54-0002l9-G1 for pgsql-performance@arkaria.postgresql.org; Tue, 07 Apr 2015 23:38:54 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1Yfd53-0002l3-Hf for pgsql-performance@postgresql.org; Tue, 07 Apr 2015 23:38:53 +0000 Received: from mail-by2on0653.outbound.protection.outlook.com ([2a01:111:f400:fc0c::653] helo=na01-by2-obe.outbound.protection.outlook.com) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Yfd4z-0007Bz-8R for pgsql-performance@postgresql.org; Tue, 07 Apr 2015 23:38:51 +0000 Received: from decina.local (70.113.16.71) by CY1PR11MB0682.namprd11.prod.outlook.com (25.163.236.20) with Microsoft SMTP Server (TLS) id 15.1.130.23; Tue, 7 Apr 2015 23:38:42 +0000 Message-ID: <55246A7E.9060800@BlueTreble.com> Date: Tue, 7 Apr 2015 18:38:38 -0500 From: Jim Nasby User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9; rv:31.0) Gecko/20100101 Thunderbird/31.4.0 MIME-Version: 1.0 To: Kevin Viraud , 'Tom Lane' CC: Subject: Re: Weird CASE WHEN behaviour causing query to be suddenly very slow References: <00a001d06b90$2d1bbe80$87533b80$@rocket-internet.de> <16234.1427810310@sss.pgh.pa.us> <00d301d06bcb$997cf220$cc76d660$@rocket-internet.de> In-Reply-To: <00d301d06bcb$997cf220$cc76d660$@rocket-internet.de> Content-Type: text/plain; charset="windows-1252"; format=flowed Content-Transfer-Encoding: 7bit X-Originating-IP: [70.113.16.71] X-ClientProxiedBy: BLUPR01CA031.prod.exchangelabs.com (25.160.23.21) To CY1PR11MB0682.namprd11.prod.outlook.com (25.163.236.20) Authentication-Results: postgresql.org; dkim=none (message not signed) header.d=none; X-Microsoft-Antispam: UriScan:;BCL:0;PCL:0;RULEID:;SRVR:CY1PR11MB0682; X-Forefront-Antispam-Report: BMV:1; SFV:NSPM; SFS:(10009020)(6009001)(51704005)(377454003)(479174004)(24454002)(92566002)(50466002)(19580395003)(86362001)(23746002)(66066001)(47776003)(65956001)(36756003)(33656002)(2950100001)(83506001)(40100003)(80316001)(50986999)(76176999)(54356999)(42186005)(77156002)(46102003)(87976001)(64126003)(87266999)(15975445007)(122386002)(62966003)(85282002)(217873001)(18886065003); DIR:OUT; SFP:1101; SCL:1; SRVR:CY1PR11MB0682; H:decina.local; FPR:; SPF:None; MLV:sfv; LANG:en; X-Microsoft-Antispam-PRVS: X-Exchange-Antispam-Report-Test: UriScan:; X-Exchange-Antispam-Report-CFA-Test: BCL:0; PCL:0; RULEID:(601004)(5002010)(5005006); SRVR:CY1PR11MB0682; BCL:0; PCL:0; RULEID:; SRVR:CY1PR11MB0682; X-Forefront-PRVS: 0539EEBD11 X-OriginatorOrg: bluetreble.com X-MS-Exchange-CrossTenant-OriginalArrivalTime: 07 Apr 2015 23:38:42.5482 (UTC) X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted X-MS-Exchange-Transport-CrossTenantHeadersStamped: CY1PR11MB0682 X-Pg-Spam-Score: -1.9 (-) 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 On 3/31/15 10:58 AM, Kevin Viraud wrote: > Touche ! Thanks a lot. > > Looking more at the data yes it goes very often to ELSE Clause. And > therefore reaching the MAX_CACHED_RES. > > In there anyway to increase that value ? Sure, change it and re-compile. But be aware that increasing it will probably increase the cost of some other stuff, so it's a tradeoff. If this is that complex though you very likely would do better in plperl, especially if you could pre-compile the RE's. AFAIK there's no way to do that in Postgres, though it might be interesting to add that ability. -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance