miércoles, 22 de julio de 2009

Configuración avanzada de índices postgresql

3 comentarios
 
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%');

3 Responses so far.

  1. Anónimo says:

    muy bien,
    sirve de mucha ayuda

  2. Dilia says:

    Oye, pregunta, si tengo una tabla con:
    sistema
    tipo
    numero
    valor
    y hago accesos seguidos por los 3 primeros campos o combinación de algunos de ellos, por ejemplo por tipo y numero o solo por tipo, debo crear 1 indice idx_sistema_tipo_numero o mejor crear 3 (uno por cada campo)? si creo uno con los 3 campos, cuando accese por 1 solo no funcionará el indice?

  3. como puedo descargar KMKey quality?

Leave a Reply