Add a planner support function for starts_with().

Enterprise / PostgreSQL - Tom Lane [sss.pgh.pa.us] - 17 November 2021 21:54 UTC

This fills in some gaps in planner support for starts_with() and the equivalent ^@ operator:

- 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".

- 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 <.

Per a complaint from Shay Rojansky. Patch by me; thanks to Nathan Bossart for review.

Discussion: https://postgr.es/m/232599.1633800229@sss.pgh.pa.us

a148f8bc04 Add a planner support function for starts_with().
src/backend/utils/adt/like_support.c | 65 ++++++++++++++++-------
src/include/catalog/catversion.h | 2 +-
src/include/catalog/pg_operator.dat | 2 +-
src/include/catalog/pg_proc.dat | 8 ++-
src/test/regress/expected/create_index_spgist.out | 33 ++++++++++++
src/test/regress/sql/create_index_spgist.sql | 8 +++
6 files changed, 95 insertions(+), 23 deletions(-)

Upstream: git.postgresql.org


  • Share