Postgres Regex Replace Performance
postgresql, regex, sql
Solution
In general, SQL queries are always faster if you include a where statement to limit them down. So the second should definitely be faster. Essentially, databases are able to do that kind of operation very quickly. The first one first gets the entire list, then checks it via the regex statement. The latter only has to perform regex on the shortened list.
However, as a_horse_with_no_name pointed out, unless there is an index associated with `%ReplaceMe%`, then the query won't go any faster. It still should go slightly quicker, however, as fewer items will be processed through the regex command.
Problem
I have a Postgres performance question. I have a table of about 500K rows of text. I need to do some global search and replace routines. Would one of these two UPDATE statements be significantly faster than the other? Or would they be pretty similar in performance? ``` update mytable set mycolumn = regexp_replace(mycolumn, 'ReplaceMe','WithMe', 'g'); update mytable set mycolumn = regexp_replace(mycolumn, 'ReplaceMe','WithMe', 'g') where mycolumn like '%ReplaceMe%'; ```