Sample Header Ad - 728x90

In PostgreSQL, can an index predicate be used to improve performance of `ON CONFLICT`?

0 votes
1 answer
210 views
Consider the following upsert, run in a trigger:
INSERT INTO 
  current_scores (user_id, score, updated_at)
VALUES 
  (NEW.user_id, NEW.score, NEW.inserted_at)
ON CONFLICT (user_id)
DO UPDATE SET
  score = NEW.score,
  updated_at = NEW.inserted_at
WHERE NEW.inserted_at > current_scores.updated_at;
It has been suggested to me that duplicating the WHERE from the ON CONFLICT as an index predicate, like this:
INSERT INTO 
  current_scores (user_id, score, updated_at)
VALUES 
  (NEW.user_id, NEW.score, NEW.inserted_at)
ON CONFLICT (user_id)

-- added index predicate
WHERE NEW.inserted_at > current_scores.updated_at

DO UPDATE SET
  score = NEW.score,
  updated_at = NEW.inserted_at
WHERE NEW.inserted_at > current_scores.updated_at;
...could improve performance by reducing the frequency that ON CONFLICT needs to run. But I don't understand why this might be the case. If ON CONFLICT is expensive, this seems like something the database could figure out on its own. Can an index predicate be used to reduce conflict resolution and improve performance?
Asked by Nathan Long (1005 rep)
Oct 26, 2023, 02:37 PM
Last activity: Oct 27, 2023, 04:52 AM