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 1iz0d0-00078C-04 for pgsql-bugs@arkaria.postgresql.org; Tue, 04 Feb 2020 16:00:42 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iz0cy-0001jQ-Hu for pgsql-bugs@arkaria.postgresql.org; Tue, 04 Feb 2020 16:00:40 +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 1iz0cy-0001jD-9d for pgsql-bugs@lists.postgresql.org; Tue, 04 Feb 2020 16:00:40 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1iz0cu-0005Ou-W3 for pgsql-bugs@lists.postgresql.org; Tue, 04 Feb 2020 16:00:39 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id 014G0Tv8006090; Tue, 4 Feb 2020 11:00:29 -0500 From: Tom Lane To: Andres Freund cc: Thomas Butz , pgsql-bugs Subject: Re: BUG #16241: Degraded hash join performance In-reply-to: <20200204134408.oing4ypvshgr5ykx@alap3.anarazel.de> References: <20200204134408.oing4ypvshgr5ykx@alap3.anarazel.de> Comments: In-reply-to Andres Freund message dated "Tue, 04 Feb 2020 05:44:08 -0800" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <6088.1580832029.1@sss.pgh.pa.us> Date: Tue, 04 Feb 2020 11:00:29 -0500 Message-ID: <6089.1580832029@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk 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 ... (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? regards, tom lane