Re: [PATCH] Add planner support function to two-argument regexp_like()

On Thu, Aug 13, 2026 at 10:24 AM Pierre Forstmann
<pierre(dot)forstmann(at)gmail(dot)com> wrote:
>
> The following review has been posted through the commitfest application:
> make installcheck-world: tested, passed
> Implements feature: tested, passed
> Spec compliant: not tested
> Documentation: not tested
>
> I have checked that make installcheck-world does not report any issue: the new test case works as expected.
>
> There is no change in SQL so I did not test SQL compliance. I don't think that documentation should be changed as this is an optimization change only.
>
> Average execution time of make installcheck-world with patch is 748s.
>
> Average execution time of make installcheck-world without patch is 749,6 s.
>
> This patch review looks easy because there is little code change . However it's for more difficult to understand the context if you don't know what a planned support function is (that was my case).
>
> I think this patch should be reviewed by a more experienced contributor with query planner skills.

Hi Pierre,

Thanks for reviewing my patch! Let me briefly explain how this works.

Postgres uses "planner support functions" to help the database
optimize queries. The ~ operator already has one, but the two-argument
regexp_like() function did not, even though they do the exact same
thing.

Here is why this support function allows for index scans: Standard
B-tree indexes cannot search regular expressions directly. However, if
a regex starts with a fixed prefix (like ^item999), the support
function extracts that prefix and translates it into a simple range
condition (like >= 'item999' and < 'item99:'). This allows Postgres to
quickly search the index instead of scanning the entire table.

My patch simply links regexp_like() to this existing support function.
Now, regexp_like() can also extract these fixed prefixes to trigger
fast index scans. The test cases I added just prove that the index is
correctly being used when we do this.

I hope this makes the context clearer!

Thanks,
Shihao