3

I am building SQL queries and I'm wondering how using posix regular expressions (such as ~ , ~* , !~ , !~*) or LIKE and ILIKE affects the performance of those queries. Does using any of these affects the speed and performance of my SQL queries? If yes, how? And which is more applicable to use?

john igneel
  • 367
  • 1
  • 4
  • 18

3 Answers3

1

The regex/like operators require something to happen in the DB, so of course they somehow affect performance... but they all do a specific job.

LIKE provides a simple syntax, but not much functionality. According to another SO answer, the LIKE functionality is pretty specialized and therefore probably more performant when compared to an equivalent regex.

which is more applicable to use?

Not all text can be matched by a LIKE, so in those cases you'll have to use a regex. But if LIKE is sufficient, the linked answer suggests that it would be better to use that.

If you're concerned about a specific query, use postgres' EXPLAIN ANALYZE to see what postgres is actually going to do.

Community
  • 1
  • 1
beerbajay
  • 19,652
  • 6
  • 58
  • 75
0

Based on my research, POSIX regular expressions are more applicable than using LIKE and ILIKE clause due to some advantages:

  1. SPEED
  2. Simple clause query

Here are some examples of the posix regex usage:

~ tilde for case sensitive

POSIX: SELECT record FROM mytable WHERE record ~ 'a';
LIKEi: SELECT record FROM mytable WHERE record like '%a%';

~* for case insensitive

POSIX: SELECT record FROM mytable WHERE record ~* 'a';
LIKEi: SELECT record FROM mytable WHERE record ilike '%A%';

!~ exclude/not (case sensitive)

POSIX: SELECT record FROM mytable WHERE record !~ 'a';
LIKEi: SELECT record FROM mytable WHERE record not like '%a%';

!~* exclude/not (case insensitive)

POSIX: SELECT record FROM mytable WHERE record !~ 'a';
LIKEi: SELECT record FROM mytable WHERE record not ilike '%a%';

Some other usage of posix regex , LIKE and ILIKE can be found here.

john igneel
  • 367
  • 1
  • 4
  • 18
0

Variations on the example of Ohn Igneel,

create temporary table test(
c1 varchar
);
insert into test values ('Andrea'), ('Luis'), ('Thomas'), ('Maria');

-- ~ tilde for case sensitive
SELECT c1 from test WHERE c1 ~ 'A';
SELECT c1 from test WHERE c1 like '%A%'; 
|c1    |
|------|
|Andrea|
-- ~* for case insensitive
SELECT c1 from test WHERE c1 ~* 'a';
SELECT c1 from test WHERE c1 ilike '%A%';
|c1    |
|------|
|Andrea|
|Thomas|
|Maria |
-- !~ exclude/not (case sensitive)
SELECT c1 from test WHERE c1 !~ 'm';
SELECT c1 from test WHERE c1 not like '%m%';
|c1    |
|------|
|Andrea|
|Luis  |
|Maria |
-- !~* exclude/not (case insensitive)
SELECT c1 from test WHERE c1 !~* 'M';
SELECT c1 from test WHERE c1 not ilike '%M%';
|c1    |
|------|
|Andrea|
|Luis  |
Luis
  • 71
  • 5