From: Holger Jakobs <holger@jakobs.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Index not always being used
Date: Thu, 29 Aug 2019 16:22:36 +0200
Message-ID: <43ccec39-3be0-a47c-cd27-fff73d948f20@jakobs.com> (raw)
In-Reply-To: <180F96D3-690E-466E-B73E-F6B34C6409C7@gmail.com>
References: <180F96D3-690E-466E-B73E-F6B34C6409C7@gmail.com>
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>
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: holger@jakobs.com, pgsql-admin@lists.postgresql.org
Subject: Re: Index not always being used
In-Reply-To: <43ccec39-3be0-a47c-cd27-fff73d948f20@jakobs.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