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
