0

This question already has an answer here:

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?

marked as duplicate by McNets, Marian, Erik Darling, LowlyDBA, gbn Jul 13 '17 at 14:33

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 3
    So the query basically returns the whole table, except perhaps a very small part. Why is doing a sequential scan unexpected? – ypercubeᵀᴹ Jul 10 '17 at 11:28
  • 1
    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 Jul 10 '17 at 16:18
  • Anybody, pl help. The "not like" clause doesnt seem to use any index, normal or trigram. Whats the way out? – Sudhir Jul 11 '17 at 6:44
  • Please Edit your question and add the output of explain (analyze, verbose). Formatted text please, no screen shots – a_horse_with_no_name Jul 13 '17 at 11:56
2

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.

  • 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 – Sudhir Jul 14 '17 at 4:08

Not the answer you're looking for? Browse other questions tagged or ask your own question.