Subject: Re: Index not always being used
To: pgsql-admin@lists.postgresql.org
References: <180F96D3-690E-466E-B73E-F6B34C6409C7@gmail.com>
From: Holger Jakobs <holger@jakobs.com>
Openpgp: preference=signencrypt
Autocrypt: addr=holger@jakobs.com; prefer-encrypt=mutual; keydata=
 mQGiBEG8IA8RBAC6WqUzEPTjest7MvFca5WlI47EgtKzOy1D1X9gSEmPj0sQrilNGEitY/+Q
 kPnCvI3odz1XXf/MZQtXwlxJA4lPmx8/K7MqBj9vh0J5jRznpE7l+SBDmmDobIqBAgl6BZvH
 1C7e2y72h5T1/plrXonLUGzthqBtsvWl0ogoyTJqiwCg6AOhYX+Xbcl8/AXy4F4qbUq4LXsD
 /j6yCKRkyleN688y9YpuS5J6/ZZ/OBCM74pa2iNge3GmXn1JeUowF693QFCGHkpswmDIoGhL
 5iM6GHj9HFB6rTXW5H3sR1y6ta7vXwoNBGkgiM89B1a/KsLT5L6MZgZ5AV0tj6vxwAsuLixS
 SDiNVNAd8vp2FfqdLlOgThsgVIwKBACsCzZa1VxpdoNSYnkTOSZhEjZkbWApdneQ2bTxXJgW
 +fWgVeLiojCp6I1AmQqzwHz8UN1nrsQjH8Rnxt2J/C5H/Ek9jlOFakFQXlPMZw3tNeTA1P3B
 3zPyq6hKFPZHFklUquE3gHVjnX9qxSW0xvFirf6xAMCMYWRnCQHRgiXoAbQhSG9sZ2VyIEph
 a29icyA8aG9sZ2VyQGpha29icy5jb20+iGQEExECACQCGwMGCwkIBwMCAxUCAwMWAgECHgEC
 F4AFAlZ7KnEFCSeLDWIACgkQileck7MZYbpbxgCgxM54+4qx/sfGwq01x93pWr2QghwAoIfD
 Of6Uoc43y98+xR5/Iu9Z1RH8uQENBEG8IBAQBACiGkoX8HKCocdCwRdMzc9siDs1w0YNayhT
 PnK9Ii3wzzS2R9NSyWsPQVyx6hkVfSmK6CE5RzfuDUzrYEgyaV/2vT3JD9Gko+RuFnjeQvVo
 8hc19bmBSn95ATki+hFy/68MqpMlsG7YyCluRSpXh2qxlhbh2FzTIQmiEQAR4kFCEwADBQP/
 Z1Y6nwoXw/5JmLJLFcvZZlFfn4g9TjyMSwH50/AXqOApccy9JSzoTFY87ItmGkhD0qj18PJB
 St2uqASGrk2/55XK596RWnoeyL9mzj5r6woDqPNRXIbK85foGVIMRrb/EufgSOP5H6VHybvB
 PD4CppEw4Q0fRsMCaOcvibkjHoqITwQYEQIADwIbDAUCVnsqcQUJJ4sNYQAKCRCKV5yTsxlh
 urunAJ9avyijdRls7l6XPc2cBMys3xu/sgCeOVJE6DzwBBRUd6S6mtf2eG4FPXg=
Message-ID: <43ccec39-3be0-a47c-cd27-fff73d948f20@jakobs.com>
Date: Thu, 29 Aug 2019 16:22:36 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.8.0
MIME-Version: 1.0
In-Reply-To: <180F96D3-690E-466E-B73E-F6B34C6409C7@gmail.com>
Content-Type: multipart/alternative;
 boundary="------------A2F08EEC663AD8263A7CC1B2"
Content-Language: de-DE
Precedence: bulk

This is a multi-part message in MIME format.
--------------A2F08EEC663AD8263A7CC1B2
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 8bit

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 <tel:+491789759012> oder +49 2202 817157
<tel:+492202817157>


--------------A2F08EEC663AD8263A7CC1B2
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    <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 "=" and the
      trigram index when using "like", "similar to" or "~" (regexp).</p>
    <p>A trigram index is not suitable for a "=" comparison.<br>
    </p>
    <p>Regards,</p>
    <p>Holger<br>
    </p>
    <div class="moz-cite-prefix">Am 29.08.19 um 16:15 schrieb John
      Scalia:<br>
    </div>
    <blockquote type="cite"
      cite="mid:180F96D3-690E-466E-B73E-F6B34C6409C7@gmail.com">
      <pre class="moz-quote-pre" wrap="">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

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

--------------A2F08EEC663AD8263A7CC1B2--