public inbox for [email protected]
help / color / mirror / Atom feedFrom: Jeff Janes <[email protected]>
To: Ed Felstein <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Performance of LIKE/NOT LIKE when used in single query
Date: Thu, 9 Jun 2016 09:37:50 -0700
Message-ID: <CAMkU=1wUJRXHf9YNWqC_KDywvmTYvmhvXq2oX+nzz5Z53YZH+A@mail.gmail.com> (raw)
In-Reply-To: <CAJpH9Wk3z+iuxFznSgS57wyAMh5dkx9+LPym=y0FEHq0oVWFRQ@mail.gmail.com>
References: <CAJpH9Wk3z+iuxFznSgS57wyAMh5dkx9+LPym=y0FEHq0oVWFRQ@mail.gmail.com>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-performance>
On Tue, Jun 7, 2016 at 9:57 PM, Ed Felstein <[email protected]> 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?
Just do it. In my hands, the planner is smart enough to figure it out
for itself.
explain analyze select * from stuff where synonym like '%BAT%' and
synonym not like '%col not like%' ;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on stuff (cost=16.10..63.08 rows=13 width=14)
(actual time=9.465..10.642 rows=23 loops=1)
Recheck Cond: (synonym ~~ '%BAT%'::text)
Rows Removed by Index Recheck: 76
Filter: (synonym !~~ '%col not like%'::text)
Heap Blocks: exact=57
-> Bitmap Index Scan on integrity_synonym_synonym_idx
(cost=0.00..16.10 rows=13 width=0) (actual time=8.847..8.847 rows=99
loops=1)
Index Cond: (synonym ~~ '%BAT%'::text)
Planning time: 18.261 ms
Execution time: 10.932 ms
So it is using the index for the positive match, and filtering those
results for the negative match, just as you wanted.
Cheers,
Jeff
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Performance of LIKE/NOT LIKE when used in single query
In-Reply-To: <CAMkU=1wUJRXHf9YNWqC_KDywvmTYvmhvXq2oX+nzz5Z53YZH+A@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox