Como você decide quais índices uma tabela precisa?

Comece pelas queries, não pela tabela. Veja o que aparece em WHERE, JOIN e ORDER BY, e então construa um índice composto com as colunas de igualdade primeiro e a coluna de faixa ou de ordenação por último. Confirme com EXPLAIN ANALYZE que uma varredura por índice substituiu a varredura sequencial. Todo índice custa throughput de escrita e armazenamento, então remova os não usados e evite indexar sozinha uma coluna de baixa cardinalidade.

Eu trabalho de trás para frente a partir do log de queries lentas em vez de chutar na hora do desenho. Pegue uma query filtrando por tenant id e status e ordenando por created at. O índice certo ali é em tenant id, status, created at nessa ordem, porque predicados de igualdade vêm primeiro e a coluna de ordenação vem por último para o índice também atender a ordenação. Coloque created at primeiro e o índice fica quase inútil para aquela query. Depois eu de fato rodo EXPLAIN ANALYZE e confiro se o plano mudou e se as estimativas de linhas fazem sentido, porque o planejador vai ignorar um índice se ele achar que aquilo vai casar com a maior parte da tabela mesmo. No lado do custo, eu já vi uma tabela pesada de escrita com onze índices em que os inserts eram o gargalo, e remover quatro não usados praticamente dobrou a taxa de ingestão. No Postgres eu checo o pg_stat_user_indexes em busca de índices com zero varreduras antes de propor a remoção de qualquer coisa, e crio índices de forma concorrente em uma tabela ao vivo.