Index not used in "not like" query [duplicate]

Question

This question already has answers here:

postgres: index to SELECT strings like '%foo%'; (4 answers)

Closed 8 years ago.

I am using PostgreSQL 9.4 and having tough time with slow queries. Some of these use "like" and others use "not like" searches. For like searches I could resolve by using trigram indexes, but those with not like are a headache.

e.g.

select * from mytable where user_code not like 'TE%'

EXPLAIN ANALYZE says "Seq scan". How can I make Postgres use index in such cases?

• ypercubeᵀᴹ: So the query basically returns the whole table, except perhaps a very small part. Why is doing a sequential scan unexpected?
• Sudhir: yes, right. When the query is select * from mytable where user_code like 'TE%' ("not" removed) then the trigram index kicks in and query runs in a snap
• Sudhir: Anybody, pl help. The "not like" clause doesnt seem to use any index, normal or trigram. Whats the way out?
• user1822: Please Edit your question and add the output of explain (analyze, verbose). Formatted text please, no screen shots

Answer 1

The query with NOT LIKE is not using any index because the optimizer decides that it is cheaper to do a sequential scan. Nothing unexpected here, it is probably much cheaper.

The condition user_code LIKE 'TE%' will probably keep a very small number of rows (say 1%), so it is good to find this small number by using an index.

But the query with user_code NOT LIKE 'TE%' will probably return a big number of rows (99% if LIKE returns 1%) so it is easier to do a sequential scan and just remove the 1% afterwards. If an index was used, it would have to do the index seek and then still go and read (that 99% of rows) from the table anyway, so almost the entire heap.

• Sudhir: Yes, I figured that out. In my case the ratio is different though. "not like TE%" records are about 66% . Still I think seq scan can be faster as these are double in number. So what you said is happening here