Btw: if you still run out of cache later with more regexes may be it makes
sense to do prefiltering first my making a single gigantic regexp as
string_agg(‘(‘||name_matches||’)’,’|’) and then only filter ones that match
later. If postgresql provides capturing groups you may even be able to
explode
The optimizer was a bit too clever. It used the same plan for the LEFT
JOIN. But that put me on the right track. I tried a LATERAL join. But the
optimizer saw through that too and used the same plan. So I tried a
materialized CTE and that finally forced it to use a different plan. That
made it run
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:
>
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.
Table "public.items"
Column │ Type │ Collation │ Nullable │ Default
┼─┼───┼
