Início / dba / Perguntas / 86682
Accepted
user4150760
Asked: 2014-12-21 10:24:24 +0800 CST

Desempenho de restrição exclusiva de coluna única versus múltipla

  • 772

Estou usando o PostgreSQL 9.3. Quero entender se tenho a opção de tornar uma restrição exclusiva em toda a tabela versus exclusiva em um subconjunto da tabela (ou seja, usando 2 colunas na restrição exclusiva, restrinjo a exclusividade), qual é o melhor para pesquisas ?

Considere esta tabela onde um código alfanumérico único é atribuído a cada aluno da turma.

CREATE TABLE sc_table (
   name text NOT NULL,
   code text NOT NULL,
   class_id integer NOT NULL,
   CONSTRAINT class_fk FOREIGN KEY (class_id) REFERENCES class (id),
   CONSTRAINT sc_uniq UNIQUE (code)
);

Atualmente, o codeé exclusivo em toda a tabela. No entanto, a especificação diz que é suficiente que o código seja único entre os classúnicos. Para meus requisitos de design, não há restrição de qualquer maneira.

No entanto, se eu alterar a restrição para ser exclusiva apenas para uma determinada classe, como isso afetaria a pesquisa por código?

Ou, em outras palavras, qual das seguintes combinações de restrição e pesquisa é a melhor em termos de velocidade:

-- 1. unique across entire table, lookup by value
CONSTRAINT sc_uniq UNIQUE (code)       
SELECT * FROM sc_table WHERE code='alpha-2-beta'

-- 2. unique across entire table, lookup by value & class
CONSTRAINT sc_uniq UNIQUE (code)       
SELECT * FROM sc_table WHERE class_id=1 AND code='alpha-2-beta' 

-- 3. unique per class, lookup by value
CONSTRAINT sc_uniq UNIQUE (code, class_id)       
SELECT * FROM sc_table WHERE code='alpha-2-beta'

-- 4. unique per class, lookup by value & class
CONSTRAINT sc_uniq UNIQUE (code, class_id)       
SELECT * FROM sc_table WHERE class_id=1 AND code='alpha-2-beta'

Pergunta : Pelo que entendi, 2 é melhor que 1 e 4 é melhor que 3. Mas qual é melhor entre 1 contra 3 e 2 contra 4?

Atualização : Adicionando saída de explain analyze. 3é ruim porque não há índice para a pesquisa. 2parece ser o melhor, mas a tabela é muito pequena para concluir isso.

-- 1
"Index Scan using sc_uniq on sc_table  (cost=0.15..8.17 rows=1 width=72) (actual time=0.041..0.044 rows=1 loops=1)"
"  Index Cond: (code = 'code1'::text)"
"Total runtime: 0.096 ms"

-- 2
"Index Scan using sc_uniq on sc_table  (cost=0.15..8.17 rows=1 width=72) (actual time=0.024..0.026 rows=1 loops=1)"
"  Index Cond: (code = 'code1'::text)"
"  Filter: (class_id = 1)"
"Total runtime: 0.056 ms"

-- 3
"Bitmap Heap Scan on sc_table2  (cost=4.18..12.64 rows=4 width=72) (actual time=0.052..0.053 rows=1 loops=1)"
"  Recheck Cond: (code = 'code1'::text)"
"  ->  Bitmap Index Scan on sc_uniq2  (cost=0.00..4.18 rows=4 width=0) (actual time=0.039..0.039 rows=1 loops=1)"
"        Index Cond: (code = 'code1'::text)"
"Total runtime: 0.121 ms"

-- 4
"Index Scan using sc_uniq2 on sc_table2  (cost=0.15..8.17 rows=1 width=72) (actual time=0.036..0.039 rows=1 loops=1)"
"  Index Cond: ((code = 'code1'::text) AND (class_id = 1))"
"Total runtime: 0.093 ms"
postgresql postgresql-9.3

2 respostas

  1. Best Answer

    Suas combinações em ordem de desempenho típico:

    1. > 2. > 4. ( > 3.)

    3.é inválido . Se as linhas forem exclusivas apenas por (code, class_id), a pesquisa por codesi só pode retornar várias linhas e é diferente das demais.

    2.é inútil . Se codefor único, não há sentido em adicionar outro predicado class_id- exceto para verificar se um dado coderealmente pertence a um dado class_id(e não obter nenhuma linha caso contrário).

    Apenas 1.e 4.faz sentido e eu iria com 1., é claro. A menos que você tenha requisitos adicionais para os valores de code, é muito mais eficiente ter uma única coluna. Você também pode torná-lo o PK. As consultas são mais simples (um predicado em vez de dois), o índice exclusivo (criado automaticamente) é potencialmente menor (o fator mais importante aqui!), a pesquisa é tipicamente um pouco mais rápida .

    UPDATEs também são potencialmente mais caros para 2., onde mais colunas acionam atualizações de índice. Uma UPDATEmudança só code_idé mais barato para 1..

    O resultado do seu teste para 1.é contra-intuitivo, talvez um artefato de sua configuração específica. Talvez você não tenha pré-aquecido o cache? Ou algum outro fator aleatório. É bastante óbvio a partir da EXPLAINsaída: a única diferença entre 1. e 2. é a Filter: (class_id = 1)etapa adicional. Nada a ganhar aqui, você só pode perder (mesmo que muito pouco neste caso). 2.é tipicamente um pouco mais lento que 1.E 4.também é tipicamente um pouco mais lento que1.

    • 1

  2. Como você está no Postgres 9.3, está deixando de fazer uma das perguntas mais importantes, quais colunas você realmente precisa que a consulta retorne? Você realmente precisa *? Caso contrário, você pode considerar a criação de um índice que possa executar uma varredura somente de índice e evitar visitar a tabela. Esse seria o principal motivo no seu caso para usar uma chave composta. Por exemplo, se você quiser pesquisar nome por código, tente este índice e consulta:

    CREATE UNIQUE INDEX sc_lookup ON sc_table (code, name);
SELECT name FROM sc_table WHERE code = 'alpha-2-beta';

    Isso normalmente daria a você uma varredura somente de índice. Há mais fatores a serem considerados, como Erwin aponta bem, mas o potencial para uma varredura somente de índice pode ser um dos benefícios de desempenho mais significativos se o seu cenário de pesquisa se adequar.

    • 1

relate perguntas