Re: INSERT ON CONFLICT and RETURNING

út 8. 9. 2020 v 11:06 odesílatel Konstantin Knizhnik <
k(dot)knizhnik(at)postgrespro(dot)ru> napsal:

> I have performed comparison of different ways of implementing UPSERT in
> Postgres.
> May be it will be interesting not only for me, so I share my results:
>
> So first of all initialization step:
>
> create table jsonb_schemas(id serial, schema bytea primary key);
> create unique index on jsonb_schemas(id);
> insert into jsonb_schemas (schema) values ('some') on conflict(schema)
> do nothing returning id;
>
> Then I test performance of getting ID of exitsed schema:
>
> 1. Use plpgsql script to avoid unneeded database modifications:
>
> create function upsert(obj_schema bytea) returns integer as $$
> declare
> obj_id integer;
> begin
> select id from jsonb_schemas where schema=obj_schema into obj_id;
> if obj_id is null then
> insert into jsonb_schemas (schema) values (obj_schema) on
> conflict(schema) do nothing returning id into obj_id;
> if obj_id is null then
> select id from jsonb_schemas where schema=obj_schema into obj_id;
> end if;
> end if;
> return obj_id;
> end;
> $$ language plpgsql;
>

In parallel execution the plpgsql variant can fail. The possible raise
conditions are not handled.

So maybe this is the reason why this is really fast.

Regards

Pavel

>
> ------------------------
> upsert-plpgsql.sql:
> select upsert('some');
> ------------------------
> pgbench -n -T 100 -M prepared -f upsert-plpgsql.sql postgres
> tps = 45092.241350
>
> 2. Use ON CONFLICT DO UPDATE:
>
> upsert-update.sql:
> insert into jsonb_schemas (schema) values ('some') on conflict(schema) do
> update set schema='some' returning id;
> ------------------------
> pgbench -n -T 100 -M prepared -f upsert-update.sql postgres
> tps = 9222.344890
>
>
> 3. Use ON CONFLICT DO NOTHING + COALESCE:
>
> upsert-coalecsce.sql:
> with ins as (insert into jsonb_schemas (schema) values ('some') on
> conflict(schema) do nothing returning id) select coalesce((select id from
> ins),(select id from jsonb_schemas where schema='some'));
> ------------------------
> pgbench -n -T 100 -M prepared -f upsert-coalesce.sql postgres
> tps = 28929.353732
>
>
> 4. Use ON CONFLICT DO SELECT
>
> upsert-select.sql:
> insert into jsonb_schemas (schema) values ('some') on conflict(schema) do
> select returning id;
> ------------------------
> pgbench -n -T 100 -M prepared -f upsert-select.sql postgres
> ps = 35788.362302
>
>
>
> So, as you can see PLpgSQL version, which doesn't modify database if key
> is found is signficantly faster than others.
> And version which always do update is almost five times slower!
> Proposed version of upsert with ON CONFLICT DO SELECT is slower than
> PLpgSQL version (because it has to insert speculative tuple),
> but faster than "user-unfriendly" version with COALESCE:
>
> Upsert implementation
> TPS
> PLpgSQL
> 45092
> ON CONFLICT DO UPDATE 9222
> ON CONFLICT DO NOTHING 28929
> ON CONFLICT DO SELECT 35788
>
> Slightly modified version of my ON CONFLICT DO SELECT patch is attached to
> this mail.
>
> --
>
> Konstantin Knizhnik
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
>