On Sat, 4 Jan 2025 at 19:04, Greg Sabino Mullane <htamf...@gmail.com> wrote: > > On Sat, Jan 4, 2025 at 12:16 PM James Addison <j...@jp-hosting.net> wrote: >> >> In the context of considering writing a patch: would the complexity of >> implementing such a feature for PostgreSQL be worth the 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. There are other ways to solve this > particular issue. Among them would be using OR not AND in your contrived > example, using partial indexes, using pg_trgm, using regular expressions ( > i.e. WHERE value ~ '(known|suffix)' ), redesigning your table 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.
