Content-Type: multipart/alternative;
	boundary=Apple-Mail-7DD3DDA1-2817-44ED-A14A-33675296388E
Mime-Version: 1.0 (1.0)
Subject: Re: Index not always being used
From: John Scalia <jayknowsunix@gmail.com>
In-Reply-To: <43ccec39-3be0-a47c-cd27-fff73d948f20@jakobs.com>
Date: Thu, 29 Aug 2019 10:29:06 -0400
Cc: pgsql-admin@lists.postgresql.org
Content-Transfer-Encoding: 7bit
Message-Id: <8D7F747E-132E-4543-9933-583DE249850F@gmail.com>
References: <180F96D3-690E-466E-B73E-F6B34C6409C7@gmail.com>
 <43ccec39-3be0-a47c-cd27-fff73d948f20@jakobs.com>
To: Holger Jakobs <holger@jakobs.com>
Precedence: bulk


--Apple-Mail-7DD3DDA1-2817-44ED-A14A-33675296388E
Content-Type: text/plain;
	charset=utf-8
Content-Transfer-Encoding: quoted-printable

Good to know, thanks

Sent from my iPad

> On Aug 29, 2019, at 10:22 AM, Holger Jakobs <holger@jakobs.com> wrote:
>=20
> Hi,
>=20
> A good solution would be to add another B-Tree index. The planner       wo=
uld choose this one in case of a search using "=3D" and the trigram index wh=
en using "like", "similar to" or "~" (regexp).
>=20
> A trigram index is not suitable for a "=3D" comparison.
>=20
> Regards,
>=20
> Holger
>=20
>> Am 29.08.19 um 16:15 schrieb John Scalia:
>> I=E2=80=99ve got a table with approximately 5.5 million rows, and one col=
umn that is frequently searched is of type varchar(50)  and we just put a tr=
igram index on it. Now, if we search =E2=80=9Cwhere bld_city =3D =E2=80=98ba=
skingridge=E2=80=99=E2=80=9D, explain says a sequential table scan is going t=
o be used, but if I change the query to use =E2=80=9Cwhere bold_city like =E2=
=80=98basking%=E2=80=99=E2=80=9D, then explain correctly shows that a bitmap=
 index scan will be performed. Why would these two forms use different appro=
aches? I=E2=80=99m a bit confused.
>> =E2=80=94
>> Jay
>>=20
>> Sent from my iPad
>>=20
> --=20
> Holger Jakobs, Bergisch Gladbach
> instant messaging: xmpp:holger@jakobs.com
> +49 178 9759012 oder +49 2202 817157

--Apple-Mail-7DD3DDA1-2817-44ED-A14A-33675296388E
Content-Type: text/html;
	charset=utf-8
Content-Transfer-Encoding: quoted-printable

<html><head><meta http-equiv=3D"content-type" content=3D"text/html; charset=3D=
utf-8"></head><body dir=3D"auto">Good to know, thanks<br><br><div id=3D"Appl=
eMailSignature" dir=3D"ltr">Sent from my iPad</div><div dir=3D"ltr"><br>On A=
ug 29, 2019, at 10:22 AM, Holger Jakobs &lt;<a href=3D"mailto:holger@jakobs.=
com">holger@jakobs.com</a>&gt; wrote:<br><br></div><blockquote type=3D"cite"=
><div dir=3D"ltr">
 =20
    <meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3DUTF-8"=
>
 =20
 =20
    <p>Hi,</p>
    <p>A good solution would be to add another B-Tree index. The planner
      would choose this one in case of a search using "=3D" and the
      trigram index when using "like", "similar to" or "~" (regexp).</p>
    <p>A trigram index is not suitable for a "=3D" comparison.<br>
    </p>
    <p>Regards,</p>
    <p>Holger<br>
    </p>
    <div class=3D"moz-cite-prefix">Am 29.08.19 um 16:15 schrieb John
      Scalia:<br>
    </div>
    <blockquote type=3D"cite" cite=3D"mid:180F96D3-690E-466E-B73E-F6B34C6409=
C7@gmail.com">
      <pre class=3D"moz-quote-pre" wrap=3D"">I=E2=80=99ve got a table with a=
pproximately 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 sear=
ch =E2=80=9Cwhere bld_city =3D =E2=80=98baskingridge=E2=80=99=E2=80=9D, expl=
ain says a sequential table scan is going to be used, but if I change the qu=
ery to use =E2=80=9Cwhere bold_city like =E2=80=98basking%=E2=80=99=E2=80=9D=
, then explain correctly shows that a bitmap index scan will be performed. W=
hy would these two forms use different approaches? I=E2=80=99m a bit confuse=
d.
=E2=80=94
Jay

Sent from my iPad

</pre>
    </blockquote>
    <div class=3D"moz-signature">-- <br>
      <p>Holger Jakobs, Bergisch Gladbach<br>
        instant messaging: <a href=3D"xmpp:holger@jakobs.com">xmpp:holger@ja=
kobs.com</a><br>
        <a href=3D"tel:+491789759012">+49 178 9759012</a> oder <a href=3D"te=
l:+492202817157">+49 2202 817157</a>
      </p>
    </div>
 =20

</div></blockquote></body></html>=

--Apple-Mail-7DD3DDA1-2817-44ED-A14A-33675296388E--