Início / dba / Perguntas / 340021
Accepted
birgersp
Asked: 2024-06-05 19:48:03 +0800 CST

Um índice de tempo deve estar em ordem crescente ou decrescente?

  • 772

Planejando algumas tabelas novas, estou tentando decidir se um índice deve ser "crescente" ou "descendente".

A tabela será bem grande (imagino aproximadamente 2.000 inserções por minuto, migrando inicialmente de uma tabela diferente com cerca de 1 bilhão de linhas).

Usarei a extensão timescaledb para isso (para particionamento por tempo).

É assim que a tabela pode ser criada:

create table "Sample"(
    "id" bigserial,
    "deviceId" int not null,
    "timestamp" timestamptz not null,
    "value" float8 not null
);
select create_hypertable('"Sample"', 'timestamp'); -- creates a desc index on "timestamp"

create index on "Sample"("deviceId", "timestamp"); -- should this be "desc"?

Estas são as duas consultas mais comuns que executaremos (deviceId e timestamps podem variar, é claro):

select "timestamp", "value"
from "Sample" where "deviceId"=123 and "timestamp"<'2024-01-01Z'
order by "timestamp" desc limit 1;

E

select "timestamp", "value"
from "Sample" where "deviceId"=123 and "timestamp" between '2024-01-01Z' and '2024-02-01Z'
order by "timestamp" asc;

Então, o que estou tentando entender é em que ordem o "carimbo de data e hora" deve estar? E porque?

Minha intuição (provavelmente errada) me diz que o índice com "timestamp" deveria estar em ordem crescente, pois preciso ordenar os dados por timestamp em ordem crescente .

Porém, os exemplos na documentação da escala de tempo sempre indexam as colunas de tempo em ordem decrescente. Eu não entendo muito bem o porquê.

Qual é a escolha ideal de índices aqui?

postgresql

1 respostas

  1. Best Answer

    Definitivamente, você deve criar o índice em ordem crescente, a menos que insira as linhas em ordem decrescente.

    Para uma varredura de índice, não faz diferença se ela é varrida para frente ou para trás; portanto, para suas consultas, a ordem não importa.

    Mas os índices de árvore B do PostgreSQL são otimizados para inserir valores na extremidade superior, enquanto não existe tal otimização para a extremidade inferior. Portanto, se você estiver inserindo dados de série temporal onde os carimbos de data e hora geralmente continuam aumentando, um índice ascendente é melhor.

    Além dessa consideração, o único motivo para criar um índice em ordem decrescente é se você precisar oferecer suporte a uma ORDER BYcondição com ordem de coluna mista, como ORDER BY a DESC, b.

    • 1

relate perguntas