Postgres Regex Replace Performance

Question

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%';

PearsonArtPhoto · Accepted Answer · 2012-11-24 12:29:22Z

up vote 2 down vote accepted

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.

Howver, 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, howver, as fewer items will be processed through the regex command.

answered 15 hours ago

PearsonArtPhoto
4,84921035

2

The way it is written, the where statement will not reduce the time it takes to find the rows. Postgres will most probably read through the entire table because the expression like '%ReplaceMe%' will not use an index (unless a trgm index is available). The second one will however update less rows and might therefor be faster. – a_horse_with_no_name 15 hours ago

Okay, I've added that in. Thanks for the info! – PearsonArtPhoto 15 hours ago

3

Thanks. I see your points. I think the key in my situation is the updates--without the WHERE, I'm updating 500K rows, even if they don't need it. With the WHERE, I'm only updating the rows that will change (which is about 3% of the total, so whittling down with the WHERE before hand will help). – Paulb 15 hours ago

1

That's exactly right, Paulb. It's also worth noting that the regex aspect of this question is a bit of a red herring. The amount of time it takes to do the regexp_replace is dwarfed by the amount of time it takes to update the row, so the difference between set mycolumn = regexp_replace(mycolumn, 'ReplaceMe','WithMe', 'g'); and set mycolumn = 'x' would probably be unnoticeable. – Andy Lester 11 hours ago

feedback

asked	today
viewed	28 times
active	today

Postgres Regex Replace Performance

1 Answer

Your Answer

Not the answer you're looking for? Browse other questions tagged sql regex postgresql or ask your own question.

Hello World!

Postgres Regex Replace Performance

1 Answer

Your Answer

Not the answer you're looking for? Browse other questions tagged sql regex postgresql or ask your own question.

Hello World!

Related