pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedIndex not always being used
4+ messages / 3 participants
[nested] [flat]
* Index not always being used
@ 2019-08-29 14:15 John Scalia <jayknowsunix@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: John Scalia @ 2019-08-29 14:15 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Index not always being used
@ 2019-08-29 14:22 Holger Jakobs <holger@jakobs.com>
parent: John Scalia <jayknowsunix@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Holger Jakobs @ 2019-08-29 14:22 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Index not always being used
@ 2019-08-29 14:29 John Scalia <jayknowsunix@gmail.com>
parent: Holger Jakobs <holger@jakobs.com>
0 siblings, 0 replies; 4+ messages in thread
From: John Scalia @ 2019-08-29 14:29 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; +Cc: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Index not always being used
@ 2019-08-30 01:43 Jeff Janes <jeff.janes@gmail.com>
parent: John Scalia <jayknowsunix@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Jeff Janes @ 2019-08-30 01:43 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2019-08-30 01:43 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-08-29 14:15 Index not always being used John Scalia <jayknowsunix@gmail.com>
2019-08-29 14:22 ` Holger Jakobs <holger@jakobs.com>
2019-08-29 14:29 ` John Scalia <jayknowsunix@gmail.com>
2019-08-30 01:43 ` Jeff Janes <jeff.janes@gmail.com>
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