Regular Expressions and Performance

Regular expression have a significant impact on system performance. The regular expression itself can be processed quickly, but what about indexes and how do regular expressions influence the optimizer?

Let's take a look at a simple query where we want to retrieve 100 from the perftest table:

 performance=#  EXPLAIN SELECT * FROM perftest WHERE id=100;  NOTICE:  QUERY PLAN: Index Scan using idx_id_perftest on perftest  (cost=0.00..4.98 rows=1 width=20) EXPLAIN 

The database performs as you might expect ”an index scan and quickly returning the result. Now we try to retrieve the same value with the help of a regular expression:

 performance=#  EXPLAIN SELECT * FROM perftest WHERE id ~ '^100$';  NOTICE:  QUERY PLAN: Seq Scan on perftest  (cost=100000000.00..100218966.00 rows=100000 width=20) EXPLAIN 

The database does a sequential scan now and our query has suddenly become ghastly slow. We can do nothing about it but try to reduce the amount of data that has to be processed by the regular expression.

Reducing the amount of data can be achieved by using temporary tables. Of course, this can only be done with SQL statements containing expressions that significantly reduce the amount of data.

Regular expressions are very powerful, but you can see in the previous example that you have to use them very carefully when you want to write fast applications. If you have only small tables, the impact on performance may not be huge; but when tables grow and many joins with many regular expressions are involved, it may happen that the query cannot be processed anymore.