Configuración avanzada de índices postgresql

Cuando creamos una tabla en postgresql es habitual crear índices por los campos que creemos van a usarse más para búsquedas. Normalmente, lo hacemos con un simple:

CREATE INDEX nombre_indice ON tabla(campo);

Bien, eso puede bastar en ciertos casos, pero si campo es un tipo texto y queremos hacer búsquedas usando LIKE, nos encontramos con sorpresas:

EXPLAIN ANALYZE SELECT * FROM tabla WHERE campo LIKE '%valor%';

Nos dirá que está ejecutando un SCAN de toda la tabla ! Nuestro índice no se está usando para búsquedas tipo LIKE. Para que lo use, necesitamos crear el índice de forma ligeramente distinta:

CREATE INDEX nombre_indice ON tabla(campo text_pattern_ops); (si campo es tipo TEXT)
CREATE INDEX nombre_indice ON tabla(campo varchar_pattern_ops); (si campo es tipo VARCHAR)

Si repetimos el EXPLAIN ANALYZE veremos que ahora SI se usa el índice

Otro caso típico que nos encontramos es con la normalización de los datos. O sea, como trabajar acentos, eñes, mínusculas y mayúsculas para que el usuario encuentre lo que busca, para que una búsqueda 'proyect' encuentre cosas como 'Caja PROYECTOR', 'Proyectó 1' o 'Proyecté una peli'.

Para ello se pueden guardar los datos tal como vienen, y crear un índice normalizado, usando una función PL/PGSQL de tipo inmutable. Por ejemplo:

CREATE OR REPLACE FUNCTION str_normalize (value TEXT) RETURNS text AS $$
BEGIN
RETURN lower(translate(value, 'áàéèíìóòúùäëïöüÁÀÉÈÍÌÓÒÚÙÄËÏÖÜñÑçÇ"?¿¡[]`{},:;=&%$#|!\ºª<>', 'aaeeiioouuaeiouAAEEIIOOUUAEIOUnNcC '));
END;
$$ LANGUAGE plpgsql;

CREATE INDEX nombre_indice ON tabla(str_normalize(campo) text_pattern_ops);

Ahora podemos comprobar que podemos hacer búsquedas normalizadas de lo más optimizado:

EXPLAIN ANALYZE SELECT * FROM tabla WHERE str_normalize(campo) LIKE str_normalize('%valor%');

Etiquetas: ,