Todos sabemos que para agilizar una consulta SQL podemos usar índices. Un índice ayuda a la base de datos a buscar lo que queremos sin tener que analizar una a una las filas de una tabla. Pero, ¿sabías que hay algunas situaciones en las que el índice se anula sin avisar? Es lo que se conoce como “index obfuscation”, aquí te describo algunos de los casos más comunes:

Caso 1: Filtrar usando funciones

El índice no se tiene en cuenta cuando filtras utilizando una función sobre una columna con índice. Por ejemplo, si tenemos un índice en la columna users.email y queremos hacer una consulta para filtrar por la palabra test, sin tener en cuenta las posibles mayúsculas, podríamos hacer algo como:

SELECT name FROM users WHERE LOWER(email) LIKE '%test%';

El problema es que al hacer un LOWER(email), la base de datos ya no puede hacer uso del índice de users.email y tiene que hacer la búsqueda en toda la tabla.

Hay varias soluciones a esto, para este caso en concreto, podríamos utilizar un collation o tipo de dato en la columna afectada que sea case-insensitive. Otra posible solución es crear un índice para nuestro caso particular, un “functional index”:

CREATE UNIQUE INDEX idx_users_email_lower
ON users (LOWER(email));

Utilizando el EXPLAIN o EXPLAIN ANALYSE en Postgres se ve una mejora considerable:

• Sin usar el índice en LOWER(email): Execution Time: 28.957 ms
• Usando el índice: Execution Time: 0.088 ms

Caso 2: Filtrar usando operaciones

Otro caso típico es el de tener un índice en una columna y hacer operaciones con esa columna a la hora de filtrar, por ejemplo:

SELECT name FROM products WHERE price / 100 > 10;

La base de datos tiene un índice en la columna price pero no en price / 100, por lo que el índice se anula y la base de datos tiene que hacer esa operación con todas las filas. En este caso la solución no sería crear un nuevo índice, sino mover la operación a la derecha:

SELECT name FROM products WHERE price > 10 * 100; -- o: price > 1000 cents

Caso 3: Filtrar usando diferentes tipos

Dependiendo de la base de datos y el lenguaje de programación que utilices, es posible que estés ignorando los índices sin saberlo si utilizas los tipos incorrectos.

Por ejemplo, imagina una tabla de products con un código numérico pero que, por cualquier motivo, se ha decidido definir como VARCHAR en la base de datos. Si en el código de tu aplicación o en la consulta SQL pasas el tipo incorrecto, la base de datos tendrá que hacer una conversión y también ignorará el índice.

SELECT name FROM products WHERE code = 123; -- 698ms

    vs

    SELECT name FROM products WHERE code = '123'; -- 0.39ms

Todos estos casos son fáciles de pasar por alto durante el desarrollo, sobre todo si trabajamos con bases de datos de prueba con pocos datos.

Siempre que hagas una consulta SQL es recomendable echarle un ojo al “explain plan”, porque quizás la base de datos esté trabajando más de lo necesario.

También es imprescindible contar con una buena capa de observabilidad para detectar estos problemas lo antes posible.

¿Conoces otros casos que puedan afectar a nuestros índices?