Add planner support function for starts_with()

Tom Lane <tgl@sss.pgh.pa.us>

From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-hackers@lists.postgresql.org
Date: 2021-10-09T17:23:49Z
Lists: pgsql-hackers

Attachments

When starts_with() and the equivalent ^@ operator were added, they
were plugged into the planner in only a rather half-baked way.
Selectivity estimation got taught about the operator, but the
other infrastructure associated with LIKE/regex matching wasn't
updated.  This causes these operators to be planned more stupidly
than a functionally-equivalent LIKE/regex pattern [1].

With the (admittedly later) introduction of planner support functions,
it's really quite easy to do better.  The attached patch adds a planner
support function for starts_with(), with these benefits:

* A condition such as "textcol ^@ constant" can now use a regular
btree index, not only an SP-GiST index, so long as the index's
collation is C.  (This works just like "textcol LIKE 'foo%'".)

* "starts_with(textcol, constant)" can be optimized the same as
"textcol ^@ constant".

I also rejiggered match_pattern_prefix() a bit, with the effect
that fixed-prefix LIKE and regex patterns are now more like
starts_with() in another way: if you apply one to an SPGiST-indexed
column, you'll get an index condition using ^@ rather than two
index conditions with >= and <.  That should be more efficient
at runtime, though I didn't try to do any performance testing.

			regards, tom lane

[1] https://www.postgresql.org/message-id/CADT4RqB13KQHOJqqQ%2BWXmYtJrukS2UiFdtfTvT-XA3qYLyB6Cw%40mail.gmail.com

Commits

  1. Add a planner support function for starts_with().