Re: Index not always being used

OrOn Thu, Aug 29, 2019 at 10:15 AM John Scalia <jayknowsunix(at)gmail(dot)com>
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