Thread: Index not always being used
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 tablescan is going to be used, but if I change the query to use “where bold_city like ‘basking%’”, then explain correctlyshows that a bitmap index scan will be performed. Why would these two forms use different approaches? I’m a bitconfused. — Jay Sent from my iPad
Hi,
A good solution would be to add another B-Tree index. The planner would choose this one in case of a search using "=" and the trigram index when using "like", "similar to" or "~" (regexp).
A trigram index is not suitable for a "=" comparison.
Regards,
Holger
Am 29.08.19 um 16:15 schrieb John Scalia:
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. — Jay Sent from my iPad
--
Holger Jakobs, Bergisch Gladbach
instant messaging: xmpp:holger@jakobs.com
+49 178 9759012 oder +49 2202 817157
Good to know, thanks
Sent from my iPad
Hi,
A good solution would be to add another B-Tree index. The planner would choose this one in case of a search using "=" and the trigram index when using "like", "similar to" or "~" (regexp).
A trigram index is not suitable for a "=" comparison.
Regards,
Holger
Am 29.08.19 um 16:15 schrieb John Scalia: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. — Jay Sent from my iPad--Holger Jakobs, Bergisch Gladbach
instant messaging: xmpp:holger@jakobs.com
+49 178 9759012 oder +49 2202 817157
OrOn Thu, Aug 29, 2019 at 10:15 AM John Scalia <jayknowsunix@gmail.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