Analisi degli errori tipici nella creazione di procedure analitiche

Oggi, per concludere questo epico viaggio nel mondo di PL/pgSQL, mettiamolo subito in chiaro: gli errori nelle procedure analitiche sono inevitabili. Perché? Perché nell’analisi si lavora con big data, calcoli complessi e a volte condizioni davvero furbe. Più la query o la procedura è complicata, più sembra un labirinto dove basta un passo falso per ottenere risultati sbagliati.

Per fortuna, la maggior parte degli errori sono tipici e si possono prevedere (e prevenire). Vediamoli uno per uno.

1. Mancanza di indici sui campi chiave

Gli indici sono come il navigatore nel mondo dei database. Se non ci sono, il database deve farsi una passeggiata a piedi su tutte le righe della tabella. Su tabelle piccole si sopporta, ma appena i dati crescono a milioni di righe, le tue query vanno più lente di Windows XP su un Pentium III.

Supponiamo che tu abbia una tabella degli ordini e vuoi calcolare le vendite dell’ultimo mese:

SELECT SUM(order_total)
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '1 month';

Se il campo order_date non ha un indice, PostgreSQL farà una scansione completa della tabella (Seq Scan). E questo è quasi sempre lento.

Soluzione: usa gli indici! Basta questo comando:

CREATE INDEX idx_order_date ON orders (order_date);

Ora PostgreSQL potrà cercare nella tabella per order_date molto più velocemente.

Uso di query inefficienti

Alcune query sembrano belle, ma funzionano come un mattone di cemento al posto di una chiave. Tipo usare subquery che potresti sostituire con un join (JOIN), o filtri inutili.

Invece di questo:

SELECT product_id, SUM(order_total)
FROM orders
WHERE product_id IN (SELECT id FROM products WHERE category = 'electronics')
GROUP BY product_id;

Meglio così:

SELECT o.product_id, SUM(o.order_total)
FROM orders o
JOIN products p ON o.product_id = p.id
WHERE p.category = 'electronics'
GROUP BY o.product_id;

Così PostgreSQL non deve fare una subquery per ogni riga e tutto va molto più veloce.

Struttura sbagliata delle tabelle temporanee

Le tabelle temporanee possono essere uno strumento potente, se le usi con la testa. Ma se ti dimentichi di aggiungere le colonne giuste o gli indici, la tabella temporanea diventa un collo di bottiglia che rallenta tutta la procedura.

Facciamo un esempio. Creiamo una tabella temporanea per calcoli intermedi:

CREATE TEMP TABLE temp_sales AS
SELECT region, SUM(order_total) AS total_sales
FROM orders
GROUP BY region;

Ma poi devi filtrare per la colonna total_sales, e non c’è un indice su quel campo.

Prima di usare una tabella temporanea, pensa a come la userai. Se ti serve filtrare su una colonna, aggiungi un indice:

CREATE INDEX idx_temp_sales_total_sales ON temp_sales (total_sales);

Errori nei calcoli (tipo divisione per zero)

La divisione per zero è un classico dell’analisi. SQL non farà finta di niente: ti blocca la query senza pietà.

Supponiamo che vuoi calcolare il valore medio degli ordini:

SELECT SUM(order_total) / COUNT(*) AS avg_order_value
FROM orders;

Se la tabella orders è vuota, farai una divisione per zero e la query fallirà.

Per evitare il problema, gestisci il caso in cui il contatore è zero:

SELECT
    CASE 
        WHEN COUNT(*) = 0 THEN 0
        ELSE SUM(order_total) / COUNT(*)
    END AS avg_order_value
FROM orders;

Mancanza di logging e controllo dell’esecuzione

Le procedure PL/pgSQL possono essere complesse e fatte di più step: da calcoli intermedi a report finali. Se qualcosa va storto in questa catena, senza logging non saprai mai dove e perché tutto è andato a rotoli.

Supponiamo che stai creando una procedura per calcolare delle metriche, ma ti dimentichi di controllare i dati attesi a ogni step. Così tutta la procedura si schianta quando trova dati inaspettati (tipo tabelle vuote).

Per evitarlo, aggiungi logging a ogni step importante della procedura. Ad esempio:

RAISE NOTICE 'Inizio calcolo vendite';
-- Il tuo codice qui...

RAISE NOTICE 'Modulo % completato con successo', modulo;

Per procedure più complesse, meglio salvare i log in una tabella dedicata:

CREATE TABLE log_analytics (
    log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    log_message TEXT
);

Nella procedura aggiungi:

INSERT INTO log_analytics (log_message)
VALUES ('Procedura completata con successo');

Problemi di performance per mancanza di ottimizzazione

L’ottimizzazione è importante non solo per le query, ma anche per le procedure. Se la procedura viene usata da tanti utenti, può diventare il collo di bottiglia del sistema.

Per esempio, ecco una procedura che ricalcola le metriche per tutte le regioni, anche se ti serve solo una regione:

CREATE OR REPLACE FUNCTION calculate_sales()
RETURNS VOID AS $$
BEGIN
    -- Ricalcolo per tutte le regioni
    INSERT INTO sales_metrics(region, total_sales)
    SELECT region, SUM(order_total)
    FROM orders
    GROUP BY region;
END;
$$ LANGUAGE plpgsql;

Questo crea carico inutile.

Come risolvere? Aggiungi la possibilità di filtrare i dati passando la regione come parametro:

CREATE OR REPLACE FUNCTION calculate_sales(p_region TEXT)
RETURNS VOID AS $$
BEGIN
    INSERT INTO sales_metrics(region, total_sales)
    SELECT region, SUM(order_total)
    FROM orders
    WHERE region = p_region
    GROUP BY region;
END;
$$ LANGUAGE plpgsql;

Ora la procedura non processa dati inutili e la query finisce prima.

Ignorare gli strumenti di analisi delle performance

Strumenti come EXPLAIN ANALYZE sono amici fidati che ti mostrano dove le query rallentano e come sistemarle. Se scrivi una procedura ma non analizzi la sua performance, sei come un programmatore di computer quantistici senza oscilloscopio: sembra che funzioni, ma nessuno sa davvero cosa succede.

Facciamo un esempio. Il problema in questa query si vede subito con EXPLAIN ANALYZE:

SELECT *
FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2023;

Questa query è inefficiente perché la funzione EXTRACT() disattiva l’uso degli indici.

Per risolvere, analizza la query così:

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE order_date >= DATE '2023-01-01' AND order_date < DATE '2024-01-01';

Come evitare gli errori tipici?

Per prevenire errori, segui queste pratiche:

1. Usa indici sui campi che filtri o usi nei join.
2. Ottimizza le query: elimina subquery inutili, usa JOIN.
3. Fai logging dell’esecuzione. Ti aiuterà a fare debug se qualcosa va storto.
4. Controlla sempre le tue procedure con strumenti come EXPLAIN ANALYZE.
5. Hai notato problemi di performance? Pensa a usare partizionamento o a rivedere la logica della query.

Ora hai le conoscenze per prevedere e prevenire errori che potrebbero lasciare i tuoi analisti senza macchina del caffè e senza Wi-Fi a causa di query lente.