Which is faster to update thousands of table rows?

Question

With stored procedures in PostgreSQL v10.1.2, which is the fastest way or which is better: to check if rows exists and then update or try to update directly with the possibility that no rows matching the condition are found? I need to check a lot of tables with the same condition, tables are not 'denormalized', I made a couple of tests and sometimes option 1 is faster in other cases option 2 ...

Option 1:

IF EXISTS ( SELECT  1
FROM   public.table1 
WHERE  column1 = 'oldvalue' )
THEN
   UPDATE public.table1
   SET   column1 = 'newvalue' , date_update= .... 
   WHERE  column1 = 'oldvalue';
END IF ;

Option 2:

UPDATE public.table1
SET   column1 = 'newvalue' , date_update= ... 
WHERE  column1 = 'oldvalue';

Option 3:

perform FROM   public.table1 WHERE  column1 = 'oldvalue' ;
if found then 
   UPDATE public.table1 SET column1='newvalue', date_update = ... WHERE column1 = 'oldvalue';
end if ;

---

I made:

BEGIN;
EXPLAIN ANALYZE VERBOSE select a_test_w_perform();
ROLLBACK;

for every option and the time average vary, results in time are something like:

• Option 1

  Query returned successfully in 311 msec. / 423 msec./ 242 msec./ 367
  

• Option 2

  Query returned successfully in 308 msec. / 307 msec./311 msec./ 200
  

  *Option 3

  Query returned successfully in 204 msec./ 279 msec./451 msec. / 230
  

Answer 1

It's generally faster to just try the UPDATE.

Issuing a SELECT first, just increases the read cost. Plus, more importantly, it's unreliable under concurrent write load to the database anyway. You would have to add FOR UPDATE or similar to lock rows. But don't. Just issue the UPDATE.

One way to increase performance in many cases, though: avoid empty updates:

UPDATE public.table1
SET    column1 = 'newvalue'
     , date_update = ... 
WHERE  column1 = 'oldvalue'
AND   (column1   , date_update) IS DISTINCT FROM 
      ('newvalue', ...        );  -- to skip empty updates

Related:

• How do I (or can I) SELECT DISTINCT on multiple columns?

It can make sense to issue SELECT first if you want to involve LIMIT (or similar), which is not allowed in UPDATE directly. But consider concurrency:

• Postgres UPDATE ... LIMIT 1

Resulting performance can vary with lots of parameters. Warm cache, concurrent transactions, load on the sever, etc. Your case may be particularly tricky:

check a lot of tables with the same condition

Involving lots of tables makes it hard to find the best query plan. If you know that a certain predicate is particularly selective - and Postgres doesn't - then it might pay to run a cheap, simplified SELECT first. But you need to deal with concurrency properly.

Typically, this would indicate problems with your query or your server configuration concerning cost settings or statistics, and it would be better to fix that instead of working around the problem. But Postgres does have a few blind spots, like no combined statistics for multiple conditions or no statistics for nested values in document types like jsonb, xml etc.

Update: Postges 10 added "multi-column optimizer statistics to compute the correlation ratio and number of distinct values". (Still does not help with values nested in document types.) Details in the manual.

When updating large parts of the whole table, different strategies might pay:

• Optimizing bulk update performance in PostgreSQL