Crear índice usando CREATE INDEX en PostgreSQL

En este artículo veremos como crear índice usando CREATE INDEX en PostgreSQL para mejorar la velocidad de la consultas y obtener los resultados más rápido. Si no conoces lo que es un índice puedes leer este otro artículo.

Sintaxis

La sintaxis para crear un índice es la siguiente:

CREATE INDEX nombre_indice ON nombreTabla [USING elegirMetodo]
(
    nombreColumna [ASC | DESC] [NULLS {FIRST | LAST }],
    ...
);Lenguaje del código: CSS (css)

En la sintaxis anterior:

• Después de CREATE INDEX se especifica el nombre del índice (se aconseja que sea fácil de recordar)
• Después de ON se especifica en qué tabla se va crear el índice.
• En tercer lugar se indica el método que se va utilizar para crear el índice. Tienes disponibles los tipos BTREE, HASH, GIST, SPGIST, GIN y BRIN. Esto es opcional, sino se especifica se usará BTREE.
• En cuarto lugar se escriben una o más columnas de la tabla que se utilizaran en el índice. Puedes utilizar ASC o DESC de forma opcional para ordenar siendo ASC por defecto si no lo especificas. También puedes utilizar opcionalmente si los nulos irán primero o al final. Ten en cuenta que si no especificas el tratamiento de nulos se podrán al principio cuando se utilice el orden descendente e irán al final cuando no especifiques el orden.

Comprobar si se usa un índice

Para comprobar si una consulta está utilizando un índice debes utilizar la instrucción EXPLAIN. En el siguiente ejemplo comprobamos si nuestra consulta a la tabla empleados esta utilizando un índice:

EXPLAIN 
select * 
from empleados
where nombre like 'Miguel%';Lenguaje del código: JavaScript (javascript)

Si ejecutamos la consulta comprobamos como en el resultado no se está utilizando ningún índice:

Crear un índice

En el siguiente ejemplo creamos un índice por el campo nombre de la tabla empleados con el resto de opciones por defecto:

CREATE INDEX index_emp_nombre 
ON empleados(nombre);

Si volvemos a ejecutar el explain podemos comprobar que ahora si esta utilizando el índice que hemos creado.

Índices parciales

Un índice parcial permite especificar por qué valores se quiere crear el índice mejorando la eficacia del índice y reduciendo su tamaño. Imagina una tabla de clientes en la que siempre filtras para obtener aquellos que su teléfono no es nulo ¿para qué indexar los nulos si nunca los utilizas? Aquí entra el índice parcial y su sintaxis es muy fácil.

CREATE INDEX nombre_indice 
ON nombreTabla(nombreColumna)
WHERE nombreColumna = valor;

Posibles problemas

En PostgreSQL son muchas las configuraciones que pueden provocar que no se use un índice, pero si te ocurre, lo más frecuente es que tengas que ejecutar de nueva las estadísticas. Para ejecutar la limpieza y el forzado de las estadísticas de una tabla ejecuta la siguiente sentencia:

VACUUM ANALYZE nombreTabla;

Otra posible solución es fomentar un escaneo de índice en lugar de un escaneo secuencial, prueba la siguiente sentencia:

set enable_seqscan = off;Lenguaje del código: JavaScript (javascript)

Vuelve a ejecutar el explain para ver si el índice se está utilizando y recuerda que si utilizas una función en la consulta sobre el campo del índice, este no será utilizado.

Ejemplo completo

Copia y pega el siguiente código en tu consola de PostgreSQL y realiza paso a paso leyendo los comentarios.

-- Borramos la tabla si existe
drop table if exists empleados;

-- Creamos la tabla
create table empleados
(
    id_empleado numeric(2,0),
    id_departamento numeric(2,0),
    nombre character varying(50),
    puesto character varying(50),
    fecha_alta date,
    sueldo integer
);

-- Insertamos valores
insert into empleados
values (1,1,'Miguel Troyano','Analista','26/09/1986',60000),
       (2,1,'Ismael Troyano','Analista','01/01/2001',60000),
	   (3,1,'Jose Troyano','Alta Dirección','01/01/2001',80000),
	   (4,1,'Pilar Redondo','Alta Dirección','02/02/2002',80000);

-- Mostramos si la consulta utiliza algún indice
EXPLAIN 
select * 
from empleados
where nombre like 'Miguel%';

-- Se crea un indice llamado index_emp_nombre
--por el campo nombre de la tabla empleados
CREATE INDEX index_emp_nombre 
ON empleados(nombre);

-- Mostramos si la consulta utiliza algún indice
EXPLAIN 
select * 
from empleados
where nombre like 'Miguel%';

--Se crea un indice parcial para indexar solo
--los empleados dados de alta desde el 01/01/2000
CREATE INDEX index_emp_fecha 
ON empleados(fecha_alta)
WHERE fecha_alta >= '01/01/2000';Lenguaje del código: JavaScript (javascript)