Sample Header Ad - 728x90

Postgresql (11.9) LIKE query not using text_ops index

1 vote
1 answer
507 views
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); index description from DBeaver 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.
Asked by nfdavenport (121 rep)
Dec 7, 2021, 07:26 PM
Last activity: Mar 2, 2025, 10:04 PM