Índices de PostgreSQL
Un índice es un tipo especial de consulta de tabla que acelera la búsqueda de datos. En pocas palabras, un índice es un puntero a los datos de una tabla. Un índice en una base de datos es muy similar al índice de un libro.
Tomando como ejemplo la página del índice de un diccionario chino, podemos encontrar rápidamente el carácter deseado mediante el índice ordenado por pinyin, trazos, radicales, etc.
Los índices ayudan a acelerar las consultas SELECT y las cláusulas WHERE, pero ralentizan la entrada de datos al usar sentencias UPDATE e INSERT. Los índices se pueden crear o eliminar sin afectar los datos.
La sentencia CREATE INDEX se utiliza para crear un índice; permite nombrar el índice, especificar la tabla y una o más columnas a indexar, e indicar si el índice se ordena de forma ascendente o descendente.
Los índices también pueden ser únicos, similares a la restricción UNIQUE, que evitan entradas duplicadas en una columna o en una combinación de columnas.
Comando CREATE INDEX
La sintaxis de CREATE INDEX (crear índice) es la siguiente:
CREATE INDEX index_name ON table_name;
Tipos de índices
Índice de una sola columna
Un índice de una sola columna es un índice creado basándose únicamente en una columna de la tabla. Su sintaxis básica es la siguiente:
CREATE INDEX index_name ON table_name (column_name);
Índice compuesto
Un índice compuesto es un índice creado sobre varias columnas de una tabla. Su sintaxis básica es la siguiente:
CREATE INDEX index_name ON table_name (column1_name, column2_name);
Ya sea un índice de una sola columna o un índice compuesto, el índice debe crearse sobre columnas que se usen con mucha frecuencia en las condiciones de filtro de la cláusula WHERE.
Si solo se utiliza una columna, se elige un índice de una sola columna; si hay varias columnas, se usa un índice compuesto.
Índice único
El uso de índices únicos no solo es por rendimiento, sino también por integridad de los datos. Un índice único no permite insertar valores duplicados en la tabla. Su sintaxis básica es la siguiente:
CREATE UNIQUE INDEX index_name on table_name (column_name);
Índice parcial
Un índice parcial es un índice construido sobre un subconjunto de la tabla; dicho subconjunto se define mediante una expresión de condición. El índice solo contiene las filas que cumplen la condición. Su sintaxis básica es la siguiente:
CREATE INDEX index_name ON table_name(column_list) WHERE condition;
Aquí, index_name es el nombre del índice que desea crear, table_name es el nombre de la tabla que contiene las columnas que desea indexar, column_list es la lista de columnas que desea indexar, y condition es una expresión booleana que define qué filas se incluirán en el índice.
Índice implícito
En PostgreSQL, un índice implícito es un índice creado automáticamente por el servidor de base de datos al crear un objeto. Este tipo de índice se crea automáticamente para restricciones de clave primaria y restricciones únicas. Cuando al crear una tabla se declara una columna como clave primaria, restricción única o clave externa, PostgreSQL crea automáticamente un índice implícito para esa columna. La ventaja de esto es que simplifica la gestión de índices y mejora el rendimiento de la base de datos.
Por ejemplo, si al crear una tabla llamada "users" se declara una columna llamada "userid" como clave primaria, PostgreSQL creará automáticamente un índice implícito para la columna "userid". Esto significa que al insertar un nuevo registro, la base de datos generará automáticamente un valor de índice único para la columna "userid".
La creación y gestión de los índices implícitos la realiza PostgreSQL automáticamente; el usuario no necesita intervenir manualmente, lo que hace que la gestión de la base de datos sea más sencilla y eficiente.
Ejemplo
El siguiente ejemplo creará un índice en la columna SALARY de la tabla COMPANY:
# CREATE INDEX salary_index ON COMPANY (salary);
Ahora, use\d companyel comando para listar todos los índices de la tabla COMPANY:
# \d company
El resultado es el siguiente: company_pkey es el índice implícito, creado cuando se creó la tabla:
ejemplodb=# \d company
Table "public.company"
Column | Type | Collation | Nullable | Default
---------+---------------+-----------+----------+---------
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 usar\diel comando para listar todos los índices de la base de datos:
ejemplodb=# \di
List of relations
Schema | Name | Type | Owner | Table
--------+-----------------+-------+----------+------------
public | company_pkey | index | postgres | company
public | department_pkey | index | postgres | department
public | salary_index | index | postgres | company
(3 rows)
DROP INDEX (eliminar índice)
Un índice se puede eliminar usando el comando DROP de PostgreSQL.
DROP INDEX index_name;
Puede utilizar la siguiente sentencia para eliminar el índice creado anteriormente:
# DROP INDEX salary_index;
Después de eliminarlo, se puede ver que salary_index ya ha sido eliminado de la lista de índices:
ejemplodb=# \di
List of relations
Schema | Name | Type | Owner | Table
--------+-----------------+-------+----------+------------
public | company_pkey | index | postgres | company
public | department_pkey | index | postgres | department
(2 rows)
¿En qué casos se debe evitar usar índices?
Aunque el propósito de los índices es mejorar el rendimiento de la base de datos, hay varios casos en los que se debe evitar su uso.
Al usar índices, se deben considerar las siguientes pautas:
- Los índices no deben usarse en tablas pequeñas.
- Los índices no deben usarse en tablas con operaciones frecuentes de actualización o inserción masiva.
- Los índices no deben usarse en columnas que contengan una gran cantidad de valores NULL.
- Los índices no deben usarse en columnas que se operan con frecuencia.