Técnicas não convencionais de otimização no PostgreSQL

• Métodos de otimização fora do padrão para aumentar o desempenho de consultas no PostgreSQL, apresentando 3 abordagens criativas em vez de apenas adicionar índices ou reescrever queries
• Eliminação de varreduras completas da tabela com base em restrições CHECK, otimização de baixa cardinalidade com índices baseados em função e implementação de restrições UNIQUE com índices Hash
• 1. Uso da configuração constraint_exclusion para evitar varreduras desnecessárias em queries com condições incorretas
• 2. Uso de índices baseados em função e colunas geradas virtuais (virtual generated columns) para reduzir o tamanho do índice e garantir consistência nas queries
• 3. Combinação de índices Hash com restrições de exclusão (exclusion constraint) para implementar de forma eficiente restrições UNIQUE em colunas de texto grandes, reduzindo bastante o espaço de armazenamento

---

Eliminação de varredura completa da tabela com base em restrições CHECK

• Mesmo havendo uma restrição CHECK que permite apenas os valores 'free' e 'pro' na coluna plan, se uma query incorreta for executada com 'Pro', o PostgreSQL fará uma varredura completa da tabela
  • No plano de execução, todas as 100 mil linhas são lidas, embora o resultado real seja 0 linhas
• Ao definir o parâmetro constraint_exclusion como 'on', o PostgreSQL considera a restrição e omite completamente a varredura
  • O tempo de execução cai de 7.4ms para 0.008ms
• O valor padrão é 'partition', e em queries simples o overhead de planejamento pode até aumentar
  • Porém, em ambientes de BI e relatórios, usuários frequentemente inserem condições incorretas, então a configuração 'on' pode ser útil

Otimização de baixa cardinalidade com índices baseados em função

• Ao executar uma query de agregação de receita diária na tabela sale, com 10 milhões de registros de vendas, uma varredura completa leva 627ms
• Ao adicionar um índice B-Tree na coluna sold_at, o tempo cai para 187ms, mas o tamanho do índice cresce para 214MB
• Se for criado um índice baseado em função sobre a expressão date_trunc('day', sold_at), o tamanho cai para 66MB e o tempo de execução melhora ainda mais, para 145ms
  • Graças à baixa cardinalidade, a deduplicação de índice é possível
• No entanto, como a expressão da query precisa corresponder exatamente à definição do índice, é necessário manter consistência na expressão
  • Para isso, pode-se criar uma VIEW contendo a mesma expressão, ou
  • adicionar uma coluna gerada virtual (virtual generated column), suportada a partir do PostgreSQL 18, para automatizar essa consistência
• Com uma coluna gerada virtual, o índice é usado automaticamente, tornando possível obter ao mesmo tempo índice menor, query mais rápida e consistência de expressão
• Porém, no PostgreSQL 18, a criação de índice sobre coluna virtual ainda não é suportada, com suporte previsto para a versão 19

Implementação de restrição UNIQUE com índice Hash

• Na tabela urls, que armazena URLs longas, ao criar um índice UNIQUE baseado em B-Tree para evitar URLs duplicadas, o tamanho do índice chega a 154MB
• Um índice Hash é muito menor porque armazena apenas o valor de hash, e não o valor real
  • O PostgreSQL não oferece suporte nativo a índice Hash UNIQUE, mas
  • usando uma restrição de exclusão (exclusion constraint), é possível implementar de forma alternativa uma restrição UNIQUE no formato EXCLUDE USING HASH (url WITH =)
• Mesmo com esse método, uma inserção duplicada gera erro, e o desempenho da query também é melhor que com B-Tree (0.022ms vs 0.046ms)
• O tamanho do índice fica em 32MB, ou seja, mais de 5 vezes menor que o B-Tree
• Desvantagens:
  • não pode ser referenciado por chave estrangeira (restrição REFERENCES não é possível)
  • compatibilidade limitada com a sintaxe INSERT ... ON CONFLICT
  • pode ser substituído por ON CONFLICT ON CONSTRAINT ou pela sintaxe MERGE
• O índice Hash é adequado para garantir unicidade em colunas de texto grandes e, quando não há necessidade de chave estrangeira, é uma alternativa eficiente em espaço

[GN⁺]

Comentários do Hacker News

• O índice tem 214 MB, cerca de metade do tamanho da tabela inteira
  Do ponto de vista de análise isso é bom, mas em termos de desempenho de escrita isso causa problema de write amplification
  Os índices são projetados de forma diferente dependendo da proporção entre leitura e escrita, e é por isso que se usam data warehouses ou réplicas de leitura
  Se você atende usuários demais, é melhor não colocar índices de BI/OLAP no banco OLTP

  • Acho que seria bom se o PostgreSQL suportasse clustered index (o Index Organized Table do Oracle)
    Se o padrão de acesso à tabela for consistente, a própria tabela pode virar o índice e trazer eficiência sem write amplification

• No primeiro exemplo, acho melhor definir Plan como um tipo enum
  É mais leve que texto e, quando um filtro inválido é inserido, trata como erro em vez de retornar resultado vazio, o que é mais seguro

• Foi um excelente texto. Uso PostgreSQL e MySQL há décadas, mas mesmo vendo este texto senti que ainda conhecia só uma parte das possibilidades

  • Também uso Postgres há mais de 10 anos, mas toda vez que vejo a documentação ainda tenho a sensação de estar só arranhando a superfície. É um sistema realmente poderoso
  • PostgreSQL é como o Emacs. Parece simples por fora, mas na prática tem a flexibilidade de um sistema operacional

• O comando MERGE mencionado no fim do texto foi o que achei mais interessante
  Normalmente faço upsert com INSERT ... ON CONFLICT DO UPDATE, mas o MERGE parece mais poderoso e útil em situações mais variadas

  • MERGE existe no padrão SQL há muito tempo, mas o Postgres adiou a adoção por causa do problema de não atomicidade no modelo MVCC
    Isso também é explicado neste post do blog da pganalyze
    Pessoalmente, prefiro INSERT ... ON CONFLICT e só uso MERGE quando realmente preciso, tratando o tratamento de erros com cuidado
  • Em termos de concorrência, INSERT ... ON CONFLICT é mais previsível
    Veja este artigo comparativo do modern-sql.com
  • Para inserção em lote em grande volume, usar COPY INTO em formato binário é o mais rápido. Quase não há overhead no lado do servidor

• O índice BRIN que o texto não abordou me pareceu interessante
  Se os dados aumentam de forma monotônica, é ideal como um índice muito pequeno e rápido

  • Não precisa ser um dado perfeitamente monotônico. Se for monotônico na maior parte do tempo, já funciona bem
    Por exemplo, serve bem para casos levemente fora de ordem, como dados de timestamp recebidos pelo servidor
    No caso de UUIDv7, talvez seja preciso ajustar pages_per_range

• Sempre acho uma pena que em índices hash não dê para aplicar restrição de unicidade
  Parece que isso se resolveria só com um pouco de glue code para converter em exclusion constraint, então fico me perguntando por que isso ainda não existe

• A validação de unicidade baseada em hash não é suportada no índice porque não lida com colisões
  A solução proposta sofre do mesmo problema

  • Isso não é verdade. O índice armazena só o hash, mas a tabela armazena o valor completo
    O Postgres só considera duplicado quando tanto o hash quanto o valor real coincidem
  • Também dá para confirmar isso neste exemplo no dbfiddle

• O conteúdo do texto foi original. Colunas virtuais e índices hash são interessantes, mas ainda passam a sensação de não estarem totalmente integrados ao ecossistema

  • As colunas virtuais estão praticamente prontas. A maior parte foi implementada no PostgreSQL 18
    Índices hash tiveram muitas limitações por muito tempo, mas vêm melhorando aos poucos, e a restrição automática de unicidade ainda é a tarefa que falta

• Fiquei pensando se não daria para criar logo um índice usando uma stored generated column

  • O texto explica por que tenta evitar esse método
    Ele é suportado desde o PostgreSQL 14, mas como o resultado é armazenado fisicamente e ocupa armazenamento adicional, isso é uma desvantagem
  • Será que também não seria possível criar um partial index baseado em expressão?
  • No fim, como isso também aumenta o espaço de armazenamento, é uma abordagem que o exemplo do texto tenta evitar

• Depois de migrar para a nuvem, passei a lidar menos com pgsql diretamente em ambientes de servidor fixo
  Fiquei curioso se o realce de sintaxe SQL mostrado no texto é um recurso embutido ou alguma ferramenta separada

  • Eu uso pgcli. Tem várias facilidades, como indicador de estado da transação, autocompletar e highlighting
    Só acho inconveniente que, ao copiar consultas longas, ele adiciona automaticamente espaços após as quebras de linha
  • Se usar uma IDE como o IntelliJ, também dá para ter destaque de sintaxe e autocompletar junto