PostgreSQL - ÍNDICES

Los índices son tablas de búsqueda especiales que el motor de búsqueda de la base de datos puede utilizar para acelerar la recuperación de datos. En pocas palabras, un índice es un puntero a datos en una tabla. Un índice en una base de datos es muy similar a un índice al final de un libro.

Por ejemplo, si desea hacer referencia a todas las páginas de un libro que trata un tema determinado, primero debe consultar el índice, que enumera todos los temas en orden alfabético y luego hacer referencia a uno o más números de página específicos.

Un índice ayuda a acelerar las consultas SELECT y las cláusulas WHERE; sin embargo, ralentiza la entrada de datos, con declaraciones UPDATE e INSERT. Los índices se pueden crear o eliminar sin ningún efecto sobre los datos.

La creación de un índice implica la instrucción CREATE INDEX, que le permite nombrar el índice, especificar la tabla y qué columna o columnas indexar, e indicar si el índice está en orden ascendente o descendente.

Los índices también pueden ser únicos, similar a la restricción ÚNICA, ya que el índice evita entradas duplicadas en la columna o combinación de columnas en las que hay un índice.

El comando CREATE INDEX

La sintaxis básica de CREATE INDEX es como sigue -

CREATE INDEX index_name ON table_name;

Tipos de índice

PostgreSQL proporciona varios tipos de índices: árbol B, Hash, GiST, SP-GiST y GIN. Cada tipo de índice utiliza un algoritmo diferente que se adapta mejor a diferentes tipos de consultas. De forma predeterminada, el comando CREATE INDEX crea índices de árbol B, que se ajustan a las situaciones más comunes.

Índices de una sola columna

Un índice de una sola columna es aquel que se crea basándose en una sola columna de la tabla. La sintaxis básica es la siguiente:

CREATE INDEX index_name
ON table_name (column_name);

Índices multicolumna

Un índice de varias columnas se define en más de una columna de una tabla. La sintaxis básica es la siguiente:

CREATE INDEX index_name
ON table_name (column1_name, column2_name);

Ya sea para crear un índice de una sola columna o un índice de varias columnas, tenga en cuenta las columnas que puede usar con mucha frecuencia en la cláusula WHERE de una consulta como condiciones de filtro.

Si solo se usa una columna, la opción debe ser un índice de una sola columna. Si hay dos o más columnas que se utilizan con frecuencia en la cláusula WHERE como filtros, el índice de varias columnas sería la mejor opción.

Índices únicos

Los índices únicos se utilizan no solo para el rendimiento, sino también para la integridad de los datos. Un índice único no permite que se inserten valores duplicados en la tabla. La sintaxis básica es la siguiente:

CREATE UNIQUE INDEX index_name
on table_name (column_name);

Índices parciales

Un índice parcial es un índice construido sobre un subconjunto de una tabla; el subconjunto se define mediante una expresión condicional (denominada predicado del índice parcial). El índice contiene entradas solo para aquellas filas de la tabla que satisfacen el predicado. La sintaxis básica es la siguiente:

CREATE INDEX index_name
on table_name (conditional_expression);

Índices implícitos

Los índices implícitos son índices que el servidor de la base de datos crea automáticamente cuando se crea un objeto. Los índices se crean automáticamente para restricciones de clave primaria y restricciones únicas.

Ejemplo

El siguiente es un ejemplo en el que crearemos un índice en la tabla EMPRESA para la columna de salario:

# CREATE INDEX salary_index ON COMPANY (salary);

Ahora, enumeremos todos los índices disponibles en la tabla EMPRESA usando \d company mando.

# \d company

Esto producirá el siguiente resultado, donde company_pkey es un índice implícito, que se creó cuando se creó la tabla.

Table "public.company"
 Column  |     Type      | Modifiers
---------+---------------+-----------
 id      | integer       | not null
 name    | text          | not null
 age     | integer       | not null
 address | character(50) |
 salary  | real          |
Indexes:
    "company_pkey" PRIMARY KEY, btree (id)
    "salary_index" btree (salary)

Puede enumerar toda la base de datos de índices utilizando el \di comando -

El comando DROP INDEX

Se puede eliminar un índice usando PostgreSQL DROPmando. Se debe tener cuidado al eliminar un índice porque el rendimiento puede disminuir o mejorar.

La sintaxis básica es la siguiente:

DROP INDEX index_name;

Puede usar la siguiente declaración para eliminar el índice creado anteriormente:

# DROP INDEX salary_index;

¿Cuándo se deben evitar los índices?

Aunque los índices están destinados a mejorar el rendimiento de una base de datos, hay ocasiones en las que deben evitarse. Las siguientes pautas indican cuándo se debe reconsiderar el uso de un índice:

Los índices no deben usarse en tablas pequeñas.
Tablas que tienen operaciones de inserción o actualización por lotes grandes y frecuentes.
Los índices no deben usarse en columnas que contengan un número elevado de valores NULL.
Las columnas que se manipulan con frecuencia no deben indexarse.

↰ Previous page Next page ↱