¿Cómo decides qué índices necesita una tabla?

Parte de las consultas, no de la tabla. Mira qué aparece en WHERE, JOIN y ORDER BY, y luego construye un índice compuesto con las columnas de igualdad primero y la columna de rango u ordenación al final. Confirma con EXPLAIN ANALYZE que un escaneo de índice sustituyó al escaneo secuencial. Cada índice cuesta rendimiento de escritura y almacenamiento, así que elimina los no usados y evita indexar por sí sola una columna de baja cardinalidad.

Trabajo hacia atrás desde el log de consultas lentas en vez de adivinar en tiempo de diseño. Toma una consulta que filtra por tenant id y status y ordena por created at. El índice correcto ahí es sobre tenant id, status, created at en ese orden, porque los predicados de igualdad van primero y la columna de ordenación al final para que el índice también pueda satisfacer el orden. Pon created at primero y el índice es casi inútil para esa consulta. Luego ejecuto de verdad EXPLAIN ANALYZE y compruebo que el plan cambió y que las estimaciones de filas son razonables, porque el planificador ignorará un índice si cree que va a coincidir con casi toda la tabla igualmente. En el lado del coste, he visto una tabla con muchas escrituras y once índices donde los inserts eran el cuello de botella, y eliminar cuatro sin usar aproximadamente duplicó el rendimiento de ingesta. En Postgres consulto pg_stat_user_indexes buscando índices con cero escaneos antes de proponer eliminar nada, y los creo de forma concurrente sobre una tabla en producción.