Re: starts_with, ^@ and index usage

I wrote:
> Shay Rojansky <roji(at)roji(dot)org> writes:
>> First, starts_with doesn't seem to use SP-GIST indexes, contrary to
>> the patch description (and also doesn't trigger a parallel seq scan) -
>> is this intentional?

> It seems like it didn't occur to anybody to tie starts_with() into
> the machinery for derived index operators. That wouldn't be hard,
> but it wasn't done.

I've started another thread with a patch for that [1].

>> Note that ^@ isn't documented on the string functions and operators,

> That's another oversight.

Well, "oversight" might be too strong a word. AFAICS from a quick look
in pg_operator, most operators on type text are comparisons, pattern
match, or text search, none of which do I want to fold into section 9.4.
The only exceptions are ||, which we do document there under SQL
operators, and ^(at)(dot) Commit 710d90da1 apparently decided to treat ^@ as a
pattern match operator, which I guess it could be if you hold your head
at the right angle, but I doubt most people would think to look for it
in section 9.7. I guess the most practical answer is to rename table
9.10 from "Other String Functions" to "Other String Functions and
Operators", which is more parallel to table 9.9 anyway. Just as in 9.9,
it would look weird to have a one-entry table of operators. (Maybe
someday in the far future it'd make sense to split 9.10 into two
tables.)

regards, tom lane

[1] https://www.postgresql.org/message-id/232599.1633800229%40sss.pgh.pa.us