Re: INSERT ON CONFLICT and RETURNING

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;

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