Performance impact of updating target columns with unchanged values ON CONFLICT

From: Abi Noda <a(at)abinoda(dot)com>
To: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: Performance impact of updating target columns with unchanged values ON CONFLICT
Date: 2018-11-22 19:32:17
Message-ID: CAM37AMMs_FLcg-ZGm2JSK7PD_+f5unNa-wipGH4dLiprdhgaTw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Given a table, `github_repos`, with a multi-column unique index on `org_id`
and `github_id` columns, is there any performance difference (or other
issues to be aware of) between the two bulk upsert operations below? The
difference is that in the first query, the `org_id` and `github_id` columns
are included in the UPDATE, whereas in the second query they are not. Since
the UPDATE runs ON CONFLICT, the updated values of `org_id` and `github_id`
will be the same as the old values, but those columns are included in the
UPDATE because the underlying library I am using is designed that way. I'm
wondering if its safe to use as-is or whether I should be explicitly
excluding those columns in the UPDATE.

Query #1:

INSERT INTO "github_repos" ("org_id","github_id","name")
VALUES (1,1,'foo')
ON CONFLICT (org_id, github_id)
DO UPDATE SET
"org_id"=EXCLUDED."org_id","github_id"=EXCLUDED."github_id","name"=EXCLUDED."name"
RETURNING "id"

Query #2:

INSERT INTO "github_repos" ("org_id","github_id","name")
VALUES (1,1,'foo')
ON CONFLICT (org_id, github_id)
DO UPDATE SET "name"=EXCLUDED."name"
RETURNING "id"

`github_repos` table:

Column | Type | Collation | Nullable
-------------------+-------------------+-----------+----------+
id | bigint | | not null |
org_id | bigint | | not null |
github_id | bigint | | not null |
name | character varying | | not null |

Indexes:
"github_repos_pkey" PRIMARY KEY, btree (id)
"unique_repos" UNIQUE, btree (org_id, github_id)

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Abi Noda 2018-11-22 21:31:10 Re: Performance impact of updating target columns with unchanged values ON CONFLICT
Previous Message Justin Pryzby 2018-11-22 16:09:58 Re: dsa_allocate() faliure