Início / dba / Perguntas / 59074
Accepted
Saurabh Nanda
Asked: 2014-02-18 17:32:02 +0800 CST

2 índices de árvore B OU 1 índice GiST em tsrange -- qual terá melhor desempenho?

  • 772

Eu tenho uma tabela que está armazenando dados de reserva usando as colunas starts_at& ends_atSempre que estou consultando a tabela para encontrar reservas sobrepostas, tenho a opção de usar uma das seguintes consultas:

SELECT * FROM reservations
WHERE starts_at < '2014-01-03 00:00:00'
AND   ends_at   >='2014-01-01 00:00:00';

Ou

SELECT * FROM reservations
WHERE tsrange(starts_at, ends_at) && ('2014-01-01 00:00:00', '2014-01-03 00:00:00')

Eu tenho índices B-Tree regulares nas colunas starts_ate , portanto, a primeira consulta sempre os usa. ends_atNo entanto, a menos que eu defina um índice GiST funcional no tsrange, a segunda consulta faz uma varredura completa.

create index tsrange_idx on reservations using gist(tsrange(starts_at, ends_at));

Minha dúvida é, conforme a tabela cresce, qual índice vai ser mais rápido? Provavelmente, a resposta é óbvia olhando para o plano de execução da consulta, mas não sou muito versado na leitura da EXPLAIN ANALYZEsaída.

postgresql index

1 respostas

  1. Best Answer

    Timestamps com índice de árvore B

    Sugiro uma terceira opção : contanto que sua tabela contenha duas timestampcolunas (que parecem estar definidas NOT NULL), eu usaria um único índice de árvore B de várias colunas com ordem de classificação oposta (se nenhuma outra consideração se aplicar):

    CREATE INDEX reservations_range_idx ON reservations (starts_at, ends_at DESC);

    Mais nesta resposta relacionada:

    Quanto à consulta, dê uma olhada no operador padrão SQLOVERLAPS :

    SELECT * FROM reservations
WHERE (starts_at, ends_at) OVERLAPS ('2014-01-01 00:00:00', '2014-01-03 00:00:00');

    Mais nesta questão relacionada no SO:

    Deve ser mais rápido que dois índices de árvore B. Menos espaço em disco e manutenção mais barata. A carga nas operações de gravação é pequena.

    Tipo de intervalo com índice GiST

    Com tabelas grandes, um índice GiST em um tipo de intervalo provavelmente é mais rápido, porque é dimensionado melhor. O armazenamento em disco é consideravelmente maior e a manutenção do índice um pouco mais cara.

    Se você seguir esse caminho, seria mais eficiente armazenar seus timestamps como intervalo ( tsrangeoutstzrange ) para começar. Um índice GiST simples sem o aspecto funcional é um pouco mais rápido.

    CREATE TABLE reservation (
  reservation_id serial PRIMARY KEY
, span tsrange
, ...
);
 
CREATE INDEX reservation_span_gist_idx on reservations USING GiST (span);

    Com o &&operador "overlap" que você já exibiu na pergunta:

    SELECT *
FROM   reservation 
WHERE  span && ('2014-01-01 00:00:00', '2014-01-03 00:00:00');

    Você pode estar interessado em uma restrição de exclusão para descartar sobreposições por design, que implementa um índice GiST como o acima automaticamente. Há um exemplo de código no manual . Esta resposta relacionada no SO tem mais detalhes:

    • 18

relate perguntas