Re: Performance of LIKE/NOT LIKE when used in single query

On Wednesday, June 8, 2016, Ed Felstein <efelstein(at)gmail(dot)com> wrote:

> Hello,
> First time poster here. Bear with me.
> Using PostgreSQL 9.5
> I have a situation where I have a LIKE and a NOT LIKE in the same query to
> identify strings in a varchar field. Since I am using wildcards, I have
> created a GIN index on the field in question, which makes LIKE '%xxxx%'
> searches run very fast. The problem is the NOT LIKE phrases, which (as
> would be expected) force a sequential scan. Being that we're talking about
> millions of records, this is not desirable.
> Here's the question...
> Is there a way, *using a single query*, to emulate the process of running
> the LIKE part first, then running the NOT LIKE just on those results? I
> can accomplish this in a multi-step process by separating the single query
> into two queries, populating a temporary table with the results of the
> LIKEs, then running the NOT LIKEs on the temporary table. For various
> reasons, this is not the ideal solution for me.
> Or is there another approach that would accomplish the same thing with the
> same level of performance?
>

Try AND...where col like '' and col not like ''

Or a CTE (with)

With likeqry as ( select where like )
Select from likeqry where not like

(sorry for brevity but not at a pc)

David J.