pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Thomas Butz <tbutz@optitool.de>
To: Andres Freund <andres@anarazel.de>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #16241: Degraded hash join performance
Date: Wed, 5 Feb 2020 12:06:41 +0100 (CET)
Message-ID: <1965637501.286993.1580900801394.JavaMail.zimbra@optitool.de> (raw)
In-Reply-To: <20200204162901.s5hbfrl2ylb3jjsq@alap3.anarazel.de>
References: <20200204134408.oing4ypvshgr5ykx@alap3.anarazel.de>
	<6089.1580832029@sss.pgh.pa.us>
	<20200204162901.s5hbfrl2ylb3jjsq@alap3.anarazel.de>

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@anarazel.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#L1...
> 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





view thread (9+ messages)

Message-ID: <1965637501.286993.1580900801394.JavaMail.zimbra@optitool.de>
Permalink:  ../1965637501.286993.1580900801394.JavaMail.zimbra@optitool.de/
Also on:    postgresql.org/message-id/1965637501.286993.1580900801394.JavaMail.zimbra@optitool.de

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: tbutz@optitool.de, andres@anarazel.de, tgl@sss.pgh.pa.us
  Subject: Re: BUG #16241: Degraded hash join performance
  In-Reply-To: <1965637501.286993.1580900801394.JavaMail.zimbra@optitool.de>
  References: <20200204134408.oing4ypvshgr5ykx@alap3.anarazel.de> <6089.1580832029@sss.pgh.pa.us> <20200204162901.s5hbfrl2ylb3jjsq@alap3.anarazel.de> <1965637501.286993.1580900801394.JavaMail.zimbra@optitool.de>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox