Eu tenho uma tabela com cerca de 100.000.000 entradas, em sua maioria ordenadas por data de criação (sem fusos horários e algumas entradas <0,01%, a chave primária e a ordem correta seriam idênticas).
Um caso de uso comum é selecionar as n entradas mais recentes por meio de alguma instrução where arbitrária que é lenta.
Um grande gargalo é manter a ordem, classificando por:
date=>x1, id => x10, not at all => x100
Existe uma maneira de aproveitar o fato de minha mesa estar quase ordenada?
Para otimizar consultas para selecionar as últimas n entradas, você pode considerar particionar a tabela por intervalo de datas. Isso permite que o PostgreSQL verifique apenas as partições relevantes, o que será mais rápido.
Atualização com base nas respostas
Existem alguns pontos a serem mencionados aqui:
Primeiro, é importante definir o que é “lento” no seu contexto. Por exemplo, você está considerando consultas que levam mais de X milissegundos como lentas? Identificar esse limite ajudará a focar nas questões mais críticas.
Em seguida, você pode obter consultas lentas registrando-as junto com o plano EXPLAIN correspondente. Isso ajudará você a entender o caminho de execução que o PostgreSQL está seguindo e onde podem estar os gargalos.
Para essas consultas, verifique quais condições são mais utilizadas na cláusula WHERE. Isso ajudará na definição de uma estratégia de indexação eficaz.
Com base na análise anterior, considere criar índices alinhados aos seus padrões de consulta. Um índice com id,date poderia funcionar aqui.
O particionamento pode ajudar criando tabelas e índices menores. Isso permite que o PostgreSQL aproveite a remoção de partições para verificar apenas as partições relevantes, melhorando o desempenho da consulta.