Otimização de queries com funções window

Falta só mais um detalhe importante que a gente ainda não falou — performance das queries com funções window. Porque até a query mais elegante pode virar uma tartaruga lenta se você não pensar em otimização. Bora mexer nisso agora!

Funções window são super flexíveis e poderosas. Mas essa flexibilidade é um presente e, ao mesmo tempo, uma ameaça pra performance. O PostgreSQL, infelizmente, não faz mágica — ele precisa de recursos pra processar os dados. E se você jogar funções window em tabelas gigantes, sua query pode virar uma maratona sem linha de chegada.

Otimizar vai te ajudar a:

• Deixar as queries que mexem com muitos dados bem mais rápidas.
• Reduzir a carga no banco de dados.
• Fazer suas queries ficarem mais amigáveis pro servidor (e pros colegas, se eles também usam o banco!).

Bora mergulhar e ver o que dá pra fazer pra suas queries voarem igual carro de Fórmula 1.

Como funcionam as funções window

Antes de otimizar, é bom entender o que deixa a query lenta. O PostgreSQL trabalha com funções window assim:

1. Ordena os dados se tiver ORDER BY dentro do OVER().
2. Processa cada linha dentro do frame ou grupo definido.
3. Retorna o resultado pra cada linha.

Agora imagina que você tem uma tabela sales com 10 milhões de linhas. Se sua query não tiver filtros, o PostgreSQL vai processar cada uma dessas linhas. Isso não é nem maratona — é esteira infinita sem parar.

Como deixar funções window mais rápidas?

1. Usar índices pra acelerar a ordenação

A maioria das funções window usa ORDER BY dentro do OVER() pra controlar a ordem das linhas. Isso significa que o PostgreSQL precisa ordenar seus dados antes de rodar a função window.

Se você tiver um índice na coluna (ou colunas) usadas no ORDER BY, o PostgreSQL consegue acelerar muito essa ordenação.

Exemplo

CREATE INDEX idx_sales_date ON sales (sale_date);

Agora, se você fizer uma query ordenando por sale_date, o índice entra em ação:

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

Sem índice em sale_date, toda execução da query vai ter uma ordenação pesada — o PostgreSQL vai ficar doido tentando achar o jeito mais rápido de organizar as linhas.

2. Usar filtros com WHERE

Reduzir o volume de dados é uma técnica chave de otimização. Se você não precisa processar todos os 10 milhões de linhas, mas só o último ano — corta o range com WHERE!

Exemplo

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';

É tipo filtrar água suja numa peneira pra ficar só com o que interessa.

3. Escolher o frame certo da função window

Quando você usa funções window com agregação, tipo SUM(), é importante escolher o frame certo. Se você usar o frame padrão (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), o PostgreSQL vai incluir todas as linhas até a atual. Isso pode ser bem ruim pra tabelas grandes.

Exemplo: usando ROWS

Se você só precisa incluir algumas linhas antes da atual, melhor especificar isso com 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;

Nesse caso, o PostgreSQL só processa três linhas (duas anteriores + a atual) pra cada linha. Bem mais eficiente do que processar centenas por padrão.

4. Minimizar o número de funções window

Cada função window é processada separadamente pelo PostgreSQL. Se você usar várias funções window, ele pode ordenar pra cada uma, o que deixa tudo mais lento. Mas se os parâmetros do window (tipo PARTITION BY e ORDER BY) forem iguais, o PostgreSQL consegue otimizar melhor.

Exemplo: otimizando com o mesmo window

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;

As duas funções (SUM() e ROW_NUMBER()) usam o mesmo frame. O PostgreSQL faz só uma ordenação — e isso é top.

5. Particionamento de tabelas

Se sua tabela é gigante, vale pensar em dividir ela fisicamente em partes menores. O PostgreSQL permite criar tabelas particionadas, assim os dados ficam em segmentos diferentes. Isso pode acelerar muito o processamento.

Exemplo de criação de tabela particionada

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

Depois disso, você cria as partições, tipo por ano:

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');

Agora, se você usar WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31', o PostgreSQL vai direto só na partição certa.

Mais sobre particionamento de tabelas você vai ver no final do curso :P

6. Evite dados desnecessários (SELECT só o que precisa)

Pega só as colunas que você realmente precisa pra função e pro resultado. Se sua função window só precisa de product_id, sale_date e amount, não puxa a tabela inteira com todos os dados do cliente.

Exemplo de query "econômica"

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

Menos dados — menos trabalho pro PostgreSQL.

7. Usar materialização (MATERIALIZED VIEW)

Se você sempre faz os mesmos cálculos com funções window, pode salvar os resultados numa view materializada. Materialized View guarda os dados no disco, assim você não precisa refazer queries pesadas toda hora.

Exemplo de criação de view 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;

Agora é só consultar os dados:

SELECT * FROM sales_running_total WHERE product_id = 10;

8. Planejar queries usando EXPLAIN e EXPLAIN ANALYZE

Como em outros pontos do SQL, você pode usar EXPLAIN ou EXPLAIN ANALYZE pra entender como o PostgreSQL executa sua query — e onde estão os gargalos.

Exemplo de análise de query

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

Essa ferramenta mostra onde o PostgreSQL gasta mais tempo, e aí você pode otimizar os pontos críticos.

Funções window são uma baita ferramenta pra análise de dados, mas tem que usar com cuidado. Quer velocidade? Use índices, coloque filtros, não esqueça das partições e aproveite as views materializadas. O PostgreSQL adora quando você pensa bem no que tá fazendo!