OrOn Thu, Aug 29, 2019 at 10:15 AM John Scalia <jayknowsunix@xxxxxxxxx> wrote:
I’ve got a table with approximately 5.5 million rows, and one column that is frequently searched is of type varchar(50) and we just put a trigram index on it. Now, if we search “where bld_city = ‘baskingridge’”, explain says a sequential table scan is going to be used, but if I change the query to use “where bold_city like ‘basking%’”, then explain correctly shows that a bitmap index scan will be performed. Why would these two forms use different approaches? I’m a bit confused.
pg_trgm code is not written to support equality. It would be almost trivial to change it to do so (see my patch in https://www.postgresql.org/message-id/flat/20160318100427.2903.57536%40wrigleys.postgresql.org, which is now out of date), but doing so would have dubious merit when the default index type (btree) already supports equality so very well.
If you really don't want to build an extra btree index, you could just write the query using LIKE with no wildcards: "where bld_city LIKE ‘baskingridge’".
Also, if all your queries will be front-anchored (wildcards only at the end, like in your example) then pg_trgm is overkill in the first place. You can just use text_pattern_ops with the default btree index instead. It will support both equality and prefix matching.
Cheers,
Jeff
