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?

Answer 1

*_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"?