Crear indice usando CREATE INDEX en PostgreSQL

En este articulo veremos como crear indice 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 indice puedes leer este otro articulo.

Sintaxis

La sintaxis para crear un indice es la siguiente:

CREATE INDEX nombre_indice ON nombreTabla [USING elegirMetodo]
(
    nombreColumna [ASC | DESC] [NULLS {FIRST | LAST }],
    ...
);

En la sintaxis anterior:

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

Comprobar si se usa un indice

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

EXPLAIN 
select * 
from empleados
where nombre like 'Miguel%';

Si ejecutamos la consulta comprobamos como en el resultado no se esta utilizando ningún indice:

Crear un indice

En el siguiente ejemplo creamos un indice 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 indice que hemos creado.

Indices parciales

Un indice parcial permite especificar por qué valores se quiere crear el indice mejorando la eficacia del indice 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 indice 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 indice, 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;

Vuelve a ejecutar el explain para ver si el indice se esta utilizando y recuerda que si utilizas una función en la consulta sobre el campo del indice, 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';