Re: Weird CASE WHEN behaviour causing query to be suddenly very slow

We have to confirm the theory first: a 'perf top' sampling during two
runs shall give enough information.

Regards,
Qingqing

On Tue, Mar 31, 2015 at 8:58 AM, Kevin Viraud
<kevin(dot)viraud(at)rocket-internet(dot)de> 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 ?
>
> Basically, I have several tables containing millions of rows and let say 5
> columns. Those five columns, depending of their combination give me a 6th
> value.
> We have complex patterns to match and using simple LIKE / EQUAL and so on
> wouldn't be enough. This can be applied to N number of table so we
> refactored this process into a function that we can use in the SELECT
> statement, by giving only the 5 values each time.
>
> I wouldn't mind using a table and mapping it through a join if it were for
> my own use.
> But the final query has to be readable and usable for almost-non-initiated
> SQL user... So using a function with encapsulated case when seemed to be a
> good idea and so far worked nicely.
>
> But we might consider changing it if we have no other choice...
>
> Regards,
>
> Kevin
>
> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Dienstag, 31. März 2015 15:59
> To: Kevin Viraud
> Cc: pgsql-performance(at)postgresql(dot)org
> Subject: Re: [PERFORM] Weird CASE WHEN behaviour causing query to be
> suddenly very slow
>
> "Kevin Viraud" <kevin(dot)viraud(at)rocket-internet(dot)de> writes:
>> I have an issue with a rather large CASE WHEN and I cannot figure out
>> why it is so slow...
>
> Do all the arms of the CASE usually fail, leaving you at the ELSE?
>
> I suspect what's happening is that you're running into the MAX_CACHED_RES
> limit in src/backend/utils/adt/regexp.c, so that instead of just compiling
> each regexp once and then re-using 'em, the regexps are constantly falling
> out of cache and then having to be recompiled. They'd have to be used in a
> nearly perfect round robin in order for the behavior to have such a big
> cliff as you describe, though. In this CASE structure, that suggests that
> you're nearly always testing every regexp because they're all failing.
>
> I have to think there's probably a better way to do whatever you're trying
> to do, but there's not enough info here about your underlying goal to
> suggest a better approach. At the very least, if you need a many-armed
> CASE, it behooves you to make sure the common cases appear early.
>
> regards, tom lane
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance