From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | pgsql-committers(at)lists(dot)postgresql(dot)org |
Subject: | pgsql: Add a planner support function for starts_with(). |
Date: | 2021-11-17 21:54:22 |
Message-ID: | E1mnSso-00014n-V3@gemulon.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-committers |
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
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/a148f8bc04b9980f019ea0d4b89311cf0bdc22b7
Modified Files
--------------
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(-)
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2021-11-18 00:10:04 | pgsql: Provide a variant of simple_prompt() that can be interrupted by |
Previous Message | Tom Lane | 2021-11-17 19:16:59 | pgsql: Clean up error handling in pg_basebackup's walmethods.c. |