Performance impact of updating target columns with unchanged valuesON CONFLICT

Поиск
Список
Период
Сортировка
От Abi Noda
Тема Performance impact of updating target columns with unchanged valuesON CONFLICT
Дата
Msg-id CAM37AMMs_FLcg-ZGm2JSK7PD_+f5unNa-wipGH4dLiprdhgaTw@mail.gmail.com
обсуждение исходный текст
Ответы Re: Performance impact of updating target columns with unchangedvalues ON CONFLICT  (Abi Noda <a@abinoda.com>)
Список 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)

В списке pgsql-performance по дате отправления:

Предыдущее
От: Justin Pryzby
Дата:
Сообщение: Re: dsa_allocate() faliure
Следующее
От: Abi Noda
Дата:
Сообщение: Re: Performance impact of updating target columns with unchangedvalues ON CONFLICT