MIME-Version: 1.0
In-Reply-To: 
 <CAJpH9Wk3z+iuxFznSgS57wyAMh5dkx9+LPym=y0FEHq0oVWFRQ@mail.gmail.com>
References: 
 <CAJpH9Wk3z+iuxFznSgS57wyAMh5dkx9+LPym=y0FEHq0oVWFRQ@mail.gmail.com>
Date: Wed, 8 Jun 2016 01:33:24 -0400
Message-ID: 
 <CAKFQuwbNn+97A+55LdMDOAQO+zopNwY++kEyAvjUyi9McULPOw@mail.gmail.com>
Subject: Re: Performance of LIKE/NOT LIKE when used in single query
From: "David G. Johnston" <david.g.johnston@gmail.com>
To: Ed Felstein <efelstein@gmail.com>
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Content-Type: multipart/alternative; boundary=001a113ce218f1119e0534bda49e
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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

On Wednesday, June 8, 2016, Ed Felstein <efelstein@gmail.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.

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

<br><br>On Wednesday, June 8, 2016, Ed Felstein &lt;<a href=3D"mailto:efels=
tein@gmail.com">efelstein@gmail.com</a>&gt; wrote:<br><blockquote class=3D"=
gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-=
left:1ex"><div dir=3D"ltr"><div>Hello,</div><div>First time poster=C2=A0her=
e.=C2=A0 Bear with me.</div><div>Using PostgreSQL 9.5</div><div>I have a si=
tuation where I have a LIKE and a NOT LIKE in the same query to identify st=
rings in=C2=A0a varchar=C2=A0field.=C2=A0 Since I am using wildcards, I hav=
e created a GIN index on the field in question, which makes LIKE &#39;%xxxx=
%&#39; searches run very fast.=C2=A0 The problem is the NOT LIKE phrases, w=
hich (as would be expected) force a sequential scan.=C2=A0 Being that we=
9;re talking about millions of records, this is not desirable.</div><div>He=
re&#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 th=
e NOT LIKE=C2=A0just on those results?=C2=A0 I can accomplish this in a mul=
ti-step process=C2=A0by separating the single query into two queries,=C2=A0=
populating a temporary table with the results of the LIKEs, then running th=
e NOT LIKEs on the temporary table.=C2=A0 For various reasons, this is not =
the ideal solution for me.</div><div>Or is there another approach that woul=
d accomplish the same thing with the same level of performance?</div></div>=
</blockquote><div><br></div><div><br></div><div>Try AND...where col like &#=
39;&#39;=C2=A0and col not like &#39;&#39;</div><div><br></div><div>Or a CTE=
 (with)</div><div><br></div><div>With likeqry as ( select where like )</div=
><div>Select from likeqry where not like</div><div><br></div><div>(sorry fo=
r brevity but not at a pc)</div><div><br></div><div>David J.=C2=A0</div>

--001a113ce218f1119e0534bda49e--