插入說明顯示的成本

我想知道為什麼 2 個查詢的執行計劃成本有所不同：

create table test_insert(id int not null primary key, value varchar2(10));

--1 
insert into test_insert(id,value)
values (2,'111');

--2 
insert into test_insert(id,value)
select 3,'111' from dual where not exists (select null from test_insert where id =3);

查詢 2 的成本總是較高（多少取決於表中的行數）。在我的理解not exits ...中不應該增加任何成本——為了強制執行 PK 約束，引擎無論如何都必須檢查相應的唯一索引，因此子查詢會增加額外的解析時間，但不會增加執行時間或計劃的額外步驟。

該範例適用於 Oracle，但我也檢查了 Postgres，結果相似。

例如，

--1
| Id  | Operation                | Name        | Cost  |
--------------------------------------------------------
|   0 | INSERT STATEMENT         |             |     1 |
|   1 |  LOAD TABLE CONVENTIONAL | TEST_INSERT |       |
--------------------------------------------------------
--2 
| Id  | Operation                | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |              |       |       |     3 (100)|          |
|   1 |  LOAD TABLE CONVENTIONAL | TEST_INSERT  |       |       |            |          |
|*  2 |   FILTER                 |              |       |       |            |          |
|   3 |    FAST DUAL             |              |     1 |       |     2   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN     | SYS_C0012345 |     1 |    13 |     1   (0)| 00:00:01 |

我不確定您要展示什麼，在底部您有一個NOT EXISTS. 這需要要麼

• 反加入或
• 正如 PostgreSQL 和 Oracle 計劃的那樣，一個相關的index only scan

PostgreSQL計劃，

test=# explain analyze insert into test_insert(id,value)
select 3,'111' where not exists (select 1 from test_insert where id =3);
                                                                            QUERY PLAN                                                                              
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Insert on test_insert  (cost=8.17..8.19 rows=1 width=4) (actual time=0.190..0.190 rows=0 loops=1)
  ->  Subquery Scan on "*SELECT*"  (cost=8.17..8.19 rows=1 width=4) (actual time=0.011..0.012 rows=1 loops=1)
        ->  Result  (cost=8.17..8.18 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)
              One-Time Filter: (NOT $0)
              InitPlan 1 (returns $0)
                ->  Index Only Scan using test_insert_pkey on test_insert test_insert_1  (cost=0.15..8.17 rows=1 width=0) (actual time=0.006..0.006 rows=0 loops=1)
                      Index Cond: (id = 3)
                      Heap Fetches: 0
Planning time: 0.294 ms
Execution time: 0.241 ms
(10 rows)

所以當然會慢一些。它在插入之前檢查索引，然後再次檢查 PRIMARY KEY 的 btree 插入（因為它總是必須）。

如果你想把它變成一個嘗試或跳過，使用ON CONFLICT DO NOTHING

EXPLAIN ANALYZE
INSERT INT test_insert(id,value)
VALUES (3,111)
ON CONFLICT DO NOTHING;

---------------------------------------------------------------------------------------------------
Insert on test_insert  (cost=0.00..0.01 rows=1 width=0) (actual time=0.034..0.034 rows=0 loops=1)
  Conflict Resolution: NOTHING
  Tuples Inserted: 0
  Conflicting Tuples: 1
  ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)
Planning time: 0.054 ms
Execution time: 0.065 ms
(7 rows)

這是您的問題的一個範例。範例架構，

CREATE TABLE f (id PRIMARY KEY);

樣本交易，

TXN1                                TXN2

INSERT INTO f(id)
SELECT x FROM (VALUES (1))
 AS g(x)
WHERE pg_sleep(10) IS NOT NULL
AND NOT EXISTS (
 SELECT 1
 FROM f
 WHERE g.x = f.id
);

                                    INSERT INTO f(id) VALUES (1);

上面會發生什麼？

• EXISTS在預設READ COMMITTED ISOLATION級別執行的測試會獲得快照。然後它站起來睡覺。
• 第二個事務插入一個值。
• 第一個事務喚醒，並測試其快照是否存在（或在進入睡眠之前執行測試）
• 第一個事務嘗試更新實現唯一約束的 btree。
• 第一個事務拋出一個錯誤，

ERROR:  duplicate key value violates unique constraint "f_pkey"
DETAIL:  Key (id)=(1) already exists.

但是，切換這些事務的順序，第一個事務會看到密鑰在那裡，並且永遠不會嘗試插入 btree。

希望這可以幫助您了解正在發生的事情。

引用自：https://dba.stackexchange.com/questions/195640