PostgreSQL: tipos de índice

En este artículo, analizaremos la

• Índices de árbol B
• Índices hash
• Índices GIN
• BRIN
• Índices GiST
• Índices SP-GiST

Vamos a discutirlos en breve.

Índices de árbol B

B-tree es un árbol autoequilibrado que mantiene datos ordenados y permite búsquedas, inserciones, eliminaciones y acceso secuencial en tiempo logarítmico.

El planificador de consultas de PostgreSQL considerará usar un índice de árbol B siempre que las columnas de índice estén involucradas en una comparación que use uno de los siguientes operadores:

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

ME GUSTA ~

column_name LIKE 'foo%' 
column_name LIKE 'bar%' 
column_name  ~ '^foo'

ME GUSTA ~*

Índices hash

Los índices hash solo pueden manejar una comparación de igualdad simple (=). Significa que siempre que una columna indexada esté involucrada en una comparación usando el operador igual (=), el planificador de consultas considerará usar un índice hash.

Para crear un índice hash, utilice la declaración CREATE INDEX con el tipo de índice HASH en la cláusula USING de la siguiente manera:

Syntax:
CREATE INDEX index_name 
ON table_name USING HASH (indexed_column);

Índices GIN

GIN significa Índices Invertidos Generalizados . Se le conoce comúnmente como GIN.

Los índices GIN son más útiles cuando tiene varios valores almacenados en una sola columna, por ejemplo, tipos hstore , array , jsonb y range.

BRIN

BRIN son las siglas de Block Range Indexes . BRIN es mucho más pequeño y menos costoso de mantener en comparación con un índice B-tree.

BRIN permite el uso de un índice en una tabla muy grande que anteriormente sería poco práctico usando B-tree sin particiones horizontales. BRIN se usa a menudo en una columna que tiene un orden de clasificación lineal, por ejemplo, la columna de fecha de creación de la tabla de pedidos de ventas.

Índices GiST

GiST significa árbol de búsqueda generalizado . Los índices GiST permiten la construcción de estructuras de árboles generales. Los índices GiST son útiles para indexar tipos de datos geométricos y búsquedas de texto completo.

Índices SP-GiST

SP-GiST significa GiST con particiones espaciales . SP-GiST admite árboles de búsqueda particionados que facilitan el desarrollo de una amplia gama de diferentes estructuras de datos no balanceadas. Los índices SP-GiST son más útiles para los datos que tienen un elemento de agrupamiento natural y que tampoco son un árbol igualmente equilibrado, por ejemplo, GIS, multimedia, enrutamiento telefónico y enrutamiento IP.