On 03.09.2020 19:56, Geoff Winkless wrote:
On Mon, 31 Aug 2020 at 14:53, Konstantin Knizhnik
<k.knizh...@postgrespro.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
