Descripción

CREATE INDEX construye un índice en la(s) columna(s) especificada(s) de la relación dada, la cual puede ser una tabla o una vista materializada. Los índices se utilizan principalmente para mejorar el rendimiento de la base de datos (aunque un uso inadecuado puede ralentizarlo).

El o los campos clave para el índice se especifican como nombres de columna, o alternativamente como expresiones escritas entre paréntesis. Se pueden especificar múltiples campos si el método de indexación admite índices multicolumna.

Un campo de índice puede ser una expresión calculada a partir de los valores de una o más columnas de la fila de la tabla. Esta característica se puede utilizar para obtener un acceso rápido a los datos basándose en alguna transformación de los datos básicos. Por ejemplo, un índice calculado sobre upper(col) permitiría que la cláusula WHERE upper(col) = 'JIM' utilizara un índice.

PostgreSQL proporciona los métodos de índice B-tree, hash, GiST, SP-GiST, GIN y BRIN. Los usuarios también pueden definir sus propios métodos de índice, pero esto es bastante complicado.

Cuando la cláusula WHERE está presente, se crea un índice parcial. Un índice parcial es un índice que contiene entradas para solo una parte de una tabla, normalmente una parte que es más útil para indexar que el resto de la tabla. Por ejemplo, si tienes una tabla que contiene pedidos facturados y no facturados, donde los pedidos no facturados ocupan una pequeña fracción de la tabla total y, sin embargo, es una sección de uso frecuente, puedes mejorar el rendimiento creando un índice solo en esa parte. Otra aplicación posible es usar WHERE con UNIQUE para garantizar la unicidad sobre un subconjunto de una tabla. Consulta la Section 11.8 para más detalles.

La expresión utilizada en la cláusula WHERE puede hacer referencia solo a columnas de la tabla subyacente, pero puede usar todas las columnas, no solo las que se están indexando. Actualmente, las subconsultas y las expresiones de agregación también están prohibidas en WHERE. Las mismas restricciones se aplican a los campos de índice que son expresiones.

Todas las funciones y operadores utilizados en la definición de un índice deben ser «inmutables» (immutable), es decir, sus resultados deben depender únicamente de sus argumentos y nunca de ninguna influencia externa (como el contenido de otra tabla o la hora actual). Esta restricción garantiza que el comportamiento del índice esté bien definido. Para usar una función definida por el usuario en una expresión de índice o en una cláusula WHERE, recuerda marcar la función como inmutable cuando la crees.

Parámetros

postgres=# \d tab
       Table "public.tab"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 col    | integer |           |          |
Indexes:
    "idx" btree (col) INVALID

Notas

Consulta la Chapter 11 para obtener información sobre cuándo se pueden usar los índices, cuándo no se usan y en qué situaciones particulares pueden ser útiles.

Actualmente, solo los métodos de índice B-tree, GiST, GIN y BRIN admiten índices de múltiples columnas clave. El hecho de que pueda haber múltiples columnas clave es independiente de si se pueden añadir columnas INCLUDE al índice. Los índices pueden tener hasta 32 columnas, incluidas las columnas INCLUDE. (Este límite se puede alterar al compilar PostgreSQL.) Actualmente, solo B-tree admite índices únicos.

Se puede especificar una clase de operadores con parámetros opcionales para cada columna de un índice. La clase de operadores identifica los operadores que utilizará el índice para esa columna. Por ejemplo, un índice B-tree en enteros de cuatro bytes utilizaría la clase int4_ops; esta clase de operadores incluye funciones de comparación para enteros de cuatro bytes. En la práctica, la clase de operadores predeterminada para el tipo de datos de la columna suele ser suficiente. El punto principal de tener clases de operadores es que para algunos tipos de datos, podría haber más de un ordenamiento significativo. Por ejemplo, podríamos querer ordenar un tipo de datos de números complejos ya sea por valor absoluto o por parte real. Podríamos hacer esto definiendo dos clases de operadores para el tipo de datos y luego seleccionando la clase adecuada al crear un índice. Para más información sobre las clases de operadores, consulta la Section 11.10 y la Section 36.16.

Cuando se invoca CREATE INDEX en una tabla particionada, el comportamiento predeterminado es hacerlo de forma recursiva en todas las particiones para garantizar que todas tengan índices coincidentes. Primero se comprueba cada partición para determinar si ya existe un índice equivalente, y si es así, ese índice se asociará como un índice de partición al índice que se está creando, que se convertirá en su índice padre. Si no existe un índice coincidente, se creará un nuevo índice y se asociará automáticamente; el nombre del nuevo índice en cada partición se determinará como si no se hubiera especificado ningún nombre de índice en el comando. Si se especifica la opción ONLY, no se realiza recursión y el índice se marca como inválido. (ALTER INDEX ... ATTACH PARTITION marca el índice como válido una vez que todas las particiones adquieren índices coincidentes). Ten en cuenta, sin embargo, que cualquier partición que se cree en el futuro utilizando CREATE TABLE ... PARTITION OF tendrá automáticamente un índice coincidente, independientemente de si se especifica ONLY.

Para los métodos de índice que admiten escaneos ordenados (actualmente, solo B-tree), se pueden especificar las cláusulas opcionales ASC, DESC, NULLS FIRST, y/o NULLS LAST para modificar el orden de clasificación del índice. Dado que un índice ordenado se puede escanear tanto hacia adelante como hacia atrás, no suele ser útil crear un índice DESC de una sola columna; ese orden de clasificación ya está disponible con un índice normal. El valor de estas opciones es que se pueden crear índices multicolumna que coincidan con el orden de clasificación solicitado por una consulta de orden mixto, como SELECT ... ORDER BY x ASC, y DESC. Las opciones de NULLS son útiles si necesitas admitir el comportamiento de «nulos clasificados abajo» (nulls sort low), en lugar del predeterminado «nulos clasificados arriba» (nulls sort high), en consultas que dependen de índices para evitar pasos de ordenamiento.

El sistema recopila regularmente estadísticas sobre todas las columnas de una tabla. Los índices que no son expresiones creados recientemente pueden utilizar inmediatamente estas estadísticas para determinar la utilidad de un índice. Para los nuevos índices de expresiones, es necesario ejecutar ANALYZE o esperar a que el demonio de autovacuum analice la tabla para generar estadísticas para estos índices.

Mientras se ejecuta CREATE INDEX, el search_path se cambia temporalmente a pg_catalog, pg_temp.

Para la mayoría de los métodos de índice, la velocidad de creación de un índice depende de la configuración de maintenance_work_mem. Los valores más grandes reducirán el tiempo necesario para la creación de índices, siempre que no lo hagas más grande que la cantidad de memoria realmente disponible, lo que provocaría que la máquina entre en intercambio (swapping).

PostgreSQL puede construir índices aprovechando múltiples CPU para procesar las filas de la tabla más rápidamente. Esta característica se conoce como construcción paralela de índices. Para los métodos de índice que admiten la construcción de índices en paralelo (actualmente, B-tree, GIN y BRIN), maintenance_work_mem especifica la cantidad máxima de memoria que puede utilizar cada operación de construcción de índice en su conjunto, independientemente de cuántos procesos trabajadores se hayan iniciado. Generalmente, un modelo de costos determina automáticamente cuántos procesos trabajadores deben solicitarse, si es que se solicita alguno.

Las construcciones paralelas de índices pueden beneficiarse de un aumento de maintenance_work_mem donde una construcción de índice secuencial equivalente verá poco o ningún beneficio. Ten en cuenta que maintenance_work_mem puede influir en el número de procesos trabajadores solicitados, ya que los trabajadores paralelos deben tener al menos una participación de 32MB del presupuesto total de maintenance_work_mem. También debe haber una participación restante de 32MB para el proceso líder. El aumento de max_parallel_maintenance_workers puede permitir que se utilicen más trabajadores, lo que reducirá el tiempo necesario para la creación de índices, siempre y cuando la construcción del índice no esté ya limitada por E/S. Por supuesto, también debe haber suficiente capacidad de CPU que de otro modo permanecería ociosa.

Establecer un valor para parallel_workers a través de ALTER TABLE controla directamente cuántos procesos trabajadores paralelos solicitará un CREATE INDEX contra la tabla. Esto elude el modelo de costos por completo y evita que maintenance_work_mem afecte al número de trabajadores paralelos solicitados. Establecer parallel_workers en 0 a través de ALTER TABLE deshabilitará la construcción paralela de índices en la tabla en todos los casos.

Aunque CREATE INDEX con la opción CONCURRENTLY admite construcciones paralelas sin restricciones especiales, solo el primer escaneo de la tabla se realiza realmente en paralelo.

Utiliza DROP INDEX para eliminar un índice.

Al igual que cualquier transacción de larga duración, un CREATE INDEX en una tabla puede afectar a qué tuplas puede eliminar un VACUUM concurrente en cualquier otra tabla.

Las versiones anteriores de PostgreSQL también tenían un método de índice R-tree. Este método se ha eliminado porque no tenía ventajas significativas sobre el método GiST. Si se especifica USING rtree, CREATE INDEX lo interpretará como USING gist para simplificar la conversión de bases de datos antiguas a GiST.

Cada proceso backend que ejecuta CREATE INDEX informará de su progreso en la vista pg_stat_progress_create_index. Consulta la Section 27.4.4 para obtener detalles.

Ejemplos

Para crear un índice B-tree único en la columna title en la tabla films:

CREATE UNIQUE INDEX title_idx ON films (title);

Para crear un índice B-tree único en la columna title con las columnas incluidas director y rating en la tabla films:

CREATE UNIQUE INDEX title_idx ON films (title) INCLUDE (director, rating);

Para crear un índice B-Tree con deduplicación deshabilitada:

CREATE INDEX title_idx ON films (title) WITH (deduplicate_items = off);

Para crear un índice en la expresión lower(title), lo que permite búsquedas eficientes que no distinguen entre mayúsculas y minúsculas:

CREATE INDEX ON films ((lower(title)));

(En este ejemplo hemos elegido omitir el nombre del índice, por lo que el sistema elegirá un nombre, normalmente films_lower_idx.)

Para crear un índice con colación no predeterminada:

CREATE INDEX title_idx_german ON films (title COLLATE "de_DE");

Para crear un índice con ordenamiento de nulos no predeterminado:

CREATE INDEX title_idx_nulls_low ON films (title NULLS FIRST);

Para crear un índice con factor de relleno no predeterminado:

CREATE UNIQUE INDEX title_idx ON films (title) WITH (fillfactor = 70);

Para crear un índice GIN con actualizaciones rápidas deshabilitadas:

CREATE INDEX gin_idx ON documents_table USING GIN (locations) WITH (fastupdate = off);

Para crear un índice en la columna code en la tabla films y hacer que el índice resida en el tablespace indexspace:

CREATE INDEX code_idx ON films (code) TABLESPACE indexspace;

Para crear un índice GiST sobre un atributo de punto de modo que podamos usar eficientemente operadores de caja sobre el resultado de la función de conversión:

CREATE INDEX pointloc
    ON points USING gist (box(location,location));
SELECT * FROM points
    WHERE box(location,location) && '(0,0),(1,1)'::box;

Para crear un índice sin bloquear las escrituras en la tabla:

CREATE INDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity);

Compatibilidad

CREATE INDEX es una extensión del lenguaje de PostgreSQL. No existen provisiones para índices en el estándar SQL.

Consulte también