From: Tom Lane <tgl@sss.pgh.pa.us>
To: James Addison <jay@jp-hosting.net>
cc: pgsql-performance@lists.postgresql.org
Subject: Re: Question: consolidating strpos searches?
In-reply-to: 
 <CALDQ5Ny-+ZxLuJiqC11EafO11F-KVSBgWnrWqWpRt4bw1L+hhw@mail.gmail.com>
References: 
 <CALDQ5Ny-+ZxLuJiqC11EafO11F-KVSBgWnrWqWpRt4bw1L+hhw@mail.gmail.com>
Comments: In-reply-to James Addison <jay@jp-hosting.net>
	message dated "Sat, 04 Jan 2025 17:16:06 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <634657.1736012711.1@sss.pgh.pa.us>
Date: Sat, 04 Jan 2025 12:45:11 -0500
Message-ID: <634658.1736012711@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/634658.1736012711%40sss.pgh.pa.us>
Precedence: bulk

James Addison <jay@jp-hosting.net> writes:
> In other words: each additional strpos(value, ...) expression
> increased the evaluation time by a similar, significant duration of
> two seconds.  This seems to confirm the basis that each expression is
> currently evaluated separately, by an independent read from the input
> text.

That's correct.  We have not felt a need to try to identify duplicate
function calls: the cost of looking for them would seldom be repaid,
so that such an optimization would be a net loss for most people.
(In your example, they're not even truly duplicates.)

> I'd like to suggest the introduction of a documented multiple string
> matching algorithm[1], to yield results for each of multiple strpos
> calls while only reading through their common input text at-most once.

This seems like something with a pretty narrow audience.  I'd suggest
building it as an extension rather than trying to persuade people
it belongs in core Postgres.

			regards, tom lane