Cómo crear índices en PostgreSQL

Los índices son tablas de búsqueda especializadas que utilizan los motores de búsqueda de bancos de datos para acelerar los resultados de las consultas. Un índice es una referencia a la información de una tabla. Por ejemplo, si los nombres en una agenda de contactos no están ordenados alfabéticamente, tendrá que ir hacia abajo en cada fila y buscar todos los nombres antes de llegar al número de teléfono específico que está buscando. Un índice acelera los comandos SELECT y las frases WHERE, realizando la entrada de datos en los comandos UPDATE e INSERT. Independientemente de si los índices se insertan o eliminan, no hay impacto en la información contenida en la tabla. Los índices pueden ser especiales de la misma manera que la limitación ÚNICA ayuda a evitar registros de réplica en el campo o conjunto de campos para los que existe el índice.

Sintaxis general

La siguiente sintaxis general se utiliza para crear índices.

>> CREAR ÍNDICE nombre_índice ON nombre_tabla (nombre_columna);

Para comenzar a trabajar en índices, abra el pgAdmin de Postgresql desde la barra de la aplicación. Encontrará la opción 'Servidores' que se muestra a continuación. Haga clic derecho en esta opción y conéctela a la base de datos.

Como puede ver, la base de datos 'Prueba' aparece en la opción 'Bases de datos'. Si no tiene una, haga clic con el botón derecho en 'Bases de datos', navegue hasta la opción 'Crear' y asigne un nombre a la base de datos según sus preferencias.

Expanda la opción 'Esquemas' y encontrará la opción 'Tablas' listada allí. Si no tiene uno, haga clic derecho en él, navegue hasta 'Crear' y haga clic en la opción 'Tabla' para crear una nueva tabla. Como ya hemos creado la tabla 'emp' puedes verla en la lista.

Pruebe la consulta SELECT en el Editor de consultas para obtener los registros de la tabla 'emp', como se muestra a continuación.

>> SELECCIONAR * DE público.emp ORDEN POR "id" ASC;

Los siguientes datos estarán en la tabla 'emp'.

Crear índices de una sola columna

Expanda la tabla 'emp' para encontrar varias categorías, e.gramo., Columnas, restricciones, índices, etc. Haga clic con el botón derecho en 'Índices', navegue hasta la opción 'Crear' y haga clic en 'Índice' para crear un nuevo índice.

Construya un índice para la tabla 'emp' dada, o visualización eventuada, usando la ventana de diálogo Índice. Aquí, hay dos pestañas: 'General' y 'Definición.'En la pestaña' General ', inserte un título específico para el nuevo índice en el campo' Nombre '. Elija el 'espacio de tabla' en el que se almacenará el nuevo índice utilizando la lista desplegable junto a 'Espacio de tabla.'Como en el área' Comentario ', haga comentarios de índice aquí. Para comenzar este proceso, navegue a la pestaña 'Definición'.

Aquí, especifique el 'Método de acceso' seleccionando el tipo de índice. Después de eso, para crear su índice como 'Único', hay varias otras opciones enumeradas allí. En el área 'Columnas', toque el signo '+' y agregue los nombres de las columnas que se usarán para la indexación. Como puede ver, hemos estado aplicando la indexación solo a la columna 'Teléfono'. Para comenzar, seleccione la sección SQL.

La pestaña SQL muestra el comando SQL que ha sido creado por sus entradas a lo largo del diálogo Índice. Haga clic en el botón 'Guardar' para crear el índice.

Nuevamente, vaya a la opción 'Tablas' y navegue hasta la tabla 'emp'. Actualice la opción 'Índices' y encontrará el índice 'index_on_phone' recién creado enumerado en él.

Ahora, ejecutaremos el comando EXPLAIN SELECT para verificar los resultados de los índices con la cláusula WHERE. Esto dará como resultado la siguiente salida, que dice, 'Seq Scan on emp.'Puede preguntarse por qué sucedió esto mientras usa índices.

Razón: el planificador de Postgres puede decidir no tener un índice por varias razones. El estratega toma las mejores decisiones la mayor parte del tiempo, aunque las razones no siempre son claras. Está bien si se utiliza una búsqueda de índice en algunas consultas, pero no en todas. Las entradas devueltas de cualquiera de las tablas pueden variar, según los valores fijos devueltos por la consulta. Debido a que esto ocurre, un escaneo de secuencia es casi siempre más rápido que un escaneo de índice, lo que indica que quizás el planificador de consultas tuvo razón al determinar que el costo de ejecutar la consulta de esta manera se reduce.

Crear índices de varias columnas

Para crear índices de múltiples columnas, abra el shell de la línea de comandos y considere la siguiente tabla 'estudiante' para comenzar a trabajar en índices con múltiples columnas.

>> SELECCIONAR * DE Estudiante;

Escriba la siguiente consulta CREATE INDEX en ella. Esta consulta creará un índice llamado 'new_index' en las columnas 'sname' y 'age' de la tabla 'student'.

>> CREAR ÍNDICE new_index ON Estudiante (sname, edad);

Ahora, enumeraremos las propiedades y atributos del índice 'new_index' recién creado usando el comando '\ d'. Como puede ver en la imagen, este es un índice de tipo btree que se aplicó a las columnas 'sname' y 'age'.

>> \ d new_index;

Crear índice ÚNICO

Para construir un índice único, asuma la siguiente tabla 'emp'.

>> SELECCIONAR * DE emp;

Ejecute la consulta CREATE UNIQUE INDEX en el shell, seguida del nombre de índice 'empind' en la columna 'nombre' de la tabla 'emp'. En la salida, puede ver que el índice único no se puede aplicar a una columna con valores de 'nombre' duplicados.

>> CREAR ÍNDICE único empind ON emp (nombre);

Asegúrese de aplicar el índice único solo a las columnas que no contienen duplicados. Para la tabla 'emp', puede suponer que solo la columna 'id' contiene valores únicos. Entonces, le aplicaremos un índice único.

>> CREAR INDICE único empind ON emp (id);

Los siguientes son los atributos del índice único.

>> \ d empid;

Índice de caída

La instrucción DROP se usa para eliminar un índice de una tabla.

>> DROP INDEX empind;

Conclusión

Si bien los índices están diseñados para mejorar la eficiencia de las bases de datos, en algunos casos, no es posible utilizar un índice. Al utilizar un índice, se deben tener en cuenta las siguientes reglas:

• Los índices no deben descartarse para tablas pequeñas.
• Tablas con una gran cantidad de operaciones de actualización / actualización por lotes o de adición / inserción a gran escala.
• Para columnas con un porcentaje sustancial de valores NULL, los índices no se pueden mezclar-
• venta.
• Se debe evitar la indexación con columnas manipuladas regularmente.