public inbox for [email protected]help / color / mirror / Atom feed
Performance of LIKE/NOT LIKE when used in single query 3+ messages / 3 participants [nested] [flat]
* Performance of LIKE/NOT LIKE when used in single query @ 2016-06-08 04:57 Ed Felstein <[email protected]> 2016-06-08 05:33 ` Re: Performance of LIKE/NOT LIKE when used in single query David G. Johnston <[email protected]> 2016-06-09 16:37 ` Re: Performance of LIKE/NOT LIKE when used in single query Jeff Janes <[email protected]> 0 siblings, 2 replies; 3+ messages in thread From: Ed Felstein @ 2016-06-08 04:57 UTC (permalink / raw) To: pgsql-performance 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? ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Performance of LIKE/NOT LIKE when used in single query 2016-06-08 04:57 Performance of LIKE/NOT LIKE when used in single query Ed Felstein <[email protected]> @ 2016-06-08 05:33 ` David G. Johnston <[email protected]> 1 sibling, 0 replies; 3+ messages in thread From: David G. Johnston @ 2016-06-08 05:33 UTC (permalink / raw) To: Ed Felstein <[email protected]>; +Cc: pgsql-performance On Wednesday, June 8, 2016, 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? 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. ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Performance of LIKE/NOT LIKE when used in single query 2016-06-08 04:57 Performance of LIKE/NOT LIKE when used in single query Ed Felstein <[email protected]> @ 2016-06-09 16:37 ` Jeff Janes <[email protected]> 1 sibling, 0 replies; 3+ messages in thread From: Jeff Janes @ 2016-06-09 16:37 UTC (permalink / raw) To: Ed Felstein <[email protected]>; +Cc: pgsql-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 ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2016-06-09 16:37 UTC | newest] Thread overview: 3+ messages (download: mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2016-06-08 04:57 Performance of LIKE/NOT LIKE when used in single query Ed Felstein <[email protected]> 2016-06-08 05:33 ` David G. Johnston <[email protected]> 2016-06-09 16:37 ` Jeff Janes <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox