Greetings hackers,
I'm seeing some odd behavior around string prefix searching -
hopefully I've missed something here (thanks to Nino Floris for
originally flagging this).
In PostgreSQL 11, a starts_with function and a ^@ operators were added
for string prefix checking, as an alternative to LIKE 'foo%' [1] [2].
I've ran a few scenarios and have seen the following behavior:
Queries tested:
1. EXPLAIN SELECT * FROM data WHERE name LIKE 'foo10%';
2. EXPLAIN SELECT * FROM data WHERE name ^@ 'foo10';
3. EXPLAIN SELECT * FROM data WHERE starts_with(name, 'foo10');
... running against a table with 500k rows and enable_seqscan turned
off. Results:
Index | Operator class | LIKE 'X%' | ^@ | starts_with
------ | ---------------- | ----------------- | ----------------- | -----------
btree | text_ops | Parallel seq scan | Parallel seq scan | Seq scan
btree | text_pattern_ops | Index scan | Parallel seq scan | Seq scan
spgist | | Index scan | Index Scan | Seq scan
First, starts_with doesn't seem to use SP-GIST indexes, contrary to
the patch description (and also doesn't trigger a parallel seq scan) -
is this intentional? The function is listed front-and-center on the
string functions and operators page[3], and receives mention on the
pattern matching page[4], without any mention of it being so
problematic.
Note that ^@ isn't documented on the string functions and operators,
so it's not very discoverable; if added to the docs, I'd recommend
adding a note on SP-GIST being required, since uninformed new users
would probably expect a default btree index to work as well.
Shay
[1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=710d90da1fd8c1d028215ecaf7402062079e99e9
[2]
https://www.postgresql.org/message-id/flat/03300255-cff2-b508-50f4-f00cca0a57a1%40sigaev.ru#38d2020edf92f96d204cd2679d362c38
[3] https://www.postgresql.org/docs/current/functions-string.html
[4] https://www.postgresql.org/docs/current/functions-matching.html