Date: Wed, 25 Aug 2021 16:05:00 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Jack Christensen <jack@jncsoftware.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Using regexp from table has unpredictable poor performance
Message-ID: <20210825210500.GF10479@telsasoft.com>
References: 
 <CAMovtNqWPT38rLihCo5JdgFasH0hVOmOBr4=FGo2ZiTMQYrbgA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAMovtNqWPT38rLihCo5JdgFasH0hVOmOBr4=FGo2ZiTMQYrbgA@mail.gmail.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20210825210500.GF10479%40telsasoft.com>
Precedence: bulk

On Wed, Aug 25, 2021 at 11:47:43AM -0500, Jack Christensen wrote:
> I have items that need to be categorized by user defined matching rules.
> Trusted users can create rules that include regular expressions. I've
> reduced the problem to this example.

> I use the following query to find matches:
> 
> select r.id, i.id
> from items i
>   join matching_rules r on i.name ~ r.name_matches;
> 
> When there are few rules the query runs quickly. But as the number of rules
> increases the runtime often increases at a greater than linear rate.

Maybe it's because the REs are cached by RE_compile_and_cache(), but if you
loop over the REs in the inner loop, then the caching is ineffecive.

Maybe you can force it to join with REs on the outer loop by writing it as:
| rules LEFT JOIN items WHERE rules.id IS NOT NULL,
..to improve performance, or at least test that theory.

-- 
Justin