On Thu, Aug 13, 2026 at 10:24 AM Pierre Forstmann <[email protected]> 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
