Ejemplo de CREATE INDEX de Postgresql

CREAR ÍNDICE: definir un nuevo índice

Sinopsis

CREATE[UNIQUE]INDEX[ CONCURRENTLY ][[IFNOTEXISTS] name ]ON[ ONLY ] table_name [USING method ](  column_name  [COLLATE collation ][ opclass [( opclass_parameter =value[,...])]][ASC|DESC][ NULLS  FIRST ][,...])[ INCLUDE ( column_name [,...])][WITH( storage_parameter [=value][,...])][TABLESPACE tablespace_name ][WHERE predicate ]

Descripción

CREATE INDEX construye un índice en las columnas especificadas de la relación especificada, que puede ser una tabla o una vista materializada. Los índices se utilizan principalmente para mejorar el rendimiento de la base de datos (aunque el uso inadecuado puede provocar un rendimiento más lento).

Los campos clave para el índice se especifican como nombres de columna o, alternativamente, como expresiones escritas entre paréntesis. Se pueden especificar varios campos si el método de índice admite índices de varias columnas.

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 función 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 en upper(col) permitiría la cláusula WHERE upper(col) = 'JIM' utilizar 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 eso es bastante complicado.

Cuando el WHERE la cláusula está presente, una índice parcial es creado. Un índice parcial es un índice que contiene entradas para solo una parte de una tabla, generalmente una parte que es más útil para indexar que el resto de la tabla. Por ejemplo, si tiene una tabla que contiene pedidos facturados y no facturados donde los pedidos no facturados ocupan una pequeña fracción del total de la tabla y, sin embargo, esa es una sección de uso frecuente, puede mejorar el rendimiento creando un índice solo en esa parte. Otra posible aplicación es utilizar WHERE con UNIQUE para imponer la unicidad sobre un subconjunto de una tabla. Consulte la Sección 11.8 para obtener más información.

La expresión utilizada en el WHERE La cláusula puede hacer referencia solo a las columnas de la tabla subyacente, pero puede usar todas las columnas, no solo las que se indexan. Actualmente, las subconsultas y las expresiones agregadas 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 una definición de índice deben ser “inmutable“, es decir, sus resultados deben depender solo de sus argumentos y nunca de ninguna influencia externa (como el contenido de otra tabla o el tiempo actual). Esta restricción asegura que el comportamiento del índice esté bien definido. Para utilizar una función definida por el usuario en una expresión de índice o WHERE cláusula, recuerde marcar la función como inmutable cuando la cree.

Parámetros

UNIQUE

Hace que el sistema verifique si hay valores duplicados en la tabla cuando se crea el índice (si los datos ya existen) y cada vez que se agregan datos. Los intentos de insertar o actualizar datos que resultarían en entradas duplicadas generarán un error.

Se aplican restricciones adicionales cuando se aplican índices únicos a tablas particionadas; ver CREAR TABLA.

CONCURRENTLY

Cuando se usa esta opción, PostgreSQL construirá el índice sin tomar ningún bloqueo que evite inserciones, actualizaciones o eliminaciones simultáneas en la tabla; mientras que una generación de índice estándar bloquea las escrituras (pero no las lecturas) en la tabla hasta que se hace. Hay varias advertencias que debe tener en cuenta al utilizar esta opción; consulte Creación de índices simultáneamente a continuación.

Para tablas temporales, CREATE INDEX siempre es no concurrente, ya que ninguna otra sesión puede acceder a ellos, y la creación de índices no concurrentes es más barata.

IF NOT EXISTS

No arroje un error si ya existe una relación con el mismo nombre. En este caso, se emite un aviso. Tenga en cuenta que no hay garantía de que el índice existente sea similar al que se habría creado. El nombre del índice es obligatorio cuando IF NOT EXISTS está especificado.

INCLUDE

El opcional INCLUDE cláusula especifica una lista de columnas que se incluirán en el índice como no clave columnas. Una columna que no es de clave no se puede utilizar en una calificación de búsqueda de escaneo de índice y no se tiene en cuenta a los fines de cualquier restricción de exclusividad o unicidad impuesta por el índice. Sin embargo, un escaneo de solo índice puede devolver el contenido de las columnas que no son clave sin tener que visitar la tabla del índice, ya que están disponibles directamente desde la entrada del índice. Por lo tanto, la adición de columnas que no son clave permite que los escaneos de índice solo se utilicen para consultas que de otro modo no podrían usarlos.

Es aconsejable ser conservador al agregar columnas que no sean clave a un índice, especialmente columnas anchas. Si una tupla de índice excede el tamaño máximo permitido para el tipo de índice, la inserción de datos fallará. En cualquier caso, las columnas que no son clave duplican los datos de la tabla del índice y aumentan el tamaño del índice, lo que potencialmente ralentiza las búsquedas. Además, la deduplicación de árbol B nunca se usa con índices que tienen una columna sin clave.

Columnas enumeradas en el INCLUDE la cláusula no necesita clases de operador apropiadas; la cláusula puede incluir columnas cuyos tipos de datos no tienen clases de operador definidas para un método de acceso dado.

Las expresiones no se admiten como columnas incluidas, ya que no se pueden utilizar en exploraciones de solo índice.

Actualmente, los métodos de acceso al índice B-tree y GiST admiten esta función. En el árbol B y los índices GiST, los valores de las columnas enumeradas en el INCLUDE Las cláusulas se incluyen en las tuplas de hojas que corresponden a las tuplas de montón, pero no se incluyen en las entradas de índice de nivel superior utilizadas para la navegación por árbol.

name

El nombre del índice que se creará. Aquí no se puede incluir ningún nombre de esquema; el índice siempre se crea en el mismo esquema que su tabla principal. Si se omite el nombre, PostgreSQL elige un nombre adecuado según el nombre de la tabla principal y el nombre de la columna indexada.

ONLY

Indica que no se debe recurrir a la creación de índices en particiones, si la tabla está particionada. El valor predeterminado es recurrir.

table_name

El nombre (posiblemente calificado por esquema) de la tabla que se indexará.

method

El nombre del método de índice que se utilizará. Las opciones son btree, hash, gist, spgist, gin, y brin. El método predeterminado es btree.

column_name

El nombre de una columna de la tabla.

expression

Una expresión basada en una o más columnas de la tabla. La expresión generalmente debe escribirse entre paréntesis, como se muestra en la sintaxis. Sin embargo, los paréntesis se pueden omitir si la expresión tiene la forma de una llamada a función.

collation

El nombre de la intercalación que se utilizará para el índice. De forma predeterminada, el índice utiliza la intercalación declarada para la columna que se indexará o la intercalación de resultados de la expresión que se indexará. Los índices con intercalaciones no predeterminadas pueden ser útiles para consultas que involucran expresiones que utilizan intercalaciones no predeterminadas.

opclass

El nombre de una clase de operador. Consulte los detalles a continuación.

opclass_parameter

El nombre de un parámetro de clase de operador. Consulte los detalles a continuación.

ASC

Especifica el orden de clasificación ascendente (que es el predeterminado).

DESC

Especifica un orden de clasificación descendente.

NULLS FIRST

Especifica que los nulos se ordenan antes que los no nulos. Este es el valor predeterminado cuando DESC está especificado.

NULLS LAST

Especifica que los nulos se ordenan después de los no nulos. Este es el valor predeterminado cuando DESC no se especifica.

storage_parameter

El nombre de un parámetro de almacenamiento específico del método de índice. Consulte Parámetros de almacenamiento de índices a continuación para obtener más detalles.

tablespace_name

El espacio de tabla en el que se creará el índice. Si no se especifica, se consulta default_tablespace, o temp_tablespaces para índices en tablas temporales.

predicate

La expresión de restricción para un índice parcial.

Parámetros de almacenamiento de índices

El opcional WITH cláusula especifica parámetros de almacenamiento para el índice. Cada método de índice tiene su propio conjunto de parámetros de almacenamiento permitidos. Los métodos de índice B-tree, hash, GiST y SP-GiST aceptan este parámetro:

fillfactor (integer)

El factor de relleno de un índice es un porcentaje que determina qué tan completo tratará el método de índice de empaquetar las páginas de índice. Para los árboles B, las páginas hoja se llenan hasta este porcentaje durante la creación del índice inicial, y también cuando se extiende el índice a la derecha (agregando nuevos valores clave más grandes). Si las páginas se llenan por completo posteriormente, se dividirán, lo que provocará una degradación gradual de la eficiencia del índice. Los árboles B usan un factor de relleno predeterminado de 90, pero se puede seleccionar cualquier valor entero de 10 a 100. Si la tabla es estática, el factor de relleno 100 es mejor para minimizar el tamaño físico del índice, pero para tablas muy actualizadas es mejor un factor de relleno más pequeño para minimizar la necesidad de divisiones de página. Los otros métodos de índice usan fillfactor de formas diferentes pero aproximadamente análogas; el factor de relleno predeterminado varía entre métodos.

Los índices de árbol B también aceptan estos parámetros:

deduplicate_items (boolean)

Controla el uso de la técnica de deduplicación de árbol B descrita en la Sección 63.4.2. Ajustado a ON o OFF para habilitar o deshabilitar la optimización. (Grafías alternativas de ON y OFF están permitidos como se describe en la Sección 19.1.) El valor predeterminado es ON.

Nota

Torneado deduplicate_items apagado a través de ALTER INDEX evita que las inserciones futuras activen la deduplicación, pero no hace que las tuplas de listas de publicación existentes utilicen la representación de tuplas estándar.

vacuum_cleanup_index_scale_factor (floating point)

Valor por índice para vacuum_cleanup_index_scale_factor.

Los índices GiST también aceptan este parámetro:

buffering (enum)

Determina si la técnica de generación de almacenamiento en búfer descrita en la Sección 64.4.1 se utiliza para generar el índice. Con OFF está deshabilitado, con ON está habilitado, y con AUTO inicialmente está deshabilitado, pero se enciende sobre la marcha una vez que el tamaño del índice alcanza el tamaño_caché_efectivo. El valor predeterminado es AUTO.

Los índices GIN aceptan diferentes parámetros:

fastupdate (boolean)

Esta configuración controla el uso de la técnica de actualización rápida descrita en la Sección 66.4.1. Es un parámetro booleano: ON permite una actualización rápida, OFF lo deshabilita. El valor predeterminado es ON.

Nota

Torneado fastupdate apagado a través de ALTER INDEX evita que las inserciones futuras entren en la lista de entradas de índice pendientes, pero no elimina por sí mismo las entradas anteriores. Tu podrías querer VACUUM la mesa o llamar gin_clean_pending_list función después para garantizar que la lista pendiente se vacíe.

gin_pending_list_limit (integer)

Parámetro personalizado gin_pending_list_limit. Este valor se especifica en kilobytes.

Los índices BRIN aceptan diferentes parámetros:

pages_per_range (integer)

Define el número de bloques de la tabla que componen un rango de bloque para cada entrada de un índice BRIN (consulte la Sección 67.1 para obtener más detalles). El valor predeterminado es 128.

autosummarize (boolean)

Define si se invoca una ejecución de resumen para el rango de páginas anterior siempre que se detecta una inserción en la siguiente.

Construyendo índices simultáneamente

La creación de un índice puede interferir con el funcionamiento normal de una base de datos. Normalmente, PostgreSQL bloquea la tabla para que sea indexada contra escrituras y realiza la construcción del índice completo con un solo escaneo de la tabla. Otras transacciones aún pueden leer la tabla, pero si intentan insertar, actualizar o eliminar filas en la tabla, se bloquearán hasta que finalice la generación del índice. Esto podría tener un efecto severo si el sistema es una base de datos de producción en vivo. Las tablas muy grandes pueden tardar muchas horas en indexarse, e incluso para tablas más pequeñas, una creación de índice puede bloquear a los escritores durante períodos que son inaceptablemente largos para un sistema de producción.

PostgreSQL admite la creación de índices sin bloquear las escrituras. Este método se invoca especificando el CONCURRENTLY opción de CREATE INDEX. Cuando se usa esta opción, PostgreSQL debe realizar dos escaneos de la tabla y, además, debe esperar a que finalicen todas las transacciones existentes que potencialmente podrían modificar o usar el índice. Por lo tanto, este método requiere más trabajo total que una generación de índice estándar y tarda mucho más en completarse. Sin embargo, dado que permite que las operaciones normales continúen mientras se crea el índice, este método es útil para agregar nuevos índices en un entorno de producción. Por supuesto, la carga adicional de CPU y E / S impuesta por la creación del índice podría ralentizar otras operaciones.

En una construcción de índice concurrente, el índice se ingresa en los catálogos del sistema en una transacción, luego ocurren dos escaneos de tabla en dos transacciones más. Antes de cada exploración de la tabla, la generación del índice debe esperar a que finalicen las transacciones existentes que han modificado la tabla. Después del segundo escaneo, la generación del índice debe esperar cualquier transacción que tenga una instantánea (consulte Capítulo 13) antes del segundo escaneo para terminar, incluidas las transacciones utilizadas por cualquier fase de las compilaciones de índices simultáneas en otras tablas. Luego, finalmente, el índice se puede marcar como listo para su uso, y el CREATE INDEX el comando termina. Incluso entonces, sin embargo, es posible que el índice no se pueda utilizar inmediatamente para consultas: en el peor de los casos, no se puede utilizar mientras existan transacciones anteriores al inicio de la creación del índice.

Si surge un problema al escanear la tabla, como un interbloqueo o una infracción de unicidad en un índice único, el CREATE INDEX El comando fallará pero dejará un “inválido“ índice. Este índice se ignorará para fines de consulta porque podría estar incompleto; sin embargo, seguirá consumiendo la sobrecarga de actualización. El psql d El comando informará un índice como INVALID:

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

El método de recuperación recomendado en tales casos es eliminar el índice y volver a intentar realizar CREATE INDEX CONCURRENTLY. (Otra posibilidad es reconstruir el índice con REINDEX INDEX CONCURRENTLY).

Otra advertencia al crear un índice único al mismo tiempo es que la restricción de unicidad ya se está aplicando a otras transacciones cuando comienza el segundo escaneo de la tabla. Esto significa que las infracciones de las restricciones se pueden informar en otras consultas antes de que el índice esté disponible para su uso, o incluso en los casos en que la generación del índice finalmente falle. Además, si ocurre una falla en el segundo escaneo, el “inválido“ index continúa aplicando su restricción de unicidad después.

Se admiten compilaciones simultáneas de índices de expresión e índices parciales. Los errores que ocurren en la evaluación de estas expresiones podrían causar un comportamiento similar al descrito anteriormente para violaciones de restricciones únicas.

Las compilaciones de índices regulares permiten que otras compilaciones de índices regulares en la misma tabla ocurran simultáneamente, pero solo una compilación de índices simultánea puede ocurrir en una tabla a la vez. En cualquier caso, no se permite la modificación del esquema de la tabla mientras se crea el índice. Otra diferencia es que un CREATE INDEX El comando se puede ejecutar dentro de un bloque de transacciones, pero CREATE INDEX CONCURRENTLY no poder.

Actualmente, no se admiten compilaciones simultáneas para índices en tablas particionadas. Sin embargo, puede construir simultáneamente el índice en cada partición individualmente y luego finalmente crear el índice particionado de manera no concurrente para reducir el tiempo en el que se bloquearán las escrituras en la tabla particionada. En este caso, la creación del índice particionado es una operación de solo metadatos.

Notas

Ver Capítulo 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 varias columnas. Se pueden especificar hasta 32 campos de forma predeterminada. (Este límite se puede modificar al compilar PostgreSQL). Actualmente, solo B-tree admite índices únicos.

Un clase de operador con parámetros opcionales se pueden especificar para cada columna de un índice. La clase de operador identifica los operadores que utilizará el índice para esa columna. Por ejemplo, un índice de árbol B en enteros de cuatro bytes usaría el int4_ops clase; esta clase de operador incluye funciones de comparación para enteros de cuatro bytes. En la práctica, la clase de operador predeterminada para el tipo de datos de la columna suele ser suficiente. El punto principal de tener clases de operador es que para algunos tipos de datos, podría haber más de un orden significativo. Por ejemplo, es posible que deseemos ordenar un tipo de datos de número complejo por valor absoluto o por parte real. Podríamos hacer esto definiendo dos clases de operador para el tipo de datos y luego seleccionando la clase adecuada al crear un índice. Más información sobre las clases de operador se encuentra en la Sección 11.10 y en Sección 37.16.

Cuando CREATE INDEX se invoca en una tabla particionada, el comportamiento predeterminado es recurrir a todas las particiones para asegurarse de que todas tengan índices coincidentes. Cada partición se verifica primero para determinar si ya existe un índice equivalente y, de ser así, ese índice se adjuntará como un índice de partición al índice que se está creando, que se convertirá en su índice principal. Si no existe un índice coincidente, se creará un nuevo índice y se adjuntará 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 el ONLY se especifica la opción, no se realiza ninguna recursividad y el índice se marca como no válido. (ALTER INDEX ... ATTACH PARTITION marca el índice como válido, una vez que todas las particiones adquieren índices coincidentes.) Tenga en cuenta, sin embargo, que cualquier partición que se cree en el futuro utilizando CREATE TABLE ... PARTITION OF automáticamente tendrá un índice coincidente, independientemente de si ONLY está especificado.

Para los métodos de índice que admiten escaneos ordenados (actualmente, solo árbol B), las cláusulas opcionales ASC, DESC, NULLS FIRSTy / o NULLS LAST se puede especificar para modificar el orden de clasificación del índice. Dado que un índice ordenado se puede escanear hacia adelante o hacia atrás, normalmente no es útil crear un índice de una sola columna. DESC index: ese orden de clasificación ya está disponible con un índice regular. El valor de estas opciones es que se pueden crear índices de varias columnas que coincidan con el orden de clasificación solicitado por una consulta de orden mixto, como SELECT ... ORDER BY x ASC, y DESC. los NULLS las opciones son útiles si necesita “nulos orden bajo“ comportamiento, en lugar del predeterminado “nulos ordenados alto“, en consultas que dependen de índices para evitar los pasos de clasificación.

El sistema recopila periódicamente estadísticas sobre todas las columnas de una tabla. Los índices sin expresión recién creados pueden usar inmediatamente estas estadísticas para determinar la utilidad de un índice. Para nuevos índices de expresión, es necesario ejecutar ANALYZE o espere a que el demonio de autovacío analice la tabla para generar estadísticas para estos índices.

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 del índice, siempre y cuando no lo haga más grande que la cantidad de memoria realmente disponible, lo que llevaría a la máquina al intercambio.

PostgreSQL puede crear índices mientras aprovecha múltiples CPU para procesar las filas de la tabla más rápido. Esta característica se conoce como construcción de índice paralelo. Para los métodos de índice que admiten la creación de índices en paralelo (actualmente, solo árbol B), maintenance_work_mem especifica la cantidad máxima de memoria que puede utilizar cada operación de creación de índice como un todo, independientemente de cuántos procesos de trabajo se hayan iniciado. Generalmente, un modelo de costos determina automáticamente cuántos procesos de trabajo deben solicitarse, si corresponde.

Las compilaciones de índices paralelas pueden beneficiarse del aumento maintenance_work_mem donde una construcción de índice serial equivalente verá poco o ningún beneficio. Tenga en cuenta que maintenance_work_mem puede influir en el número de procesos de trabajo solicitados, ya que los trabajadores paralelos deben tener al menos un 32MB parte del total maintenance_work_mem presupuesto. También debe haber un restante 32MB compartir para el proceso de 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 del índice, siempre que la generación del índice no esté ya vinculada a la E / S. Por supuesto, también debería haber suficiente capacidad de CPU que, de otro modo, permanecería inactiva.

Establecer un valor para parallel_workers a través de ALTER TABLE controla directamente cuántos procesos de trabajo paralelos serán solicitados por un CREATE INDEX contra la mesa. Esto pasa por alto el modelo de costos por completo y evita maintenance_work_mem que afecte a cuántos trabajadores paralelos se solicitan. Configuración parallel_workers a 0 a través de ALTER TABLE deshabilitará las compilaciones de índices paralelas en la tabla en todos los casos.

Propina

Es posible que desee restablecer parallel_workers después de configurarlo como parte del ajuste de una construcción de índice. Esto evita cambios involuntarios en los planes de consulta, ya que parallel_workers afecta todos escaneos de mesa paralela.

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

Utilice DROP INDEX para eliminar un índice.

Como cualquier transacción de larga duración, CREATE INDEX en una mesa puede afectar qué tuplas se pueden eliminar por concurrente VACUUM en cualquier otra mesa.

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

Ejemplos de

Para crear un índice de árbol B único en la columna title en la mesa films:

CREATEUNIQUEINDEX title_idx ON films (title);

Para crear un índice de árbol B único en la columna title con columnas incluidas director y rating en la mesa films:

CREATEUNIQUEINDEX title_idx ON films (title) INCLUDE (director, rating);

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

CREATEINDEX 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:

CREATEINDEXON films ((lower(title)));

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

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

CREATEINDEX title_idx_german ON films (title COLLATE"de_DE");

Para crear un índice con un orden de clasificación de nulos no predeterminado:

CREATEINDEX title_idx_nulls_low ON films (title NULLS FIRST);

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

CREATEUNIQUEINDEX title_idx ON films (title)WITH(fillfactor=70);

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

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

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

CREATEINDEX code_idx ON films (code)TABLESPACE indexspace;

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

CREATEINDEX 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 escrituras en la tabla:

CREATEINDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity);

Compatibilidad

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

Ver también

ALTER INDEX, DROP INDEX, REINDEX