Re: NOT LIKE index support - Mailing list pgsql-hackers

From Andreas Karlsson
Subject Re: NOT LIKE index support
Date
Msg-id 56E894E7.3020800@proxel.se
Whole thread Raw
In response to NOT LIKE index support  (Arjen Nienhuis <a.g.nienhuis@gmail.com>)
List pgsql-hackers
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




pgsql-hackers by date:

Previous
From: David Rowley
Date:
Subject: Re: Parallel Aggregate
Next
From: Robert Haas
Date:
Subject: Re: Parallel Aggregate