Re: INSERT ON CONFLICT and RETURNING
От | Konstantin Knizhnik |
---|---|
Тема | Re: INSERT ON CONFLICT and RETURNING |
Дата | |
Msg-id | d0b44854-5e5a-da7a-ecce-552094b246fa@postgrespro.ru обсуждение исходный текст |
Ответ на | Re: INSERT ON CONFLICT and RETURNING (Geoff Winkless <pgsqladmin@geoff.dj>) |
Ответы |
Re: INSERT ON CONFLICT and RETURNING
|
Список | pgsql-hackers |
On 03.09.2020 19:56, Geoff Winkless wrote: > On Mon, 31 Aug 2020 at 14:53, Konstantin Knizhnik > <k.knizhnik@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
В списке pgsql-hackers по дате отправления: