Início / dba / Perguntas / 252173
Accepted
gavenkoa
Asked: 2019-10-30 14:07:37 +0800 CST

Um índice parcial com WHERE NOT NULL deve ser usado se as consultas usarem uma instrução preparada por JDBC?

  • 772

Suponha que criamos um índice parcial no Postgres que evita dados NULL inúteis:

 CREATE INDEX my_ix ON my (col1) WHERE col1 IS NOT NULL;

Devo escrever a consulta SELECT como:

SELECT * FROM my WHERE col1 = 'abc';

ou como:

SELECT * FROM my WHERE col1 = 'abc' AND col1 IS NOT NULL;

No caso de bibliotecas de conexão JDBC, pode ser assim:

SELECT * FROM my WHERE col1 = ?;

e não tenho certeza se o Postgres pode inferir a aplicabilidade do índice sem:

 AND col1 IS NOT NULL

Também é interessante se o Postgres puder inferir transitividade, para índice:

 CREATE INDEX my_ix ON my (col1) WHERE col1 > 0;

Espero não precisar escrever:

SELECT * FROM my WHERE col1 > ? AND col1 > 0;

se os parâmetros forem 10, 23, etc (> 0).

postgresql index

1 respostas

  1. Best Answer

    Você pode facilmente testá-lo com o seguinte script:

    CREATE TABLE my (col1) AS
   SELECT * FROM generate_series(1, 100000);

ANALYZE my;

CREATE INDEX my_null ON my (col1) WHERE col1 IS NOT NULL;

/* use a prepared statement similar to JDBC */
PREPARE stmt1(integer) AS SELECT * FROM my WHERE col1 = $1;

/* the first five executions use custom plans */
EXPLAIN (COSTS off) EXECUTE stmt1(1);
EXPLAIN (COSTS off) EXECUTE stmt1(2);
EXPLAIN (COSTS off) EXECUTE stmt1(NULL);
EXPLAIN (COSTS off) EXECUTE stmt1(4);
EXPLAIN (COSTS off) EXECUTE stmt1(5);
/* from the sixth execution on, a generic plan is used */
EXPLAIN (COSTS off) EXECUTE stmt1(6);
EXPLAIN (COSTS off) EXECUTE stmt1(NULL);

DROP INDEX my_null;

CREATE INDEX my_pos ON my (col1) WHERE col1 > 0;

/* use a prepared statement similar to JDBC */
PREPARE stmt2(integer) AS SELECT * FROM my WHERE col1 > $1;

/* the first five executions use custom plans */
EXPLAIN (COSTS off) EXECUTE stmt2(99997);
EXPLAIN (COSTS off) EXECUTE stmt2(99997);
EXPLAIN (COSTS off) EXECUTE stmt2(1);
EXPLAIN (COSTS off) EXECUTE stmt2(NULL);
EXPLAIN (COSTS off) EXECUTE stmt2(99998);
/* PostgreSQL continues to use a custom plan! */
EXPLAIN (COSTS off) EXECUTE stmt2(0);
EXPLAIN (COSTS off) EXECUTE stmt2(NULL);
EXPLAIN (COSTS off) EXECUTE stmt2(99997);

DROP TABLE my;
DEALLOCATE ALL;

    Aqui está um violino SQL que ajusta o código.

    Explicações:

    Com uma instrução preparada, o PostgreSQL pode armazenar em cache os planos de consulta para a sessão do banco de dados. Usar um plano tão genérico que permanece o mesmo, independentemente dos valores dos parâmetros, tem a vantagem de economizar tempo de planejamento.

    Mas o PostgreSQL nem sempre usa um plano genérico, porque às vezes pode ser melhor gerar um plano personalizado que respeite o valor do parâmetro.

    Para decidir o que fazer, o PostgreSQL usa a seguinte heurística:

    • As primeiras cinco execuções sempre usarão um plano personalizado.

    • Se o custo estimado dos planos personalizados não for mais barato que o custo estimado do plano genérico, o PostgreSQL utilizará o plano genérico a partir da sexta execução.

    A partir do PostgreSQL v12, você pode configurar o comportamento com o plan_cache_modeparâmetro de configuração.

    Por que a primeira declaração usa um plano genérico?

    Para a primeira instrução, uma varredura de índice sempre pode ser usada, mesmo que o argumento seja NULL(porque nada precisa ser feito nesse caso) . Assim, o PostgreSQL usará o plano genérico após a quinta execução.

    Você pode reconhecer o plano genérico $1na EXPLAINsaída.

    Por que a segunda instrução continua usando um plano personalizado?

    Com um plano genérico, o PostgreSQL só pode usar uma varredura sequencial, porque o índice só pode ser usado para alguns valores de parâmetros. Para algumas das primeiras cinco execuções, o plano personalizado é significativamente mais barato que o plano genérico, portanto, o PostgreSQL continua usando planos personalizados.

    Para responder sua pergunta:

    Se for necessário adicionar uma WHEREcondição extra que garanta que o PostgreSQL saiba que pode usar um índice depende do caso individual.

    Você pode adicionar a condição extra sem uma desvantagem, pode ajudar o otimizador e não pode causar danos.

    Mas mesmo com a condição extra, você não pode ter certeza de que o índice será usado.

    • 4

relate perguntas