Re: BUG #16241: Degraded hash join performance

I've opened an issue: https://github.com/giggls/mapnik-german-l10n/issues/40

I suspect that the number of executed regexp_replace calls is the culprit here.
The cache of regexp.c seems to be limited to 32 entries which might be to low to keep all involved regexes cached.

> Hi,
>
> On 2020-02-04 11:00:29 -0500, Tom Lane wrote:
>> Andres Freund <andres(at)anarazel(dot)de> writes:
>> > Interesting! The no-children one clearly shows that a lot of the the
>> > time is spent evaluating regular expressions (there's other regex
>> > functions in the profile too):
>> > 23.36% postgres postgres [.] subcolor
>>
>> Huh ...
>>
>> > I'm not aware of any relevant regular expression evaluation changes
>> > between 11 and 12. Tom, does this trigger anything?
>>
>> (1) Nope, I'm not either; the last non-back-patched change in that
>> code was c54159d44 in v10.
>>
>> (2) subcolor() is part of regex compilation, not execution, which makes
>> one wonder why it's showing up at all. Maybe the regex cache in
>> adt/regexp.c is overflowing and preventing useful caching? But
>> that didn't change in v12 either. Are these test cases really
>> 100% equivalent? I'm wondering if there are a few more "hot"
>> regex patterns in the v12 data ...
>
> They are not 100% equivalent, but the part of the plan we see is very
> similar rowcount wise. It's possible that the functions differ more
> however, there are different postgis versions involved, and apparently
> also an "osml10n" extension.
>
>
>> (3) Where the heck is the regex use coming from at all? I don't
>> see any regex operators in the plan. Maybe it's inside the
>> plpgsql function?
>
> It definitely is. The stack shows at least two levels of plpgsql
> functions. And Thomas has since confirmed that removing the functioncall
> fixes the issue.
>
> Based on the name I think this is somewhere around this:
> https://github.com/giggls/mapnik-german-l10n/blob/master/plpgsql/get_localized_name_from_tags.sql#L120
> The callgraph indicates that most of the cost comes from within
> textregexreplace_noopt.
>
> Not clear why the cache isn't fixing this - there are no variables in
> the regexp_replace calls as far as I can see.
>
> Greetings,
>
> Andres Freund

--

Thomas Butz