Horton

Thread

Commits

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

  1. Add planner support function for starts_with()

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-10-09T17:23:49Z

    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

  2. Re: Add planner support function for starts_with()

    Nathan Bossart <bossartn@amazon.com> — 2021-11-17T19:00:13Z

    On 10/9/21, 10:24 AM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
> 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:

The patch looks reasonable to me.

Nathan

  3. Re: Add planner support function for starts_with()

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-11-17T21:54:43Z

    "Bossart, Nathan" <bossartn@amazon.com> writes:
> On 10/9/21, 10:24 AM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
>> 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:

> The patch looks reasonable to me.

Pushed, thanks for reviewing!

			regards, tom lane