Início / dba / Perguntas / 345413
Accepted
Hasan Can Saral
Asked: 2025-02-24 13:12:05 +0800 CST

Índice de expressão com função definida pelo usuário não usada na consulta

  • 772

Tenho a seguinte tabela:

CREATE TABLE users (
 first_name VARCHAR(256)  DEFAULT NULL NULL,
 last_name  VARCHAR(256)  DEFAULT NULL NULL,
 full_name  VARCHAR(1024) GENERATED ALWAYS AS
        (CASE
             WHEN first_name IS NULL AND
                  last_name IS NULL THEN NULL
             ELSE
                 (TRIM(COALESCE(first_name, '') || ' ' || COALESCE(last_name, ''))) END) STORED;
);

E tenho a seguinte função para "normalizar":

CREATE OR REPLACE FUNCTION NORMALIZE_FUNC(IN source VARCHAR)
    RETURNS VARCHAR
    LANGUAGE PLPGSQL
AS
$$
BEGIN

    source := (SELECT COALESCE(source, ''));

    source := (SELECT REPLACE(
                      REPLACE(
                      REPLACE(
                      REPLACE(
                      REPLACE(
                      REPLACE(
                      LOWER(source),
                      'ş', 's'),
                      'ç', 'c'),
                      'ı', 'i'),
                      'ü', 'u'),
                      'ö', 'o'),
                      'ğ', 'g'))::VARCHAR;

    RETURN source;

END
$$
IMMUTABLE;

e o seguinte índice e consulta, soundex()de onde é a fuzzystrmatchextensão:

CREATE INDEX users_full_name_soundex_idx ON users (SOUNDEX(NORMALIZE_FUNC(full_name))) WHERE full_name IS NOT NULL;

SELECT *
FROM users
WHERE SOUNDEX(NORMALIZE_FUNC(full_name)) = SOUNDEX(NORMALIZE_FUNC('name surname'))

e eu obtenho uma varredura sequencial na tabela users.

Quando tento sem o definido pelo usuário NORMALIZE_FUNC:

CREATE INDEX users_full_name_soundex_idx ON users (SOUNDEX(full_name)) WHERE full_name IS NOT NULL;

SELECT *
FROM users
WHERE SOUNDEX(full_name) = SOUNDEX('name surname')

Vejo que o users_full_name_soundex_idxíndice é usado e a consulta é rápida.

O que estou perdendo?

postgresql

1 respostas

  1. Best Answer

    O problema é a WHEREcláusula na definição do índice, que você não definiu normalize_func()com RETURNS NULL ON NULL INPUT.

    Normalmente, um índice parcial (um índice com uma WHERE condição) só pode ser usado se a WHEREcondição do índice também for usada na consulta. Então por que o índice é usado se você não usa normalize_func()na definição do índice e na consulta? A resposta é que o otimizador do PostgreSQL é inteligente o suficiente para fazer a seguinte dedução:

    • soundex('name surname'), que é avaliado 'N526'durante o planejamento da consulta, não é NULL
    • soundex()é uma função que foi definida como RETURNS NULL ON NULL INPUT(que é o mesmo que STRICT), então para todas as linhas onde full_nameé NULL, soundex(full_name)também será NULL, e a comparação não retornaráTRUE
    • como todas as linhas onde full_nameé NULL não atenderão à condição, apenas as linhas onde full_namenão é NULL podem ter sucesso, e podemos usar o índice parcial sem perder nenhuma linha

    Como você esqueceu de definir normalize_func()como RETURNS NULL ON NULL INPUT, essa dedução não pode ser feita quando essa função está envolvida.

    Você tem várias opções:

    • defina a função como RETURNS NULL ON NULL INPUT:

      ALTER FUNCTION normalize_func() RETURNS NULL ON NULL INPUT;

    • acabar com a WHEREcondição na definição do índice

    • adicionar AND full_name IS NOT NULLà consulta

    A primeira solução é a mais inteligente e também pode trazer benefícios adicionais de desempenho, já que o PostgreSQL pode pular a execução do corpo da função para argumentos NULL.

    • 2

relate perguntas