Sequential scan for column indexed with varchar_pattern_ops

Question

I have a table Users and it contains location column. I have indexed location column using varchar_pattern_ops. But when I run query planner it tells me it is doing a sequential scan.

EXPLAIN ANALAYZE
SELECT * FROM USERS
WHERE lower(location) like '%nepa%'
ORDER BY location desc;

It gives following result:

Sort  (cost=12.41..12.42 rows=1 width=451) (actual time=0.084..0.087 rows=8 loops=1)
Sort Key: location
Sort Method: quicksort  Memory: 27kB
  ->  Seq Scan on users  (cost=0.00..12.40 rows=1 width=451) (actual time=0.029..0.051 rows=8 loops=1)
      Filter: (lower((location)::text) ~~ '%nepa%'::text)
 Planning time: 0.211 ms
 Execution time: 0.147 ms

I have searched through stackoverflow. Found most answers to be like "postgres performs sequential scan in large table in case index scan will be slower". But my table is not big either.

The index in my users table is:

"index_users_on_lower_location_varchar_pattern_ops" btree (lower(location::text) varchar_pattern_ops)

What is going on?

Erwin Brandstetter · Accepted Answer · 2024-01-21 22:08:32Z

6

*_patter_ops indexes are good for prefix matching - LIKE patterns anchored to the start, without leading wildcard. But not for your predicate:

WHERE lower(location) like '%nepa%'

I suggest a trigram index instead. You don't need lower() in the index (or query) since trigram indexes support case insensitive ILIKE (or ~*) at practically the same cost.

Follow instructions here:

PostgreSQL LIKE query performance variations

Also:

But my table is not big either.

You seem to have that backwards. If your table is not big enough, it's faster for Postgres to just read it sequentially and not bother with indexes. You would not create any indexes at all. The tipping point depends on many factors.

Your index definition does not make sense to begin with:

(lower(location::text) varchar_pattern_ops)

For a varchar columns use the varchar_pattern_ops operator class.
But lower() returns text even for varchar input, so use text_pattern_ops. Except that you probably do not need this (or any?) index at all, as advised.
And if you still think you need that index, consider a COLLATE "C" index instead. See:

Is there a difference between text_pattern_ops and COLLATE "C"?

edited Jan 21, 2024 at 22:08

answered Sep 10, 2017 at 4:40

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rabin Poudyal Over a year ago

Yes. Thank you very much. I will definitely do that. You made me clear pattern_ops are used for prefix matching. I did not understand that before. Also, I will use varchar for location field. You are a hero.

Collectives™ on Stack Overflow

Sequential scan for column indexed with varchar_pattern_ops

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related