3

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
  • 605,456
  • 145
  • 1,078
  • 1,228
Rabin Poudyal
  • 717
  • 6
  • 20

1 Answers1

5

*_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 you create a trigram index instead. And you do not need lower() in the index (or query) since trigram indexes support case insensitive ILIKE (or ~*) at practically the same cost.

Follow instructions here:

Also:

But my table is not big either.

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

Aside: 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 if you cast to text, use text_pattern_ops. Since lower() returns text even for varchar input, use text_pattern_ops. Except that you probably do not need this (or any?) index at all, as advised.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 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. – Rabin Poudyal Sep 10 '17 at 05:12