Postgresql (11.9) LIKE query not using text_ops index

Question

I've seen related questions but I think this should be working and isn't. I have a text_ops function based index on column to do LIKE searches, but it only picks up the index with an equals:

CREATE INDEX foo ON mytable USING btree ((upper((street)::text), group_id, current_version, pending, storage_id);

This query is lightning fast on my 113M row table:

select * from  mytable 
where upper(street) = '104 LESHAWN COVE'
and group_id = 5022352
and current_version = 1
and pending = 0

The corresponding LIKE is takes a couple of minutes:

select * from  mytable 
where upper(street) LIKE '104 LESHAWN COVE%'
and group_id = 5022352
and current_version = 1
and pending = 0

The explain plan show the one using the index and the other not. I thought the text_ops indexed column should allow for the LIKE to work, but I must be missing something here.

show lc_collate  
  en_US.UTF-8

I don't know that it matters, but this is on a partitioned table on the group_id column which also exists in the index for unpartitioned environments. The index is on the partition in this case.

So what am I missing. These operator classes are new to me, so I must not understand part of it.

Answer 1

While text and varchar columns in postgresql are basically identical, the operation patterns text_ops and varchar_pattern_ops are not. Specifically calling out the varchar_pattern_ops in the index creation is necessary to make the LIKE work. At least that is what fixed it for me.

CREATE INDEX foo2 ON mytable USING btree ((upper((street)::text) varchar_pattern_ops, group_id, current_version, pending, storage_id);