Melhor ordem de classificação no índice da árvore B para dar suporte à consulta em linhas recentes?

_{Não gosto do nome "create_date" para uma coluna que não é realmente uma, datemas uma timestamptz. Usando "created_at" em vez disso.}

Como created_atpode ser NULL, esta 3ª variante será mais rápida (mesmo que não muito):

CREATE INDEX index_c ON my_table (created_at DESC NULLS LAST);

NULLvalores são classificados após o maior valor por padrão. DESCENDINGA ordem de classificação é a inversão perfeita, portanto, os NULLvalores vêm primeiro. Ver:

• Classificar por coluna ASC, mas valores NULL primeiro?

O Postgres pode escanear índices de árvore B para trás quase na mesma velocidade, então ambas as suas variantes estão quase no mesmo nível. Mas o operador >=exclui NULLvalores (como a maioria dos operadores). Portanto, o Postgres precisa pular os NULLvalores iniciais / finais, respectivamente, primeiro. Normalmente não é caro, mas ainda assim.

O índice com DESC NULLS LAST(ou NULLS FIRST) tem os maiores valores primeiro e os NULLvalores por último (ou vice-versa), então a consulta pode começar a ler diretamente do topo (abaixo) do índice.

Se não pode haver NULLvalores, não haverá diferença perceptível. E você deve declarar a coluna NOT NULL. (E você deveria ter dito isso.)

Se as inserções vierem com timestamps estritamente ascendentes (e não houver atualizações!) - ou se isso for verdade para linhas inseridas recentemente desde "ontem", as linhas (relevantes) são fisicamente agrupadas por timestamp automaticamente. Caso contrário, pode valer a pena agrupar fisicamente as linhas de tempos em tempos. (Sem interferir no carregamento simultâneo no banco de dados!) Isso pode fazer uma diferença maior, pois mantém o número de páginas de dados que precisam ser lidas no mínimo. Ver:

• Configurando o PostgreSQL para desempenho de leitura

Se sua mesa for grande, um índice parcial pode pagar:

CREATE INDEX index_c_partial ON my_table (created_at DESC NULLS LAST)
WHERE  created_at >= '2021-06-26 0:0';  -- recent but before yesterday

Ele corta a maioria das linhas antigas, para que o índice diminua para uma fração de tamanho.

Mas como seu cut-off ( 'yesterday') é um alvo móvel, você terá que recriar esse índice de tempos em tempos para remover tuplas antigas, ou o benefício se deteriorará com o tempo. Tipo, diariamente, semanalmente, mensalmente - você decide.

Com o cache quente, esse índice parcial não será muito mais rápido que o índice completo, mas como é muito menor, suas chances de permanecer no cache são maiores (depende da sua configuração completa), o que normalmente faz uma grande diferença. (E não ocupa tantos recursos para começar.)

Como temos um índice tão pequeno agora, e enquanto lidamos com poucas colunas (ou você não precisa realmente SELECT *começar?!), podemos também torná-lo um índice de cobertura (Postgres 11 ou posterior):

CREATE INDEX index_c_partial_covering ON my_table
   (created_at DESC NULLS LAST) INCLUDE (id, data)
WHERE  created_at >= '2021-06-26 0:0';

Novamente, os detalhes dependem da situação completa. Relacionado:

• A cobertura de índices no PostgreSQL ajuda as colunas JOIN?

Se algumas pré-condições forem atendidas, você obterá varreduras somente de índice mais baratas agora. A ordem física das linhas na tabela não importa neste caso.

Ah, e mova essa timestamptzcoluna para uma posição diferente na definição da tabela. A maneira como você o tem agora maximiza o inchaço devido ao preenchimento de alinhamento. Qualquer outra posição para a timestamptzcoluna é melhor. Curti:

CREATE TABLE my_table (
, created_at timestamptz DEFAULT now() NOT NULL
, id serial NOT NULL PRIMARY KEY
, data text
);

Ver:

• Calculando e economizando espaço no PostgreSQL