PostgreSQL Index Advisor

• Extensão do PostgreSQL da Supabase que recomenda índices para melhorar o desempenho de consultas
• Ao passar uma consulta para a função index_advisor(), ela retorna o custo antes/depois para startup/total e o SQL DDL para criar o índice
  • Execução: select * from index_advisor('select book.id from book where title = $1');
  • Retorno: {"CREATE INDEX ON public.book USING btree (title)"}
• Para consultas complexas, pode retornar várias instruções de criação de índice
• Suporte a parâmetros genéricos ($1, $2, ..)
• Suporte a Materialized View
• Consegue identificar tabelas/colunas ocultas por views

[savvykang]

Na versão atual, ele recomenda apenas índices btree de uma única coluna. Se as condições de consulta ficarem mais complexas ou se você estiver usando busca full text, não poderá utilizá-lo. https://supabase.com/docs/guides/…

[savvykang]

Quando as condições da consulta são complexas, dizem que vários índices de coluna única são usados em vez de um índice multicoluna, mas parece que o comportamento não é exatamente o mesmo. Ou então também dizem que há situações em que o melhor é usar ao mesmo tempo um índice multicoluna e vários índices de coluna única

https://www.postgresql.org/docs/current/indexes-bitmap-scans.html

[xguru]

Comentários do Hacker News

• Seria bom se existisse um recurso que recomendasse tipos de dados mais eficientes com base nos dados realmente armazenados na tabela
• Seria ótimo ter um banco de dados que detectasse consultas lentas automaticamente e criasse os índices necessários
  • Ao executar testes de carga na aplicação, ele chamaria o banco de dados, coletaria as consultas e depois o banco se ajustaria automaticamente
• Eu não sabia que o HypoPG já estava disponível no RDS havia mais de 1 ano
• Em joins com 3 ou mais tabelas, eu quero que um índice seja usado em uma relation, mas, se eu não colocar limit na CTE, o Postgres tenta executar cada join em paralelo e acaba tentando fazer join de um número enorme de linhas
  • Lidar com o planejador de consultas hoje em dia está quase me fazendo terminar com o pg
• O CockroachDB tem um recurso semelhante embutido
  • Ele pega consultas lentas existentes, analisa índices virtuais e sugere um plano de consulta melhor
  • Dá para adicionar com um clique na UI do console
• Em mecanismos de consulta distribuída como Presto ou Spark, eles fazem algo parecido usando particionamento e buckets em vez de índices
  • Isso pode reduzir computação, tempo e custo
• É conveniente por ser escrito em Vanilla Pl/PgSQL
  • Existe a tentação de copiar a função index_advisor(text) para a sessão e começar a fazer hard coding e heurísticas
  • A maioria das extensões realmente úteis exige compilar, instalar, criar e remover
• É semelhante ao TiAdvisor do TiDB e usa um método virtual
• Estou usando o pghero, e ele oferece essa funcionalidade via GUI
• Não parece oferecer considerações ou insights sobre os trade-offs envolvidos
  • A extensão subjacente, HypoPG, aparentemente não coleta estatísticas sobre os dados que influenciam o planejador de consultas
• Fico curioso para saber se ele reconhece tabelas pai e filhas herdadas