Re: INSERT ON CONFLICT and RETURNING

On 03.09.2020 19:56, Geoff Winkless wrote:
> On Mon, 31 Aug 2020 at 14:53, Konstantin Knizhnik
> <k(dot)knizhnik(at)postgrespro(dot)ru> wrote:
>> If we are doing such query:
>>
>> INSERT INTO jsonb_schemas (schema) VALUES (obj_schema)
>> ON CONFLICT (schema) DO UPDATE schema=jsonb_schemas.schema RETURNING id
>>
>>
>> Then as far as I understand no extra lookup is used to return ID:
> The conflict resolution checks the unique index on (schema) and
> decides whether or not a conflict will exist. For DO NOTHING it
> doesn't have to get the actual row from the table; however in order
> for it to return the ID it would have to go and get the existing row
> from the table. That's the "extra lookup", as you term it. The only
> difference from doing it with RETURNING id versus WITH... COALESCE()
> as you described is the simpler syntax.
Sorry, but there is no exrta lookup in this case.
By "lookup" I mean index search.
What we are doing in case ON CONFLICT SELECT is just fetching tuple from
the buffer.
So we are not even loading any data from the disk.

By in case

   with ins as (insert into jsonb_schemas (schema) values (obj_schema)
on conflict(schema) do nothing returning id)
   select coalesce((select id from ins),(select id from jsonb_schemas
where   schema=obj_schema));

we actually execute extra subquery: select id from jsonb_schemas where
schema=obj_schema:

explain 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'));
                                                   QUERY PLAN
----------------------------------------------------------------------------------------------------------------
 Result  (cost=8.21..8.21 rows=1 width=4)
   CTE ins
     ->  Insert on jsonb_schemas  (cost=0.00..0.01 rows=1 width=36)
           Conflict Resolution: NOTHING
           Conflict Arbiter Indexes: jsonb_schemas_pkey
           ->  Result  (cost=0.00..0.01 rows=1 width=36)
   InitPlan 2 (returns $2)
     ->  CTE Scan on ins  (cost=0.00..0.02 rows=1 width=4)
   InitPlan 3 (returns $3)
     ->  Index Scan using jsonb_schemas_pkey on jsonb_schemas
jsonb_schemas_1  (cost=0.15..8.17 rows=1 width=4)
           Index Cond: (schema = '\x736f6d65'::bytea)

Is it critical?
At my system average time of executing this query is 104 usec, and with
ON CONFLICT SELECT fix - 82 usec.
The difference is no so large, because we in any case insert speculative
tuple.
But it is incorrect to say that "it's not inherently any less efficient."

> I'm not saying the simpler syntax isn't nice, mind you. I was just
> pointing out that it's not inherently any less efficient.
>
> Geoff

--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company