MIME-Version: 1.0
From: Ed Felstein <efelstein@gmail.com>
Date: Tue, 7 Jun 2016 21:57:44 -0700
Message-ID: 
 <CAJpH9Wk3z+iuxFznSgS57wyAMh5dkx9+LPym=y0FEHq0oVWFRQ@mail.gmail.com>
Subject: Performance of LIKE/NOT LIKE when used in single query
To: pgsql-performance@postgresql.org
Content-Type: multipart/alternative; boundary=001a113aaafc7138da0534bd25ac
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

--001a113aaafc7138da0534bd25ac
Content-Type: text/plain; charset=UTF-8

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?

--001a113aaafc7138da0534bd25ac
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hello,</div><div>First time poster=C2=A0here.=C2=A0 B=
ear with me.</div><div>Using PostgreSQL 9.5</div><div>I have a situation wh=
ere I have a LIKE and a NOT LIKE in the same query to identify strings in=
=C2=A0a varchar=C2=A0field.=C2=A0 Since I am using wildcards, I have create=
d a GIN index on the field in question, which makes LIKE &#39;%xxxx%&#39; s=
earches run very fast.=C2=A0 The problem is the NOT LIKE phrases, which (as=
 would be expected) force a sequential scan.=C2=A0 Being that we&#39;re tal=
king about millions of records, this is not desirable.</div><div>Here&#39;s=
 the question...</div><div>Is there a way, <u>using a single query</u>, to =
emulate the process of running the LIKE part first, then running the NOT LI=
KE=C2=A0just on those results?=C2=A0 I can accomplish this in a multi-step =
process=C2=A0by separating the single query into two queries,=C2=A0populati=
ng a temporary table with the results of the LIKEs, then running the NOT LI=
KEs on the temporary table.=C2=A0 For various reasons, this is not the idea=
l solution for me.</div><div>Or is there another approach that would accomp=
lish the same thing with the same level of performance?</div><div><br></div=
></div>

--001a113aaafc7138da0534bd25ac--