Postgres not using index with text_pattern_ops for pattern matching query

dferrazm 0
Viewed 33

I have a column foo_bar of type text indexed with text_pattern_ops:

profiles_foo_bar_txt" btree (foo_bar text_pattern_ops)

My psql server is configured with UTF-8:

lc_collate 
------------
 en_US.utf8
(1 row)

But for some reason the index is not being used for pattern matching queries:

EXPLAIN SELECT "profiles".* FROM "profiles" WHERE foo_bar LIKE 'Mar%';

results in

                           QUERY PLAN                            
-----------------------------------------------------------------
 Seq Scan on profiles  (cost=0.00..2288.12 rows=51370 width=142)
   Filter: (foo_bar ~~ 'Mar%'::text)
(2 rows)

Am I missing something? Any ideas on what I might be doing wrong?

EDIT:

As requested on Frank Heiken's comment, here's the out put when using EXPLAIN(ANALYZE, VERBOSE, BUFFERS):

    QUERY PLAN                                                      
----------------------------------------------------------------------------------------------------------------------
 Seq Scan on public.profiles  (cost=0.00..2436.12 rows=51370 width=11) (actual time=0.013..14.224 rows=51370 loops=1)
   Output: foo_bar
   Filter: (profiles.foo_bar ~~ 'Mar%'::text)
   Rows Removed by Filter: 1
   Buffers: shared hit=1794
 Planning Time: 0.077 ms
 Execution Time: 16.932 ms
(7 rows)

So it seems that indded the index with text_pattern_ops it not being for that pattern matching query.

Any ideas?

database postgresql
1 Answers

So it, seems that @jjanes comment was right. It turns out that I added that foo_bar for test purposes and all the 50k rows had the same value on that column (eg Mary John). So probably the query Mar% was so unselective (ie would match everything) that using the index didn't make sense.

I then updated that column and populated with different names, ie, there were now names starting with Mas*, Mar* and other completely different names.

I then reran EXPLAIN and could confirm that the index was indeed being used now:

                                                                     QUERY PLAN                                                                      
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using profiles_foo_bar_txt on public.profiles  (cost=0.29..597.03 rows=16923 width=6) (actual time=0.020..4.951 rows=16978 loops=1)
   Output: foo_bar
   Index Cond: ((profiles.foo_bar ~>=~ 'Mar'::text) AND (profiles.foo_bar ~<~ 'Mas'::text))
   Filter: (profiles.foo_bar ~~ 'Mar%'::text)
   Heap Fetches: 0
   Buffers: shared hit=69
 Planning Time: 0.104 ms
 Execution Time: 5.910 ms
(8 rows)

Btw, without the index, that same query would take between 12ms - 18ms for 50k+ rows. With the index, it takes between 3ms - 7ms now.

There are 16,978 results which match that Mar% query.

In terms of whether the gain of performance makes sense for this scenario (or if it should've been more), I cannot say.

According to this article, for the same amount of rows 50k, the author had an improvement for a prefix match query from 8ms (without the index) to 1ms (with the index). So something looks off still.

dferrazm0
Related