MIME-Version: 1.0
References: 
 <CALDQ5Ny-+ZxLuJiqC11EafO11F-KVSBgWnrWqWpRt4bw1L+hhw@mail.gmail.com>
 <CAKAnmm+YvxoQgFrQNTUm1gNh3NSHqbwie4fx--dLp7xXjdj=9Q@mail.gmail.com>
In-Reply-To: 
 <CAKAnmm+YvxoQgFrQNTUm1gNh3NSHqbwie4fx--dLp7xXjdj=9Q@mail.gmail.com>
From: James Addison <jay@jp-hosting.net>
Date: Wed, 15 Jan 2025 23:58:59 +0000
Message-ID: 
 <CALDQ5NzmGnrnCWFSu=w-myYbfRH0K-3JrY0PunMSm3+16-JJ8w@mail.gmail.com>
Subject: Re: Question: consolidating strpos searches?
To: Greg Sabino Mullane <htamfids@gmail.com>
Cc: pgsql-performance@lists.postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CALDQ5NzmGnrnCWFSu%3Dw-myYbfRH0K-3JrY0PunMSm3%2B16-JJ8w%40mail.gmail.com>
Precedence: bulk

On Sat, 4 Jan 2025 at 19:04, Greg Sabino Mullane <htamfids@gmail.com> wrote=
:
>
> On Sat, Jan 4, 2025 at 12:16=E2=80=AFPM James Addison <jay@jp-hosting.net=
> wrote:
>>
>> In the context of considering writing a patch: would the complexity of i=
mplementing 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 exte=
nsion.

Will do; thanks, both of you.

>> And either way, is there more I should learn about and consider?  How wo=
uld I provide convincing supporting
>> evidence if I do write a patch?
>
> As this is the performance mailing list, it might help to describe the re=
al-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 e=
xample, using partial indexes, using pg_trgm,  using regular expressions ( =
i.e. WHERE value ~ '(known|suffix)' ), redesigning your table and/or querie=
s, 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.