Ottimizzazione delle query con le funzioni window

Rimane ancora un dettaglio importante di cui non abbiamo parlato: le performance delle query con le funzioni window. Anche la query più elegante può diventare una tartaruga se non pensi all’ottimizzazione. Oggi ci occupiamo proprio di questo!

Le funzioni window sono super flessibili e potenti. Ma questa flessibilità è sia un regalo che una potenziale minaccia per le performance. PostgreSQL, purtroppo, non funziona per magia: ha bisogno di risorse per processare i dati. E se ci butti dentro funzioni window su tabelle enormi, la tua query rischia di fare una maratona sul posto.

L’ottimizzazione ti permette di:

• Velocizzare le query che lavorano con grandi quantità di dati.
• Minimizzare il carico sul database.
• Rendere le tue query più amichevoli per il server (e per i colleghi, se lavorano anche loro sul db!).

Dai, tuffiamoci e vediamo cosa si può fare per far volare le tue query come una macchina da corsa all’autodromo.

Basi del funzionamento delle funzioni window

Prima di ottimizzare, meglio capire cosa rallenta la nostra query. PostgreSQL lavora con le funzioni window così:

1. Ordina i dati se nella query c’è ORDER BY dentro OVER().
2. Processa ogni riga all’interno della finestra o gruppo specificato.
3. Restituisce il risultato per ogni riga.

Ora immagina di avere una tabella sales con 10 milioni di righe. Se nella tua query non ci sono filtri, PostgreSQL processerà ognuna di queste righe. Non è più una maratona: è un tapis roulant infinito.

Come velocizzare le funzioni window?

1. Usare gli indici per velocizzare l’ordinamento

La maggior parte delle funzioni window usa ORDER BY dentro OVER() per gestire l’ordine delle righe. Questo significa che PostgreSQL deve ordinare i dati prima di eseguire la funzione window.

Se hai un indice sulla colonna (o sulle colonne) usate in ORDER BY, PostgreSQL può velocizzare di molto questo ordinamento.

Esempio

CREATE INDEX idx_sales_date ON sales (sale_date);

Ora, se scrivi una query con ordinamento su sale_date, l’indice entra in gioco:

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

Senza indice su sale_date, ogni esecuzione della query comporta un ordinamento costoso — PostgreSQL andrà in panico cercando di ordinare le righe più velocemente.

2. Applicare filtri con WHERE

Restringere la quantità di dati è una tecnica chiave di ottimizzazione. Se non ti servono tutte e 10 milioni di righe, ma solo quelle dell’ultimo anno — restringi il range con WHERE!

Esempio

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

È come filtrare l’acqua sporca con un setaccio per tenere solo le informazioni utili.

3. Scegliere il frame window giusto

Quando lavori con funzioni window di aggregazione, tipo SUM(), è importante scegliere il frame giusto. Se usi il frame di default (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), PostgreSQL includerà tutte le righe fino a quella corrente. Questo può essere poco ottimale su tabelle grandi.

Esempio: usare ROWS

Se ti servono solo un paio di righe prima di quella corrente, meglio specificarlo 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;

In questo caso PostgreSQL processa solo tre righe (due precedenti + quella corrente) per ogni riga. Molto più efficiente che processarne centinaia di default.

4. Minimizzare il numero di funzioni window

Ogni funzione window viene processata da PostgreSQL separatamente. Se usi più funzioni window, PostgreSQL potrebbe fare l’ordinamento per ognuna, rallentando tutto. Però, se i parametri della window (tipo PARTITION BY e ORDER BY) sono uguali, PostgreSQL può ottimizzare e riutilizzare l’ordinamento.

Esempio: ottimizzazione con la stessa 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;

Entrambe le funzioni (SUM() e ROW_NUMBER()) usano lo stesso frame. PostgreSQL farà un solo ordinamento — e questa è una figata.

5. Partizionamento delle tabelle

Se la tua tabella è troppo grande, pensa a dividerla fisicamente in parti più piccole. PostgreSQL permette di creare tabelle partizionate, così i dati finiscono in segmenti diversi. Questo può velocizzare di molto l’elaborazione.

Esempio di creazione di una tabella partizionata

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

Dopo, si creano le partizioni, ad esempio per anno:

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

Ora, se usi WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31', PostgreSQL accederà solo alla partizione giusta.

Scoprirai di più sul partizionamento delle tabelle verso la fine del corso :P

6. Evita dati inutili (SELECT solo quello che serve)

Scegli solo le colonne che servono per la funzione e il risultato. Se la tua funzione window ha bisogno solo di product_id, sale_date e amount, non portarti dietro tutto lo zoo della tabella con i bio-dati del cliente.

Esempio di query “risparmiosa”

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

Meno dati — meno lavoro per PostgreSQL.

7. Usare la materializzazione (MATERIALIZED VIEW)

Se fai spesso gli stessi calcoli con le funzioni window, puoi salvare i risultati in una materialized view. La Materialized View salva i dati su disco, così eviti di rifare ogni volta query pesanti.

Esempio di creazione di una materialized view

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;

Ora puoi semplicemente chiedere i dati:

SELECT * FROM sales_running_total WHERE product_id = 10;

8. Pianificare le query con EXPLAIN e EXPLAIN ANALYZE

Come in altri aspetti di SQL, puoi usare EXPLAIN o EXPLAIN ANALYZE per capire come PostgreSQL esegue la tua query — e dove si nascondono i colli di bottiglia.

Esempio di analisi delle query

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

Questo strumento ti mostra dove PostgreSQL perde più tempo, così puoi ottimizzare i punti critici.

Le funzioni window sono uno strumento potentissimo per l’analisi dei dati, ma vanno usate con attenzione. Vuoi velocità? Scegli gli indici, aggiungi filtri, non dimenticare le partizioni e non aver paura di usare le materialized view. PostgreSQL adora quando lo usi con criterio!