From: John Scalia <jayknowsunix@gmail.com>
To: Holger Jakobs <holger@jakobs.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Index not always being used
Date: Thu, 29 Aug 2019 10:29:06 -0400
Message-ID: <8D7F747E-132E-4543-9933-583DE249850F@gmail.com> (raw)
In-Reply-To: <43ccec39-3be0-a47c-cd27-fff73d948f20@jakobs.com>
References: <180F96D3-690E-466E-B73E-F6B34C6409C7@gmail.com>
<43ccec39-3be0-a47c-cd27-fff73d948f20@jakobs.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: jayknowsunix@gmail.com, holger@jakobs.com, pgsql-admin@lists.postgresql.org
Subject: Re: Index not always being used
In-Reply-To: <8D7F747E-132E-4543-9933-583DE249850F@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox