Início / dba / Perguntas / 90722
Accepted
user4150760
Asked: 2015-02-01 20:34:19 +0800 CST

O índice exclusivo é melhor que a restrição exclusiva quando um índice com uma classe de operador é necessário

  • 772

Os documentos do Postgres dizem:

O uso de índices para impor restrições exclusivas pode ser considerado um detalhe de implementação que não deve ser acessado diretamente. Deve-se, no entanto, estar ciente de que não há necessidade de criar índices manualmente em colunas únicas; fazer isso apenas duplicaria o índice criado automaticamente.

Com base nisso, se eu quiser um índice de expressão em uma coluna e também quiser que essa coluna seja única, case 2abaixo seria melhor, pois pode realizar o acima com um único índice. Considerando case 1que teria um índice criado automaticamente por causa de uma restrição única e outro porque preciso de um índice em minúsculas?

Como @Colin'tHart apontou, esses 2 casos não são os mesmos. Eu deveria ter postado esta pergunta sem o uso de lower()expressão. Nesse caso, entendo que a CREATE UNIQUE INDEXseria melhor do que uma restrição única e um índice simples.

Com base nisso, se eu quiser um índice com uma classe de operador (por exemplo text_pattern_ops, ) em uma coluna e também quiser que essa coluna seja única, case 2abaixo seria melhor, pois pode realizar o acima com um único índice. Considerando case 1que teria um índice criado automaticamente por causa de uma restrição única e outro porque preciso de uma classe de operador diferente?

Caso 1:

CREATE TABLE book (
   id SERIAL PRIMARY KEY,
   name text NOT NULL,
   CONSTRAINT book_name_key UNIQUE (name)
);

CREATE INDEX book_name_like ON book (name text_pattern_ops);

Caso 2:

CREATE TABLE book (
   id SERIAL PRIMARY KEY,
   name text NOT NULL
);

CREATE UNIQUE INDEX book_name_like ON book (name text_pattern_ops);
database-design postgresql

2 respostas

  1. Best Answer

    Por um momento, pensei que alguém poderia usar um text_pattern_opsíndice pré-existente com a USING INDEXcláusula ao adicionar um arquivo UNIQUE CONSTRAINT. Mas isso falha, porque:

    ERROR: index "book2_name_like" does not have default sorting behavior

    O manual:

    O índice não pode ter colunas de expressão nem ser um índice parcial. Além disso, deve ser um índice de árvore b com ordenação de classificação padrão . Essas restrições garantem que o índice seja equivalente a um que seria construído por um comando regular ADD PRIMARY KEYou .ADD UNIQUE

    Por exemplo, um índice exclusivo como esse permitiria uma restrição FK referenciando-o, mas teria um desempenho terrível , porque não oferece suporte a operadores padrão.
    O manual:

    Observe que você também deve criar um índice com a classe de operador padrão se desejar que consultas envolvendo , , ou comparações comuns <usem <=um >índice >=.

    Então, para responder à pergunta:

    Se você precisar de um UNIQUE CONSTRAINT(entre outros motivos: para fazer referência a um FK), sua primeira variante com restrição e índice é a única opção. Além disso, a classe de operador padrão do índice criado pela restrição oferece suporte a mais operações (como classificação na ordem de classificação padrão).

    Se você não precisa de nada disso, vá com sua segunda variante porque, obviamente, apenas um índice é mais barato de manter: apenas um UNIQUE text_pattern_opsíndice.

    Diferenças entre índice e restrição:

    Alternativa com COLLATE "C"

    Em vez de criar dois índices, há outra alternativa para índices que pode ser preferível. O manual:xxx_pattern_ops

    A diferença das classes de operador padrão é que os valores são comparados estritamente caractere por caractere, em vez de de acordo com as regras de agrupamento específicas do local. Isso torna essas classes de operador adequadas para uso por consultas que envolvem expressões de correspondência de padrão ( LIKEou expressões regulares POSIX) quando o banco de dados não usa a localidade "C" padrão.

    E:

    O índice usa automaticamente o agrupamento da coluna subjacente.

    Você pode criar a coluna sem collation (usando COLLATE "C"). Em seguida, a classe de operador padrão se comporta da mesma maneira text_pattern_ops- além disso, o índice pode ser usado com todos os operadores padrão.

    CREATE TABLE book2 (
   book_id serial PRIMARY KEY,
   book    text NOT NULL COLLATE "C" UNIQUE  -- that's all!
);

    Agora, LIKEpode usar o índice:

    SELECT * FROM book2 WHERE book LIKE 'foo%';

    Mas ILIKEainda não pode:

    SELECT * FROM book2 WHERE book ILIKE 'foo%';

    db<>fiddle aqui Old sqlfddle

    Considere um índice de trigrama usando o módulo adicional pg_trgm para uma solução mais geral:

    • 6

  2. Tome cuidado.

    Seus dois projetos não são os mesmos.

    O caso 1 implementa uma restrição única no nome do livro, mas inclui maiúsculas e minúsculas, portanto, "O senhor das moscas" e "o senhor das moscas" seriam diferentes. O caso 1 então cria um segundo índice para dar suporte à pesquisa eficiente de consultas do formulário

    select *
from book
where lower(name) like 'the lord%';

    O caso 2 é diferente. Ele força os títulos de seus livros a serem diferentes, independentemente de maiúsculas e minúsculas, e não permite que ambos os títulos acima sejam inseridos.

    • 3

relate perguntas