Performance impact of updating target columns with unchanged values ON CONFLICT

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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)

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux