Re: starts_with, ^@ and index usage

Shay Rojansky <roji(at)roji(dot)org> writes:
> In PostgreSQL 11, a starts_with function and a ^@ operators were added
> for string prefix checking, as an alternative to LIKE 'foo%' [1] [2].

> 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? The function is listed front-and-center on the
> string functions and operators page[3], and receives mention on the
> pattern matching page[4], without any mention of it being so
> problematic.

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.

Before (I think) v12, function invocations never could be converted
to indexquals anyway, so it's not surprising that a v11-era patch
wouldn't have thought it needed to address that point.

I do see that starts_with() is marked parallel safe, so it's not clear
why it wouldn't be amenable to a parallel seqscan. The function (as
opposed to the operator) isn't tied into selectivity estimation either,
so maybe that has something to do with using a default selectivity
estimate for it? But said estimate would almost always be too high,
which doesn't seem like the direction that would discourage parallelism.

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

That's another oversight.

It seems clear that the original patch author was pretty narrowly focused
on use of the operator with SP-GIST, and didn't think about how it should
fit into the larger ecosystem.

regards, tom lane