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)
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 |