Обсуждение: Index not always being used

Поиск
Список
Период
Сортировка

Index not always being used

От
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
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


Re: Index not always being used

От
Holger Jakobs
Дата:

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

Re: Index not always being used

От
John Scalia
Дата:
Good to know, thanks

Sent from my iPad

On Aug 29, 2019, at 10:22 AM, Holger Jakobs <holger@jakobs.com> wrote:

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

Re: Index not always being used

От
Jeff Janes
Дата:
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