Add a planner support function for starts_with().
Tom Lane <tgl@sss.pgh.pa.us>
Add a planner support function for starts_with(). 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
Files
| Path | Change | +/− |
|---|---|---|
| src/backend/utils/adt/like_support.c | modified | +46 −19 |
| src/include/catalog/catversion.h | modified | +1 −1 |
| src/include/catalog/pg_operator.dat | modified | +1 −1 |
| src/include/catalog/pg_proc.dat | modified | +6 −2 |
| src/test/regress/expected/create_index_spgist.out | modified | +33 −0 |
| src/test/regress/sql/create_index_spgist.sql | modified | +8 −0 |
Discussion
- Add planner support function for starts_with() 3 messages · 2021-10-09 → 2021-11-17