Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1izIWA-0004KF-7M for pgsql-bugs@arkaria.postgresql.org; Wed, 05 Feb 2020 11:06:51 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1izIW8-0002u5-0A for pgsql-bugs@arkaria.postgresql.org; Wed, 05 Feb 2020 11:06:48 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1izIW7-0002ty-Mj for pgsql-bugs@lists.postgresql.org; Wed, 05 Feb 2020 11:06:47 +0000 Received: from zimbra.optitool.de ([2001:a60:a0c8::3]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1izIW4-0006Kc-S1 for pgsql-bugs@lists.postgresql.org; Wed, 05 Feb 2020 11:06:47 +0000 Received: from zimbra.optitool.de (localhost [IPv6:::1]) by zimbra.optitool.de (Postfix) with ESMTPS id D2049445F25; Wed, 5 Feb 2020 12:06:41 +0100 (CET) Received: from localhost (localhost [IPv6:::1]) by zimbra.optitool.de (Postfix) with ESMTP id B40A7447C25; Wed, 5 Feb 2020 12:06:41 +0100 (CET) DKIM-Filter: OpenDKIM Filter v2.10.3 zimbra.optitool.de B40A7447C25 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=optitool.de; s=E37AA3DA-415C-11E8-B16D-258E1F829521; t=1580900801; bh=88eRNZg+kHdt3lCiKBg399HG/fbgDluPgIM3CQGCvlE=; h=Date:From:To:Message-ID:MIME-Version; b=sOiWJpvCVMLwDbYM8GlNXANqSHzIIYPuJmgNbB0+8ifVPDi0ydp7zJl9dfVmLGF/O OA69tVUSUWATgAjo/XMSWJHzOKvYr37f5t2RlhF6achwjRmI1caQxXKus2n1jPJXCP 9JPA6wGhcd6U58cJW54FkJV2u0hcy/8732yybd+eXFEhnJ3fTuWeFKxvv1jOdY/5To MzcasBgoAvTSS73seh2KVCVlpHM138+wbnbMSru2QXpiq8j9FIBR8wrRjetKrcbIyU vLKYrKLZ+ba6rOGL7I3ZK+FgYAvdzS8Q350m/Yw3LifHNaWgOVQt+lXa9iKghClZGW E9yI+AXSM/guw== Received: from zimbra.optitool.de ([IPv6:::1]) by localhost (zimbra.optitool.de [IPv6:::1]) (amavisd-new, port 10026) with ESMTP id Ok1BPdSkYykX; Wed, 5 Feb 2020 12:06:41 +0100 (CET) Received: from zimbra.optitool.de (localhost [127.0.0.1]) by zimbra.optitool.de (Postfix) with ESMTP id 7C28B445F25; Wed, 5 Feb 2020 12:06:41 +0100 (CET) Date: Wed, 5 Feb 2020 12:06:41 +0100 (CET) From: Thomas Butz To: Andres Freund Cc: Tom Lane , pgsql-bugs Message-ID: <1965637501.286993.1580900801394.JavaMail.zimbra@optitool.de> 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> Subject: Re: BUG #16241: Degraded hash join performance MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-Originating-IP: [2001:a60:a0c8:100:47ef:5fd3:bc93:5baf] X-Mailer: Zimbra 8.8.15_GA_3888 (ZimbraWebClient - FF72 (Linux)/8.8.15_GA_3890) Thread-Topic: BUG #16241: Degraded hash join performance Thread-Index: eoeIh+xNhGq3yNuIp6imXFokcsoy9w== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk 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 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