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
- planner-support-for-starts-with-1.patch (text/x-diff) patch
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
-
Add a planner support function for starts_with().
- a148f8bc04b9 15.0 landed