Cuando tenemos búsquedas frecuentes sobre un atributo --lo mismo aplica para un conjunto de atributos-- lo ideal es tener esa columna indexada para obtener más rápido la información.
PostgreSQL antes de crear el índice, hace un poco de heurística para determinar que tipo de estructura de datos utilizará y que sea óptima para esa columna --o conjunto de columnas--.
En realidad los criterios a seguir son muy simples, si la información de la base de datos se consultará con frecuencia sobre un atributo, ese es el candidato ideal para ser indexado.
En el caso de una llave primaria, PostgreSQL automáticamente crea el índice:
dbarc=> create table borrame (a text primary key, b int); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'borrame_pkey' for table 'borrame' CREATE
Como mencionamos en la sección 4.4, almacenar grandes textos en una base de datos es algo completamente imbécil, y peor en el caso de crear índices sobre campos de texto. Si por alguna retorcida razón, se nos ocurre guardar una descripción textual de personas, indexar este campo no tendría ningún sentido, porque tendríamos que hacer la búsqueda exactamente con el mismo contenido del campo:
INSERT INTO persona (generales) VALUES ('Es una persona a todo dar, le
gustan los animalitos y los niños pequeños, come de todo.')
WHERE id = 10;
SELECT * FROM persona WHERE generales = 'Es una persona a todo dar, le
gustan los animalitos y los niños pequeños, come de todo.';
¿Queda claro el concepto? Pero bueno, puede darse el caso en que realmente sea necesario tener un campo de este estilo y para el cuál valga la pena crear un índice, pero en ese caso, con los fuentes de PostgreSQL se distribuye una función extra, fulltextindex como parte del directorio contrib, que implementa esto de una manera muy interesante. Lo que hace es extraer las palabras y las referencias del registro a una tabla auxiliar, donde las palabras están indexadas, de tal manera que cuando se quiere hacer una búsqueda sobre el campo de texto, se buscan las palabras en esa tabla y lo que regresa son las referencias a las tuplas donde aparecen. El ahorro de espacio y la ganancia en velocidad son evidentes.
Para el caso en particular de tener que mantener muchos archivos de texto, es más recomendable usar indexadores de texto que no sean directos6.1, como glimpse, de Udi Manber. Glimpse utiliza algoritmos sofisticados para mantener los indices pequeños, de tal manera que los indices representan del 15 al 25% del tamaño del texto indexado. En el caso de los indices directos, el tamaño de estos con frecuencia es mayor que el tamaño del texto indexado.
Por último, la mejor manera de saber en que atributos conviene tener indices es correr un vacuum verbose analyze sobre la base de datos, para actualizar las estadísticas de las tablas y después utilizar la instrucción explain sobre las consultas que sabemos serán las más frecuentes:
discos=> VACUUM VERBOSE ANALYZE discos; NOTICE: --Relation discos-- NOTICE: Pages 19: Changed 0, Reapped 19, Empty 0, New 0; Tup 1390: Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 144, MinLen 76, MaxLen 168; Re-using: Free/Avail. Space 4772/288; EndEmpty/Avail. Pages 0/3. Elapsed 0/0 sec. NOTICE: Index xtitulo: Pages 23; Tuples 1390: Deleted 0. Elapsed 0/0 sec. NOTICE: Index xmedio: Pages 9; Tuples 1390: Deleted 0. Elapsed 0/0 sec. NOTICE: Index xautor: Pages 18; Tuples 1390: Deleted 0. Elapsed 0/0 sec. NOTICE: Rel discos: Pages: 19 --> 19; Tuple(s) moved: 0. Elapsed 0/0 sec. VACUUM discos=> EXPLAIN SELECT autor FROM discos WHERE autor = 'Bob Dylan'; NOTICE: QUERY PLAN: Index Scan using xautor on discos (cost=2.79 rows=16 width=12) EXPLAIN discos=> EXPLAIN SELECT * FROM discos WHERE categoria = 1; NOTICE: QUERY PLAN: Seq Scan on discos (cost=64.87 rows=1390 width=102) EXPLAIN
De inmediato podemos ver que el costo cuando hacemos un select sobre un campo indexado es menor que cuando lo hacemos sobre un campo no indexado, pero es de notar también que el sistema nos reporta que en el primer caso la búsqueda es sobre 16 tuplas mientras que en el segundo caso la búsqueda es sobre el total de las tuplas. Es decir, como era de esperarse, en el segundo caso hace un barrido secuencial sobre toda la tabla, mientras que en el primer caso, en el índice ``barre'' 16 entradas hasta encontrar al autor buscado.
Por supuesto, si vamos a permitir búsquedas sobre cualquier atributo lo primero que se nos ocurrirá es indexar cada uno de ellos, pero esto consume espacio brutalmente:
-rw------- 1 postgres postgres 155648 Jul 2 03:27 discos -rw------- 1 postgres postgres 8192 Dec 24 1999 sqcat -rw------- 1 postgres postgres 8192 Dec 24 1999 sqlug -rw------- 1 postgres postgres 147456 Jun 7 05:43 xautor -rw------- 1 postgres postgres 73728 Jun 7 05:43 xmedio -rw------- 1 postgres postgres 188416 Jun 7 05:43 xtitulo
en efecto, el índice para autores es casi del mismo tamaño que la tabla que contiene ese campo y el índice por título es incluso mayor6.2. Si el espacio en disco no es importante, no hay problema, pero si el acceso a disco es lento, entonces sí es una consideración a tener en cuenta.
<1765>>