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
Last activity: Oct 27, 2023, 04:52 AM