Supongamos que tenemos una tabla similar a esta:

CREATE TABLE test1 (
    id integer,
    content varchar
);

y la aplicación realiza muchas consultas de la forma:

SELECT content FROM test1 WHERE id = constante;

Sin ninguna preparación previa, el sistema tendría que escanear toda la tabla test1, fila por fila, para encontrar todas las entradas coincidentes. Si hay muchas filas en test1 y solo unas pocas filas (tal vez cero o una) que serían devueltas por dicha consulta, este es claramente un método ineficiente. Pero si se le ha indicado al sistema que mantenga un índice en la columna id, puede usar un método más eficiente para localizar las filas coincidentes. Por ejemplo, podría solo tener que recorrer unos pocos niveles de profundidad en un árbol de búsqueda.

Se utiliza un enfoque similar en la mayoría de los libros de no ficción: los términos y conceptos que los lectores buscan con frecuencia se recopilan en un índice alfabético al final del libro. El lector interesado puede escanear el índice con relativa rapidez y saltar a la página o páginas adecuadas, en lugar de tener que leer todo el libro para encontrar el material de interés. Al igual que es tarea del autor anticipar los elementos que es probable que los lectores busquen, es tarea del programador de la base de datos prever qué índices serán útiles.

Se puede usar el siguiente comando para crear un índice en la columna id, como se ha analizado:

CREATE INDEX test1_id_index ON test1 (id);

El nombre test1_id_index se puede elegir libremente, pero deberías elegir algo que te permita recordar más tarde para qué servía el índice.

Para eliminar un índice, usa el comando DROP INDEX. Los índices se pueden añadir a las tablas y eliminar de ellas en cualquier momento.

Una vez creado el índice, no se requiere más intervención: el sistema actualizará el índice cuando se modifique la tabla, y utilizará el índice en las consultas cuando piense que hacerlo sería más eficiente que un escaneo secuencial de la tabla. Pero es posible que tengas que ejecutar el comando ANALYZE regularmente para actualizar las estadísticas y permitir que el planificador de consultas tome decisiones informadas. Consulta Chapter 14 para obtener información sobre cómo averiguar si se está utilizando un índice y cuándo y por qué el planificador podría decidir no usar un índice.

Los índices también pueden beneficiar a los comandos UPDATE y DELETE con condiciones de búsqueda. Además, los índices se pueden usar en búsquedas de unión (joins). Por lo tanto, un índice definido en una columna que forma parte de una condición de unión también puede acelerar significativamente las consultas con uniones.

En general, los índices de PostgreSQL se pueden usar para optimizar consultas que contienen una o más cláusulas WHERE o JOIN de la forma:

columna-indexada operador-indexable valor-de-comparación

Aquí, la columna-indexada es cualquier columna o expresión sobre la que se ha definido el índice. El operador-indexable es un operador que es miembro de la clase de operadores (operator class) del índice para la columna indexada. (Aparecen más detalles sobre esto a continuación). Y el valor-de-comparación puede ser cualquier expresión que no sea volátil y no haga referencia a la tabla del índice.

En algunos casos, el planificador de consultas puede extraer una cláusula indexable de esta forma a partir de otra construcción SQL. Un ejemplo sencillo es que si la cláusula original era:

valor-de-comparación operador columna-indexada

entonces se puede dar la vuelta a la forma indexable si el operador original tiene un operador conmutador que es miembro de la clase de operadores del índice.

Crear un índice en una tabla grande puede llevar mucho tiempo. Por defecto, PostgreSQL permite que ocurran lecturas (sentencias SELECT) en la tabla en paralelo con la creación del índice, pero las escrituras (INSERT, UPDATE, DELETE) se bloquean hasta que finaliza la construcción del índice. En entornos de producción, esto suele ser inaceptable. Es posible permitir que ocurran escrituras en paralelo con la creación del índice, pero hay varias advertencias a tener en cuenta — para obtener más información, consulta Construcción de índices de forma concurrente.

Después de crear un índice, el sistema tiene que mantenerlo sincronizado con la tabla. Esto añade sobrecarga a las operaciones de manipulación de datos. Los índices también pueden impedir la creación de tuplas de solo montón (heap-only tuples). Por lo tanto, los índices que rara vez o nunca se usan en las consultas deberían eliminarse.