[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-admin
Subject:    Re: Index not always being used
From:       Jeff Janes <jeff.janes () gmail ! com>
Date:       2019-08-30 1:43:45
Message-ID: CAMkU=1wY2JzTJCxvYEzujKMQCB9-PGEZwjZbo7X6EsvnyPh5ZA () mail ! gmail ! com
[Download RAW message or body]

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

[Attachment #3 (text/html)]

<div dir="auto"><div dir="ltr"><div dir="ltr">OrOn Thu, Aug 29, 2019 at 10:15 AM John \
Scalia &lt;<a href="mailto:jayknowsunix@gmail.com" target="_blank" \
rel="noreferrer">jayknowsunix@gmail.com</a>&gt; wrote:<br></div><div \
class="gmail_quote"><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">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.<br></blockquote><div><br></div><div>pg_trgm code is not written to support \
equality.   It would be almost trivial to change it to do so (see my patch in  <a \
href="https://www.postgresql.org/message-id/flat/20160318100427.2903.57536%40wrigleys.postgresql.org" \
target="_blank" rel="noreferrer">https://www.postgresql.org/message-id/flat/20160318100427.2903.57536%40wrigleys.postgresql.org</a>, \
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.</div><div><br></div><div>If you really don&#39;t want to build an extra btree \
index, you could just write the query using LIKE with no wildcards:     &quot;where \
bld_city LIKE ‘baskingridge'&quot;.</div><div><br></div><div>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.</div><div dir="auto"><br></div><div dir="auto">Cheers,</div><div \
dir="auto"><br></div><div dir="auto">Jeff</div></div></div></div>



[prev in list] [next in list] [prev in thread] [next in thread]