¿Es una buena práctica crear un índice en todas las columnas utilizadas en la cláusula WHERE?

No es una buena práctica crear índices de una sola columna en todas las columnas mencionadas en las cláusulas WHERE. Esos índices no ayudan mucho a sus consultas, y cuestan tiempo y IO cuando realiza operaciones INSERTAR y ACTUALIZAR.

Es una buena práctica crear índices de varias columnas que coincidan con las cláusulas WHERE de sus consultas de gran volumen.

Su consulta de muestra

 SELECT * FROM TABLE_1 WHERE COL_1 = ? AND COL_2 = ? AND COL_3 = ?

se beneficiará de un índice BTREE en (COL_1, COL_2, COL_3) . postgresql puede acceder aleatoriamente al índice de la primera fila de su tabla que coincida con su cláusula WHERE, luego recuperar las filas escaneando el índice.

Si tu consulta fuera

 SELECT * FROM TABLE_1 WHERE COL_1 = ? AND COL_TIME >= ? AND COL_3 = ?

desearía un índice en (COL_1, COL_3, COL_TIME) . De nuevo, postgresql puede acceder aleatoriamente al índice a la primera fila elegible, luego escanear el índice secuencialmente hasta llegar a la última fila elegible. Coloque las columnas de coincidencia de igualdad primero en el índice, luego la columna de coincidencia de rango ( COL_TIME >= ? ).

Diseñe sus índices para que coincidan

1. los requisitos de su base de datos para claves primarias y otras restricciones.
2. sus consultas de alto volumen.

Simplemente poner índices de una sola columna en muchas columnas es un error n00b. Pregúntame cómo sé esto alguna vez. ;-)

La indexación puede parecer arcana cuando empiezas a trabajar con ella. El libro de Marcus Winand https://use-the-index-luke.com/ es un buen lugar para comenzar a aprender.

Indexar una tabla frente a una consulta no es tan fácil que @OJones habla ... ¡Porque la cláusula WHERE no es la única cláusula SQL involucrada en la indexación de la tabla ...!

De hecho, todas las cláusulas de la consulta que son relativas a la tabla que desea acelerar con índices deben analizarse.

Como ejemplo, el hecho de que utilices un:

 SELECT *

...en su consulta, no ayuda ser lo suficientemente rápido con un índice que contiene solo las columnas utilizadas en la cláusula where. Muy a menudo, este índice no se utilizará porque para informar todos los valores de todas las columnas de la tabla (debido a SELECT *), el optimizador ("planer" como se dice en PG) necesita buscar en el índice, y luego haga otro acceso a la tabla para capturar todas las columnas que no están en la definición del índice...

Ahora tiene la opción de crear un índice con todas las columnas de la tabla (y en este caso, el uso de la cláusula INCLUDE recientemente agregada como lo hace MS SQL Server desde hace 15 años, lo ayudará a tener un índice no demasiado grande), o para reducir las columnas devueltas enumeradas en la cláusula SELECT y crear un índice de cobertura...

Un índice de cobertura es aquel que no necesita acceder dos veces a la tabla porque contiene todas las columnas requeridas de la consulta.

En un documento francés que se puede leer en:

Que faut-il indexador?

Clasifico los índices con una cita de "estrella":

• 1 estrella es un índice que solo cubre la cláusula WHERE de un SELECT
• 2 estrellas es un índice que cubre la cláusula WHERE y SELECT
• 3 estrellas es un índice que cubre la cláusula WHERE, SELECT y ORDER BY
• 4 estrellas es un índice que cubre las cláusulas WHERE, SELECT, ORDER BY y GROUP BY