Re: Question: consolidating strpos searches?

On Sat, 4 Jan 2025 at 19:04, Greg Sabino Mullane <htamfids@gmail.com> wrote:
>
> On Sat, Jan 4, 2025 at 12:16 PM James Addison <jay@jp-hosting.net> wrote:
>>
>> In the context of considering writing a patch: would the complexity of implementing such a feature for PostgreSQL be
worththe potential 
>> performance benefits?
>
> Probably not. As Tom said, this sounds like it should be tried as an extension.

Will do; thanks, both of you.

>> And either way, is there more I should learn about and consider?  How would I provide convincing supporting
>> evidence if I do write a patch?
>
> As this is the performance mailing list, it might help to describe the real-world problem being encountered here.
Thereare other ways to solve this particular issue. Among them would be using OR not AND in your contrived example,
usingpartial indexes, using pg_trgm,  using regular expressions ( i.e. WHERE value ~ '(known|suffix)' ), redesigning
yourtable and/or queries, and outsourcing the searching of large strings to a system more suitable for it. 

The example is indeed contrived, and the idea doesn't resolve a
problem I've encountered -- in fact, my interest stems from an open
TODO item to implement Boyer-Moore string search.  I began considering
how to implement multiple string pattern search in that context -- but
LIKE/ILIKE introduce a few non-trivial considerations -- notably
wildcard patterns -- compared to strpos.  Whether to require strict
ordering of search results can also be relevant, depending on the
pattern match approach (and boolean operators, as noted) involved.