Thread: NOT LIKE index support

<p dir="ltr">I noticed index support for NOT LIKE is missing. Is there a special reason for that, or would a patch be
accepted?<pdir="ltr">A use case would be:<p dir="ltr">... WHERE url NOT LIKE 'http%'<p dir="ltr">Or<p dir="ltr">...
WHEREpath NOT LIKE '/%' 

On 03/15/2016 11:01 PM, Arjen Nienhuis wrote:
> I noticed index support for NOT LIKE is missing. Is there a special
> reason for that, or would a patch be accepted?
>
> A use case would be:
>
> ... WHERE url NOT LIKE 'http%'
>
> Or
>
> ... WHERE path NOT LIKE '/%'

My guess is the lack of many compelling use cases for such a feature. 
Indexes are generally only useful for expressions with a high 
selectivity (i.e. where you match a small percentage of the rows in the 
table), and in most cases NOT LIKE would match more than half of the 
rows in the table causing the query planner to prefer doing a sequential 
scan. And your examples above seem like they would match most rows in 
the table, making an index scan rarely worth it.

We do not have support for indexing the <> operator either for btree 
indexes.

Andreas