Início / dba / Perguntas / 195640
Accepted
a1ex07
Asked: 2018-01-18 15:33:44 +0800 CST

Custo exibido por explicação para inserção

  • 772

Eu me pergunto por que há uma diferença no custo do plano de execução para as 2 consultas:

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);

O custo da consulta 2 é sempre maior (quanto depende do número de linhas na tabela). No meu entendimento not exits ..., não deve adicionar nenhuma sobrecarga - para impor a restrição de PK, o mecanismo deve verificar o índice exclusivo correspondente de qualquer maneira, portanto, a subconsulta adiciona tempo de análise extra, mas não tempo de execução ou etapa extra ao plano.

O exemplo é para Oracle, mas verifiquei o Postgres também, os resultados são semelhantes.

Por exemplo,

--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 respostas

  1. Best Answer

    Não sei exatamente o que você está tentando demonstrar, na parte inferior você tem um NOT EXISTS. Isso implica ou um

    • anti-junção ou
    • como o PostgreSQL e a Oracle planejam, umindex only scan

    plano 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)

    Então é claro que é mais lento. Ele está verificando o índice antes de inserir e, em seguida, a inserção de btree para a PRIMARY KEY está verificando novamente (como sempre deve).

    Se você quiser rolar isso em um teste ou pule, useON 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)

    Aqui está um exemplo do seu problema. Esquema de amostra,

    CREATE TABLE f (id PRIMARY KEY);

    Transações de amostra,

    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);

    O que acontece no acima?

    • Os testes que operam no nívelEXISTS padrão obtêm um instantâneo. E então ele aguenta e vai dormir.READ COMMITTED ISOLATION
    • A segunda transação insere um valor.
    • A primeira transação é ativada e testa sua existência (ou executou o teste antes de dormir)
    • A primeira transação tenta atualizar a btree que está implementando a restrição exclusiva.

    • A primeira transação gera um erro,

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

    Mude a ordem dessas transações e a primeira transação vê que a chave está lá e nunca tenta inserir na btree.

    Espero que isso ajude você a entender o que está acontecendo.

    • 1

relate perguntas