Los índices se utilizan, principalmente, para mejorar la performance de una base de datos. Se definen sobre las columnas de la tabla que se utilicen en consultas repetidamente. Su uso inapropiado resultará en un funcionamiento más lento, ya que los tiempos de actualización e inserción se ven incrementados ante la presencia de índices.
Dos tipos de índices pueden ser definidos:
Indice de valor. Los campos clave para el son especificados como nombres de columna; una columna puede contener un operador de clase asociado. Un operador de clase permite especificar los operadores a ser utilizados por un índice en particular. Por ejemplo, un índice del tipo btree de enteros de cuatro bytes utilizaría la clase int4_ops; esta clase contiene funciones de comparación para enteros de cuatro bytes. La clase de operadores por defecto es la apropiada para cada tipo de campo.
Indice funcional. El índice se define por el resultado de una función definida por el usuario aplicada a uno o más atributos de la clase individual. Estos índices funcionales pueden ser utilizados para obtener un acceso más veloz a información basada en operadores que normalmente requieren alguna transformación para ser aplicados a la información de la base.
Postgres provee accesos de tipo btree (árbol-b), rtree (árbol-r) y hash (desmenuzamiento) para índices secundarios. El acceso de tipo btree es una implementación de btree tipo Lehman-Yao de alta concurrencia. El acceso de tipo rtree se implementa utilizando el algoritmo de Guttman's. El método hash es una implementación del método de Litwin's. Se citan estos algoritmos únicamente para indicar que todos estos métodos de acceso son totalmente dinámicos y no requieren ser optimizados periódicamente (como es el caso, por ejemplo, de los métodos de acceso tipo hash estáticos).
El optimizador de consultas de Postgres considerará utilizar índices de tipo btree en una búsqueda cuando alguno de los atributos indexados esté involucrado en una comparación que utilice uno de los siguientes operadores: <, <=, =, >=, >.
En Postgres, las dos clases de caja del tipo de datos box soportan índices. La diferencia entre ellas radica en que bigbox_ops reduce las coordenadas de la caja para evitar excepciones de punto flotante originadas en multiplicaciones, sumas, y substracciones con coordenadas de punto flotante demasiado grandes. Si el campo sobre el que se ubica su rectángulo es de aproximadamente 20.000 unidades cuadradas o mayor, debería utilizar bigbox_ops . La clase de operadores poly_ops brinda soporte para índices de tipo rtree en la información de polygon.
El optimizador de consultas de Postgres considerará utilizar índices de tipo rtree en una búsqueda cuando alguno de los atributos indexados esté involucrado en una comparación que utilice uno de los siguientes operadores: <<, &<, &>, >>, @, ~=, &&.
El optimizador de consultas de Postgres considerará utilizar índices de tipo hash en una búsqueda cuando alguno de los atributos indexados esté involucrado en una comparación que utilice el operador =.
Actualmente, sólo el acceso de tipo BTREE brinda soporte para índices multi-columna. Pueden especificarse hasta 7 claves.
Utilice DROP INDEX para eliminar un índice.
La clase de operadores int24_ops resulta útil para construir índices basados en datos de tipo int2, y realizar comparaciones con datos de tipo int4 en la calificación de consultas. De manera similar, int42_ops brinda soporte para índices basados en datos de tipo int4 que serán comparados con datos de tipo int2 en las consultas.
El siguiente comando lista todos los posibles valores para ops_names:
SELECT am.amname AS acc_name,
opc.opcname AS ops_name,
opr.oprname AS ops_comp
FROM pg_am am, pg_amop amop,
pg_opclass opc, pg_operator opr
WHERE amop.amopid = am.oid AND
amop.amopclaid = opc.oid AND
amop.amopopr = opr.oid
ORDER BY acc_name, ops_name, ops_comp
Autor: Escrito por Herouth Maoz. Este texto apareció originalmente en la lista de correo de usuarios el 02-03-1998 en respuesta a la pregunta: "¿Cuál es la diferencia entre las limitaciones de PRIMARY KEY y las de UNIQUE?"
Subject: Re: [QUESTIONS] PRIMARY KEY | UNIQUE
Cuál es la diferencia entre:
PRIMARY KEY(fields,...) and
UNIQUE (fields,...)
- ¿Es un alias?
- ¿Si PRIMARY KEY ya es, por sí mismo, único,
por qué existe entonces otra clave llamada UNIQUE?
Una clave principal es un campo (o combinación de más de un campo) utilizado para identificar un registro específico. Por ejemplo, el número de seguro social que identifica a una persona.
Una combinación UNIQUE de campos no tiene nada que ver con la identificación del registro. Es simplemente una limitación para mantener la integridad. Por ejemplo, yo tengo una colección de enlaces. Cada colección se identifica mediante un número único, el cual es la clave principal. Esta clave es la que se utiliza en las relaciones.
Sin embargo, mi aplicación requiere que cada colección tenga una nombre único. ¿Por qué? Para que cualquier persona que quiera modificar una colección pueda identificarla. Es mucho más difícil saber, si usted tiene dos colecciones con el nombre "Ciencias Naturales", que la marcada con el número 24433 es la que necesita, y la marcada con el número 29882 no lo es.
Entonces, el usuario selecciona la colección por su nombre. Nos aseguramos por consiguiente que, dentro de la base de datos, los nombres son únicos. Sin embargo, ninguna otra tabla en la base de datos se refiere a la colección por su nombre. Eso sería muy ineficiente. Más aun, a pesar de ser único, ¡el nombre no define realmente a la colección! Por ejemplo, si alguien decide cambiar el nombre de la colección de "Ciencias Naturales" a "Biología", seguirá siendo la misma colección, solo que con un nombre diferente. Mientras el nombre siga siendo único esto está bien.
Entonces:
Clave Principal:
Se utiliza para la identificación de los registros y para referirse a ellas.
Es imposible (o muy difícil) de actualizar.
No deben aceptar NULLs.
Campo(s) único(s):
Se utilizan como una forma alternativa de acceso a los registros.
Son actualizables, siempre y cuando se mantengan únicos.
Aceptan NULLs.
En cuanto a por qué no se definen claves no-únicas en la sintaxis estándar de SQL; bueno, usted debe entender que los índices dependen de las implementaciones. SQL no define la implementación, simplemente las relaciones entre los datos en la base. Postgres no permite índices no-únicos, pero los índices utilizados para fortalecer a SQL son siempre únicos.
Así, usted podría consultar una tabla mediante una combinación de sus columnas, a pesar de no poder tener un índice basado en ellas. Los índices son meramente una ayuda para la implementación que cada RDBMS le ofrece a fin de que las consultas que se realizan con más frecuencia sean más eficientes. Algunos RDBMS podrán ofrecerle ayudas adicionales, como mantener una clave en la memoria principal. Tendrán algún comando especial, por ejemplo:
CREATE MEMSTORE ON <table> COLUMNS <cols>(este no es un comando real, simplemente un ejemplo).
De hecho, cuando usted crea una clave principal o una combinación única de campos, en ninguna parte de la especificación de SQL se dice que se crea un índice, ni que la recuperación de datos utilizando la clave sea más eficiente que realizar un recorrido secuencial.
Entonces, si usted desea utilizar una combinación de campos que no sea única como clave secundaria, en realidad no necesita especificar nada - ¡simplemente empiece a recuperar datos a través de esa combinación! Sin embargo, si usted desea que esa recuperación de datos sea eficiente, deberá acudir a los medios que le ofrece su RDBMS - ya se trate de un índice, un comando MEMSTORE imaginario, o un RDBMS inteligente que construya índices sin su conocimiento fundamentándose en el hecho de que usted ya ha realizado varias consultas basadas en una combinación específica de claves... (Un sistema que aprende de la experiencia).