PostgreSQL performance difference between LIKE and regex

Question

Could someone explain such a big performance difference between these SQLs ?

SELECT count(*) as cnt FROM table WHERE name ~ '\*{3}'; -- Total runtime 12.000 - 18.000 ms
SELECT count(*) as cnt FROM table WHERE name ~ '\*\*\*'; -- Total runtime 12.000 - 18.000 ms
SELECT count(*) as cnt FROM table WHERE name LIKE '%***%'; -- Total runtime 5.000 - 7.000 ms

As you can see, the difference is more than double between LIKE operator and simple regular expression (I thought LIKE operator internally would be converted into the regular expression and there shouldn't be any difference)

There are almost 13000 rows there and the column "name" is of "text" type. There are no indexes related to the "name" column defined in the table.

EDIT:

EXPLAIN ANALYZE OF EACH OF THEM:

EXPLAIN ANALYZE SELECT count(*) as cnt FROM datos WHERE nombre ~ '\*{3}';

Aggregate  (cost=894.32..894.33 rows=1 width=0) (actual time=18.279..18.280 rows=1 loops=1)
  ->  Seq Scan on datos (cost=0.00..894.31 rows=1 width=0) (actual time=0.620..18.266 rows=25 loops=1)
        Filter: (nombre ~ '\*{3}'::text)
Total runtime: 18.327 ms

EXPLAIN ANALYZE SELECT count(*) as cnt FROM datos WHERE nombre ~ '\*\*\*';
Aggregate  (cost=894.32..894.33 rows=1 width=0) (actual time=17.404..17.405 rows=1 loops=1)
  ->  Seq Scan on datos  (cost=0.00..894.31 rows=1 width=0) (actual time=0.608..17.396 rows=25 loops=1)
        Filter: (nombre ~ '\*\*\*'::text)
Total runtime: 17.451 ms

EXPLAIN ANALYZE SELECT count(*) as cnt  FROM datos WHERE nombre LIKE '%***%';
Aggregate  (cost=894.32..894.33 rows=1 width=0) (actual time=4.258..4.258 rows=1 loops=1)
  ->  Seq Scan on datos  (cost=0.00..894.31 rows=1 width=0) (actual time=0.138..4.249 rows=25 loops=1)
        Filter: (nombre ~~ '%***%'::text)
Total runtime: 4.295 ms

@CraigRinger I added explain analyze of each query into the question's text — dmikam
– dmikam, Commented Apr 6, 2015 at 8:55
To run a regex comparison is more expensive than to apply a dummy LIKE format. — zerkms
– zerkms, Commented Apr 6, 2015 at 8:55
@dmikam I do not agree - regex is harder to parse and harder to apply. If you don't agree with me - try to implement both LIKE and PCRE-compatible engines. Then compare which one took more effort and works slower. "matching only strings ending with ***" --- nope, there are also whitespaces there. — zerkms
– zerkms, Commented Apr 6, 2015 at 9:20
The three queries have a small footprint, and are completely served from memory/buffers. That's why the CPU cost dominates the total cost. Once the data has to be pulled from disk, the cost will be dominated by seektime and I/O, and the queries will roughly perform the same. (at least: that is what I expect) — wildplasser
– wildplasser, Commented Apr 6, 2015 at 13:33

Juergen · Accepted Answer · 2015-05-28 19:34:59Z

20

The text LIKE text operator (~~) is implemented by specific C code in like_match.c. It's ad-hoc code that is completely independent from regular expressions. Looking at the comments, it's obviously specially optimized to implement only % and _ as wildcards, and short-circuiting to an exit whenever possible, whereas a regular expression engine is more complex by several orders of magnitude.

Note that in your test case , just like the regexp is suboptimal compared to LIKE, LIKE is probably suboptimal compared to strpos(name, '***') > 0

strpos is implemented with the Boyer–Moore–Horspool algorithm which is optimized for large substrings with few partial matches in the searched text.

Internally these functions are reasonably optimized but when there are several methods to the same goal, choosing the likely best is still the job of the caller. PostgreSQL will not analyze for us the pattern to match and switch a regexp into a LIKE or a LIKE into a strpos based on that analysis.

edited May 28, 2015 at 19:34

Juergen

12.8k7 gold badges43 silver badges56 bronze badges

answered Apr 6, 2015 at 16:00

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Casimir et Hippolyte Over a year ago

Note that several regex engines test the string before starting the engine walk with the Boyer-Moore algorithm too when the pattern contains or starts with a literal string (as a pre-optimization to fail faster). I don't know if it is the case with Postgres.

dmikam Over a year ago

I looked through like_match.c and it is definitely much more simple then regex(even if it is still recursive). So my initial mistake was thinking that LIKE internally implemented using regex or something very similar. Naturally strpos algorithm is even "cheaper" but in my rough comparison it confirmed that the CPU cost of chosen string analysis algorithm could potentially create that difference in execution time I had.

Collectives™ on Stack Overflow

PostgreSQL performance difference between LIKE and regex

EDIT:

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

EDIT:

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related