pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance impact of updating target columns with unchanged values ON CONFLICT
5+ messages / 2 participants
[nested] [flat]
* Performance impact of updating target columns with unchanged values ON CONFLICT
@ 2018-11-22 19:32 Abi Noda <a@abinoda.com>
0 siblings, 1 reply; 5+ messages in thread
From: Abi Noda @ 2018-11-22 19:32 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
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)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Performance impact of updating target columns with unchanged values ON CONFLICT
@ 2018-11-22 21:31 Abi Noda <a@abinoda.com>
parent: Abi Noda <a@abinoda.com>
0 siblings, 1 reply; 5+ messages in thread
From: Abi Noda @ 2018-11-22 21:31 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
In other words, is Postgres smart enough to not actually write to disk any
columns that haven’t changed value or update indexes based on those columns?
On Thu, Nov 22, 2018 at 11:32 AM Abi Noda <a@abinoda.com> wrote:
> 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)
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Performance impact of updating target columns with unchanged values ON CONFLICT
@ 2018-11-22 22:40 Justin Pryzby <pryzby@telsasoft.com>
parent: Abi Noda <a@abinoda.com>
0 siblings, 1 reply; 5+ messages in thread
From: Justin Pryzby @ 2018-11-22 22:40 UTC (permalink / raw)
To: Abi Noda <a@abinoda.com>; +Cc: pgsql-performance@lists.postgresql.org
On Thu, Nov 22, 2018 at 01:31:10PM -0800, Abi Noda wrote:
> In other words, is Postgres smart enough to not actually write to disk any
> columns that haven’t changed value or update indexes based on those columns?
You're asking about what's referred to as Heap only tuples:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/access/heap/README.HOT;hb=H...
https://wiki.postgresql.org/wiki/Index-only_scans#Interaction_with_HOT
Note, if you're doing alot of updates, you should consider setting a lower the
table fillfactor, since HOT is only possible if the new tuple (row version) is
on the same page as the old tuple.
|With HOT, a new tuple placed on the same page and with all indexed columns the
|same as its parent row version does not get new index entries."
And check pg_stat_user_tables to verify that's working as intended.
Justin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Performance impact of updating target columns with unchanged values ON CONFLICT
@ 2018-11-24 03:44 Abi Noda <a@abinoda.com>
parent: Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 5+ messages in thread
From: Abi Noda @ 2018-11-24 03:44 UTC (permalink / raw)
To: pryzby@telsasoft.com; +Cc: pgsql-performance@lists.postgresql.org
Thanks Justin. Do you know if Postgres treats an UPDATE that sets the
indexed columns set to the same previous values as a change? Or does it
only count it as "changed" if the values are different. This is ambiguous
to me.
*> HOT solves this problem for a restricted but useful special case where a
tuple is repeatedly updated in ways that do not change its indexed columns.*
*> With HOT, a new tuple placed on the same page and with all indexed
columns the same as its parent row version does not get new index entries.*
*> [HOT] will create a new physical heap tuple when inserting, and not a
new index tuple, if and only if the update did not affect indexed columns.*
On Thu, Nov 22, 2018 at 2:40 PM Justin Pryzby <pryzby@telsasoft.com> wrote:
> On Thu, Nov 22, 2018 at 01:31:10PM -0800, Abi Noda wrote:
> > In other words, is Postgres smart enough to not actually write to disk
> any
> > columns that haven’t changed value or update indexes based on those
> columns?
>
> You're asking about what's referred to as Heap only tuples:
>
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/access/heap/README.HOT;hb=H...
> https://wiki.postgresql.org/wiki/Index-only_scans#Interaction_with_HOT
>
> Note, if you're doing alot of updates, you should consider setting a lower
> the
> table fillfactor, since HOT is only possible if the new tuple (row
> version) is
> on the same page as the old tuple.
>
> |With HOT, a new tuple placed on the same page and with all indexed
> columns the
> |same as its parent row version does not get new index entries."
>
> And check pg_stat_user_tables to verify that's working as intended.
>
> Justin
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Performance impact of updating target columns with unchanged values ON CONFLICT
@ 2018-11-24 03:53 Abi Noda <a@abinoda.com>
parent: Abi Noda <a@abinoda.com>
0 siblings, 0 replies; 5+ messages in thread
From: Abi Noda @ 2018-11-24 03:53 UTC (permalink / raw)
To: pryzby@telsasoft.com; +Cc: pgsql-performance@lists.postgresql.org
I take that question back – someone helped me on StackExchange and
addressed it:
*> It appears that Postgres is smart enough to identify cases where indexed
columns are not changed , and perform HOT updates; thus , there is no
difference between having or not having key columns in update statement
from performance point of view. The only thing that matters it whether
actual value changed. Surely, this behaviour is limited to B-Tree indexes. *
https://dba.stackexchange.com/questions/223231/performance-impact-of-updating-target-columns-with-sa...
On Fri, Nov 23, 2018 at 7:44 PM Abi Noda <a@abinoda.com> wrote:
> Thanks Justin. Do you know if Postgres treats an UPDATE that sets the
> indexed columns set to the same previous values as a change? Or does it
> only count it as "changed" if the values are different. This is ambiguous
> to me.
>
> *> HOT solves this problem for a restricted but useful special case where
> a tuple is repeatedly updated in ways that do not change its indexed
> columns.*
>
> *> With HOT, a new tuple placed on the same page and with all indexed
> columns the same as its parent row version does not get new index entries.*
>
> *> [HOT] will create a new physical heap tuple when inserting, and not a
> new index tuple, if and only if the update did not affect indexed columns.*
>
>
>
> On Thu, Nov 22, 2018 at 2:40 PM Justin Pryzby <pryzby@telsasoft.com>
> wrote:
>
>> On Thu, Nov 22, 2018 at 01:31:10PM -0800, Abi Noda wrote:
>> > In other words, is Postgres smart enough to not actually write to disk
>> any
>> > columns that haven’t changed value or update indexes based on those
>> columns?
>>
>> You're asking about what's referred to as Heap only tuples:
>>
>> https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/access/heap/README.HOT;hb=H...
>> https://wiki.postgresql.org/wiki/Index-only_scans#Interaction_with_HOT
>>
>> Note, if you're doing alot of updates, you should consider setting a
>> lower the
>> table fillfactor, since HOT is only possible if the new tuple (row
>> version) is
>> on the same page as the old tuple.
>>
>> |With HOT, a new tuple placed on the same page and with all indexed
>> columns the
>> |same as its parent row version does not get new index entries."
>>
>> And check pg_stat_user_tables to verify that's working as intended.
>>
>> Justin
>>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2018-11-24 03:53 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-11-22 19:32 Performance impact of updating target columns with unchanged values ON CONFLICT Abi Noda <a@abinoda.com>
2018-11-22 21:31 ` Abi Noda <a@abinoda.com>
2018-11-22 22:40 ` Justin Pryzby <pryzby@telsasoft.com>
2018-11-24 03:44 ` Abi Noda <a@abinoda.com>
2018-11-24 03:53 ` Abi Noda <a@abinoda.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox