Optimización de consultas con funciones de ventana

Queda un detalle importante que todavía no hemos comentado: el rendimiento de las consultas con funciones de ventana. Porque incluso la consulta más elegante puede convertirse en una tortuga lenta si no tienes en cuenta la optimización. ¡Hoy vamos a meternos de lleno en esto!

Las funciones de ventana son increíblemente flexibles y potentes. Pero esa flexibilidad no solo es un regalo, también puede ser una amenaza para el rendimiento. PostgreSQL, por desgracia, no funciona por "magia", y necesita recursos para procesar los datos. Y si le metes funciones de ventana a tablas enormes, tu consulta puede acabar haciendo algo parecido a correr una maratón en una cinta sin fin.

La optimización te permite:

• Acelerar consultas que trabajan con grandes volúmenes de datos.
• Minimizar la carga sobre la base de datos.
• Hacer tus consultas más amigables para el servidor (¡y para tus compis, si también trabajan con la base!).

Vamos a sumergirnos y ver qué se puede hacer para que tus consultas vuelen como un bólido en el circuito.

Bases del funcionamiento de las funciones de ventana

Antes de empezar a optimizar, conviene entender qué es lo que realmente ralentiza nuestra consulta. PostgreSQL trabaja con funciones de ventana de la siguiente manera:

1. Ordena los datos si la consulta tiene ORDER BY dentro de OVER().
2. Procesa cada fila dentro del marco o grupo de ventana definido.
3. Devuelve el resultado para cada fila.

Ahora imagina que tenemos una tabla sales con 10 millones de filas. Si tu consulta no tiene filtros, PostgreSQL va a procesar cada una de esas filas. Eso ya no es una maratón, ¡es una cinta de correr infinita!

¿Cómo acelerar las funciones de ventana?

1. Uso de índices para acelerar el ordenamiento

La mayoría de las funciones de ventana usan ORDER BY dentro de OVER() para controlar el orden de las filas. Esto significa que PostgreSQL tiene que ordenar tus datos antes de ejecutar la función de ventana.

Si tienes un índice en la columna (o columnas) que usas en ORDER BY, PostgreSQL puede acelerar mucho ese ordenamiento.

Ejemplo

CREATE INDEX idx_sales_date ON sales (sale_date);

Ahora, si escribes una consulta ordenando por sale_date, el índice entra en acción:

SELECT
    sale_date,
    product_id,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales;

Sin un índice en sale_date, cada vez que ejecutes la consulta habrá un ordenamiento costoso — PostgreSQL estará en modo pánico buscando cómo ordenar las filas más rápido.

2. Aplicar filtros usando WHERE

Reducir el volumen de datos es una técnica clave de optimización. Si no necesitas procesar los 10 millones de filas, pero solo quieres trabajar con el último año — ¡reduce el rango de datos usando WHERE!

Ejemplo

SELECT
    sale_date,
    product_id,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales
WHERE sale_date >= '2023-01-01';

Es como filtrar agua sucia con un colador para quedarte solo con la info útil.

3. Elegir el marco de ventana adecuado

Al trabajar con funciones de ventana de agregación, como SUM(), es importante elegir el marco de ventana correcto. Si usas el marco por defecto (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), PostgreSQL incluirá todas las filas hasta la actual. Esto puede ser poco óptimo en tablas grandes.

Ejemplo: uso de ROWS

Si solo necesitas incluir un par de filas antes de la actual, mejor indícalo explícitamente con ROWS:

SELECT
    sale_date,
    product_id,
    SUM(amount) OVER (
        PARTITION BY product_id 
        ORDER BY sale_date 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_sum
FROM sales;

En este caso, PostgreSQL solo procesa tres filas (dos anteriores + la actual) para cada fila. Es mucho más eficiente que procesar cientos de filas por defecto.

4. Minimizar la cantidad de funciones de ventana

Cada función de ventana se procesa individualmente en PostgreSQL. Si usas varias funciones de ventana, PostgreSQL puede ordenar para cada una por separado, lo que ralentiza todo. Sin embargo, si los parámetros de ventana (por ejemplo, PARTITION BY y ORDER BY) coinciden, PostgreSQL puede optimizarlo mejor.

Ejemplo: optimización con la misma ventana

SELECT
    product_id,
    sale_date,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total,
    ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY sale_date) AS row_num
FROM sales;

Ambas funciones (SUM() y ROW_NUMBER()) usan el mismo marco. PostgreSQL solo hará un ordenamiento — y eso mola.

5. Particionado de tablas

Si tu tabla es demasiado grande, vale la pena pensar en dividirla físicamente en partes más pequeñas. PostgreSQL permite crear tablas particionadas, así los datos se guardan en diferentes segmentos. Esto puede acelerar mucho el procesamiento.

Ejemplo de creación de tabla particionada

CREATE TABLE sales_partitioned (
    sale_date DATE NOT NULL,
    product_id INT NOT NULL,
    amount NUMERIC NOT NULL
) PARTITION BY RANGE (sale_date);

Después de esto, se crean varias particiones, por ejemplo, por años:

CREATE TABLE sales_2022 PARTITION OF sales_partitioned
FOR VALUES FROM ('2022-01-01') TO ('2022-12-31');

CREATE TABLE sales_2023 PARTITION OF sales_partitioned
FOR VALUES FROM ('2023-01-01') TO ('2023-12-31');

Ahora, si usas WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31', PostgreSQL irá solo a la partición correspondiente.

Más sobre particionado de tablas lo verás al final del curso :P

6. Evita datos innecesarios (SELECT solo lo que necesitas)

Elige solo las columnas que necesitas para la función y tu resultado. Si tu función de ventana solo necesita product_id, sale_date y amount, no arrastres todo el zoológico de la tabla con los bio-datos del cliente.

Ejemplo de consulta "económica"

SELECT
    product_id,
    sale_date,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales;

Menos datos — menos trabajo para PostgreSQL.

7. Uso de materialización (MATERIALIZED VIEW)

Si sueles hacer los mismos cálculos con funciones de ventana, puedes guardar los resultados en una vista materializada. Materialized View guarda los datos en disco para evitar recalcular consultas complejas.

Ejemplo de creación de vista materializada

CREATE MATERIALIZED VIEW sales_running_total AS
SELECT 
    product_id,
    sale_date,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales;

Ahora puedes consultar los datos directamente:

SELECT * FROM sales_running_total WHERE product_id = 10;

8. Planificación de consultas usando EXPLAIN y EXPLAIN ANALYZE

Como en otros aspectos de SQL, puedes usar EXPLAIN o EXPLAIN ANALYZE para ver cómo PostgreSQL ejecuta tu consulta — y dónde están los cuellos de botella.

Ejemplo de análisis de consultas

EXPLAIN ANALYZE
SELECT 
    product_id,
    sale_date,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales;

Esta herramienta te muestra dónde PostgreSQL gasta más tiempo, y así puedes optimizar los puntos lentos.

Las funciones de ventana son una herramienta potente para analizar datos, pero hay que usarlas con cuidado. ¿Quieres velocidad? Elige índices, añade filtros, no olvides las particiones y no dudes en usar vistas materializadas. ¡A PostgreSQL le encanta cuando lo usas con cabeza!