主页 / dba / 问题 / 195640
Accepted
a1ex07
Asked: 2018-01-18 15:33:44 +0800 CST

插入解释显示的成本

  • 772

我想知道为什么这两个查询的执行计划成本不同：

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 |
optimization execution-plan

1 个回答

  1. Best Answer

    我不太确定你想展示什么，在底部你有一个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 中。

    希望这可以帮助您了解正在发生的事情。

    • 1

相关问题