0

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?

Improve this question
4
  • 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, 2017 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, 2017 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, 2017 at 6:44
  • Please Edit your question and add the output of explain (analyze, verbose). Formatted text please, no screen shots
    – user1822
    Jul 13, 2017 at 11:56

1 Answer 1

Reset to default
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.

Improve this answer
1
  • 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, 2017 at 4:08

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