Re: Use a hash table for the regexp cache

On Sun, Nov 1, 2026 at 9:56 PM jian he <jian(dot)universality(at)gmail(dot)com> wrote:
> Currently, compiled regular expressions are cached in
> RegexpCacheMemoryContext, which is session-level. Another possible
> direction is to cache them in query-level instead.
>
> Nearly all callers of RE_compile_and_cache() can be reached from a
> function that has an fcinfo: the regex operators, the regexp_*
> functions, textregexsubstr() and textregexreplace(). We can cache the
> compiled regex in fcinfo->flinfo->fn_extra, the same way
> array_sort_internal() caches its ArraySortCachedInfo. The compiled
> regex would then be freed automatically when the query ends.

Thanks for looking!

I think a query-lifetime cache would make a common case slower, and it
can't cover all the callers.

The session cache lets a short query reuse a regexp compiled by an
earlier query. With a cache in fn_extra, every execution compiles it
again, including executions of a prepared statement, since the FmgrInfo
is rebuilt for each execution. To measure the cost of one pg_regcomp()
per query on a one-row table, I ran
"SELECT count(*) FROM t WHERE c ~ ('^(item|thing)[0-9]*' || :n || '$')"
with pgbench and one client: 20 us per query when :n is fixed (session
cache hit) vs. 26 us when :n is random (compiled once per query), or
about 30% slower.

Also, not all callers have a usable fn_extra:

- regexp_matches() and regexp_split_to_table() are set-returning
functions, and fn_extra already holds their FuncCallContext. That
state only lasts for one call, so in something like
"FROM t, regexp_matches(t.c, 'pat')" the regexp would be compiled
again for every row of t.

- jsonpath's like_regex is reached through the jsonb_path_*()
functions (some of which are SRFs too) and through JSON_EXISTS() /
JSON_VALUE() in execExprInterp.c, which don't go through an fcinfo at
all. One jsonpath can also contain several like_regex items, so
there is no single slot per call site.

- regexp_fixed_prefix() is called from the planner (like_support.c),
where there is no fcinfo. In fact, for "WHERE c ~ 'pat'", the
planner compiles 'pat' in regexp_fixed_prefix() and the executor
then reuses that entry from the session cache in textregexeq(); with
fn_extra, even a single query would compile it twice.

- DirectFunctionCall4Coll(textregexreplace, ...) in extension.c passes
a NULL flinfo, and RE_compile_and_cache() is exported in
regex/regex.h, so extensions may be calling it too.

And when the pattern comes from a column, one call site sees many
patterns, so each call site would still need a small cache of its own.

The case where fn_extra would help most is a constant pattern, and 0001
already handles that by checking the most recently used entry before
computing a hash, so a hit costs a single memcmp().

> In https://wiki.postgresql.org/wiki/Todo we also have an entry
> {Consider a special data type for regular expressions}

A regexp data type would avoid the cache for patterns stored as that
type, but it's a much bigger project with compatibility questions of
its own, and 0001 doesn't get in its way.

So I'd like to keep the session-level cache and go on with the sizing
question from my first mail. If others would rather explore the
query-level approach, I'm happy to prototype it so we can compare.

--
Tianyi Gao