Tipos de índices en PostgreSQL

Un índice es un objeto más de la base de datos. Vinculado a una tabla, está formado por una o más columnas de la tabla y por un puntero a la fila de la tabla que representa, con la interesante característica de que las filas dentro del índice mantienen siempre un orden especificado por algún algoritmo.

Cuando insertamos una fila nueva en la tabla, ésta se graba en un lugar indeterminado del espacio libre del fichero físico sin embargo, en el índice, el valor de la columna indexada (o columnas) se insertarán en su lugar correspondiente según dicho algoritmo. El índice nos garantiza que se mantendrá el orden también en operaciones de eliminación y de actualización de la columna o columnas indexadas.

Sin índice, cualquier búsqueda en la tabla obligará al planificador de consultas a hacer un escaneo completo secuencial de la tabla que, para tablas grandes, supone una importante inversión de tiempo.

Vamos a demostrar esto creando una tabla grande y ejecutando una consulta cualquiera con EXPLAIN ANALYZE.

demo=# create table test (id numeric, name varchar, fecha date);
CREATE TABLE

demo=# insert into test (select generate_series(1,10000000), ‘name-‘||generate_series(1,10000000), now() + interval ‘1’ minute);
INSERT 0 10000000

demo=# explain analyze select * from test where id > 100 and id <= 150;

QUERY PLAN

Gather (cost=1000.00..127195.83 rows=1 width=22) (actual time=29.329..1807.921 rows=50 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on test (cost=0.00..126195.73 rows=1 width=22) (actual time=1151.470..1736.500 rows=17 loops=3)
Filter: ((id > ‘100’::numeric) AND (id <= ‘150’::numeric))
Rows Removed by Filter: 3333317
Planning Time: 4.010 ms
JIT:
Functions: 6
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 1.856 ms, Inlining 0.000 ms, Optimization 17.279 ms, Emission 16.198 ms, Total 35.332 ms
Execution Time: 1808.181 ms
(12 rows);

Casi 2 segundos tardaríamos en obtener las 50 filas que buscamos. Demasiado tiempo ¿no crees?. Vamos a indexar la columna id con el algoritmo B-Tree que es el algoritmo con el que se crean los índices por defecto, si no le indicamos otra cosa.

demo=# create index test_idx on test (id);
CREATE INDEX

La operación CREATE INDEX es bloqueante pero puedes utilizar la cláusula CONCURRENTLY con la orden CREATE INDEX para que no lo sea.

demo=# explain analyze select * from test where id > 100 and id <= 150;

QUERY PLAN

Index Scan using test_idx on test (cost=0.43..9.46 rows=51 width=22) (actual time=0.008..0.013 rows=50 loops=1)
Index Cond: ((id > ‘100’::numeric) AND (id <= ‘150’::numeric))
Planning Time: 0.428 ms
Execution Time: 0.023 ms
(4 rows)

La mejora es considerable como se puede apreciar. Puedes estar pensando que los datos ya estaban en memoria al haber sido recuperados de los ficheros con la consulta anterior.. prueba a reiniciar el servidor para vaciar las memorias y comprueba de nuevo. Verá que no hay grandes diferencias.

Debemos saber que tener índices no es gratuito. Por un lado, las operaciones de inserción y eliminación de filas en la tabla, y las operaciones de actualización de los campos indexados se verán aumentados puesto que ahora, además de realizar la operación correspondiente sobre la tabla tendrá también que realizarla sobre el índice. Por otra parte, el índice también ocupa espacio físico.

Vamos a ver el tamaño del índice con respecto al tamaño de la tabla.

demo=# \dti+
List of relations

El tamaño del índice no es nada despreciable. Pero créeme, tener los índices adecuados compensa con creces estos inconvenientes.

Vamos a enumerar ahora los tipos de índices disponibles, en el estándar de PostgreSQL, y cuándo es mejor usar uno u otro.

ÍNDICES B-TREE

El planificador de consultas de PostgreSQL utilizará un índice B-Tree siempre que una columna indexada esté involucrada en una comparación, usando alguno de estos operadores:

<, <=, =, >=, >, BETWEEN, IN, IS/IS NOT NULL

También LIKE y ~ si el patrón de búsqueda es una constante anclada al comiendo de la cadena.

Hemos demostrado ya la potencia de este tipo de índices con el ejemplo anterior.

ÍNDICES HASH

Los índices Hash almacenan un código hash de 32 bits a partir del valor de la columna indexada. Estos índices, por lo tanto, solo pueden manejar comparaciones de igualdad simple.

Vamos a comprobarlo:

demo=# explain analyze select * from test where name = ‘name-123’;

QUERY PLAN

Gather (cost=1000.00..116778.43 rows=1 width=22) (actual time=13.616..588.602 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on test (cost=0.00..115778.33 rows=1 width=22) (actual time=368.723..558.949 rows=0 loops=3)
Filter: ((name)::text = ‘name-123’::text)
Rows Removed by Filter: 3333333
Planning Time: 0.075 ms
JIT:
Functions: 6
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 0.712 ms, Inlining 0.000 ms, Optimization 8.618 ms, Emission 16.178 ms, Total 25.508 ms
Execution Time: 588.893 ms
(12 rows)

demo=# create index test_name_idx_hash on test using hash (name);
CREATE INDEX

demo=# explain analyze select * from test where name = ‘name-123’;

QUERY PLAN

Index Scan using test_name_idx_hash on test (cost=0.00..8.02 rows=1 width=22) (actual time=0.453..0.454 rows=1 loops=1)
Index Cond: ((name)::text = ‘name-123’::text)
Planning Time: 2.905 ms
Execution Time: 0.468 ms
(4 rows)

Vamos a ver el tamaño de este nuevo índice:

Como en el caso anterior, el tiempo de ejecución cuando se dispone de un índice es considerablemente menor.

ÍNDICES GiST

Los índices GiST no son un tipo de índice al uso sino una infraestructura dentro de la cual se pueden implementar muchas estrategias de indexación diferentes según la clase de operador.

La distribución estándar de PostgreSQL incluye clases de operadores GiST para varios tipos de datos geométricos bidimensionales, que admiten consultas indexadas utilizando los operadores:

<<, &<, &>, >>, <<|, &<|, |&>, |>>, @>, <@, -=, &&

Los índices GiST son capaces de optimizar, por ejemplo, las consultas que buscan los lugares más cercanos a un punto geográfico dado.

ÍNDICES SP-GiST

Al igual que los índices GiST, los índices SP-GiST ofrecen una infraestructura que admite varios tipos de búsquedas.

Los operadores SP-GiST que incluye la versión estándar de PostgreSQL son:

<<, >>, ~=, <@, <<|, |>>

ÍNDICES GIN

Los índices GIN se conocen como «índices invertidos» y son apropiados para datos que contienen múltiples componentes con sus correspondientes valores como por ejemplo las matrices.

Un índice invertido contiene una entrada separada para cada valor de componente y puede manejar de manera eficiente las consultas que prueban la presencia de valores de componentes específicos.

Al igual que los dos anteriores, los índices GIN puede admitir muchas estrategias de indexación diferentes. Los operadores con los que se pueden usar un índice GIN son:

<@, @>, = &&

ÍNDICES BRIN

BRIN es la abreviatura de «Block Range INdexes». Estos índices almacenan resúmenes sobre los valores almacenados en bloques físicos consecutivos de una tabla. Esto los hace muy óptimos para columnas cuyos valores están ordenador linealmente con el orden físico de las filas de la tabla.

Para los tipos de datos que tienen un orden de clasificación lineal, los datos indexados corresponde a los valores mínimo y máximo en la columna para cada rango de bloque. De esta forma, el índice en lugar de almacenar un valor por cada fila de la tabla, almacena un par de valores por cada conjunto de n filas (las que caben en un bloque para esa tabla). El tamaño del índice es mucho menor que en los casos anteriores, y aún más eficiente.

Los operadores con los que el planificador puede utilizar un índice BRIN son:

<, <=, =, >=, >

La columna fecha de la tabla test tiene un orden natural, podemos suponer que equivale a la fecha en la que se hace un pedido, la fecha en la que se contrata a un empleado, etc. aunque no tiene por qué ser un campo de tipo FECHA. Podría ser también la clave primaria de una tabla, que se genera automáticamente con una secuencia, por ejemplo.

Vamos a probar este tipo de índice:

demo=# create index test_name_idx_brin on test using brin (id);
CREATE INDEX

demo=# explain analyze select * from test where id > 300 and id <= 350;

QUERY PLAN

Bitmap Heap Scan on test (cost=16.03..42545.12 rows=1 width=22) (actual time=0.143..2.956 rows=50 loops=1)
Recheck Cond: ((id > ‘300’::numeric) AND (id <= ‘350’::numeric))
Rows Removed by Index Recheck: 20047
Heap Blocks: lossy=128
-> Bitmap Index Scan on test_name_idx_brin (cost=0.00..16.03 rows=20080 width=0) (actual time=0.104..0.105 rows=1280 loops=1)
Index Cond: ((id > ‘300’::numeric) AND (id <= ‘350’::numeric))
Planning Time: 0.068 ms
Execution Time: 2.972 ms

En esta ocasión, además de apreciarse una importante mejora en el tiempo de ejecución de la consulta podemos ver que el tamaño del índice es mucho menor que en los casos anteriores.

Si quieres saber más sobre índices y sobre todo aquello que puede ayudarte a mantener un servidor de bases de datos PostgreSQL a pleno rendimiento inscríbete en mi curso Tuning del servidor PostgreSQL para optimizar el rendimiento.

¡Hasta la próxima!