Come ottimizzare aggregazioni lente su una tabella grande?

Question

IT Interview Practice · Accepted Answer

Per ottimizzare un'aggregazione lenta, identifica prima **il significato del risultato e l'operazione che consuma tempo**. Un'aggregazione combina righe in un riepilogo, come i ricavi per commerciante. Il suo costo dipende dalla quantità di dati da leggere e combinare.

Considera una dashboard con i ricavi del mese scorso. Usa questo esempio per decidere se migliorare la query, memorizzare un riepilogo precalcolato o spostare altrove la reportistica.

## 1. Conferma il risultato prima di renderlo più veloce

Chiarisci se i ricavi si riferiscono agli ordini pagati o creati, come vengono gestiti i rimborsi e quali valute sono incluse. Chiedi anche quanto debba essere aggiornata la dashboard. Queste scelte cambiano sia la query sia le ottimizzazioni disponibili.

Un errore comune consiste nel collegare un pagamento a più articoli dell'ordine e poi sommare l'importo del pagamento:

```text
[Payment: 100] -> [Join with 3 items] -> [100, 100, 100]
                                               |
                                          [SUM = 300]
```

I numeri sono illustrativi. La query ha moltiplicato il pagamento: accelerarla restituirebbe soltanto un totale errato più rapidamente. Aggrega al livello aziendale previsto o modifica il join prima di valutare le prestazioni.

## 2. Trova la parte costosa dell'esecuzione

Un piano di esecuzione mostra le operazioni usate per ottenere il risultato. Controlla i passaggi costosi e la dimensione dei loro input, invece di presumere che serva semplicemente un altro indice.

| Osservazione | Domanda da approfondire |
| --- | --- |
| Vengono lette molte righe irrilevanti | Filtri o esclusione delle partizioni possono evitarle? |
| Il numero effettivo di righe differisce molto dalle stime | Le statistiche rappresentano questi dati? |
| Un join ripete il lavoro molte volte | L'input è troppo grande o il percorso di accesso è inadatto? |
| Raggruppamento o ordinamento scrivono dati temporanei su disco | È possibile ridurre l'input prima di modificare i limiti di memoria? |

In PostgreSQL, EXPLAIN (ANALYZE, BUFFERS) esegue la query e riporta informazioni di esecuzione. Svolgi un'indagine costosa in un ambiente controllato o con un budget di produzione deliberato. [EXPLAIN di PostgreSQL](https://www.postgresql.org/docs/current/using-explain.html).

## 3. Riduci il lavoro nel database attuale

Per la dashboard, seleziona mese e colonne necessari. Controlla se il piano esclude davvero le partizioni estranee e se i join introducono dati inutili al riepilogo.

Un indice è utile quando evita abbastanza letture. Se il report richiede la maggior parte delle righe, una scansione sequenziale può essere appropriata. Valuta il piano risultante e il costo misurato, non soltanto la presenza di un indice.

Se l'ordinamento usa il disco per mancanza di memoria, ridurre l'input può risolvere il problema. Aumentare la memoria per ogni query può invece esaurire la RAM quando molti report vengono eseguiti insieme. Prova con un carico concorrente rappresentativo.

## 4. Decidi se preparare il risultato in anticipo

Se molti utenti richiedono gli stessi totali e un ritardo è accettabile, scandire ripetutamente gli ordini grezzi spreca lavoro. Una tabella di riepilogo può conservare totali per giorno, commerciante e valuta; la dashboard combina questi risultati più piccoli.

```text
[Orders] -> [Scheduled / incremental calculation]
                              |
                       [Daily summaries] -> [Dashboard]
```

Il vantaggio deriva dall'eseguire una volta i calcoli condivisi invece che a ogni richiesta. Una vista materializzata è un altro modo di memorizzare il risultato di una query, con proprie modalità di aggiornamento.

Questo risparmio introduce responsabilità:

- Definisci la frequenza di aggiornamento e mostra all'utente quanto sono aggiornati i dati.
- Applica rimborsi, ordini tardivi e correzioni senza conteggi doppi.
- Rendi sicura l'elaborazione ripetuta e mantieni un modo per ricostruire i riepiloghi.
- Conserva somma e conteggio quando combini medie: fare la media delle medie può essere sbagliato.

Per questa dashboard, i riepiloghi precalcolati sono un'opzione solo se le regole di aggiornamento e correzione soddisfano il requisito del primo passaggio.

## 5. Separa la reportistica quando le misure lo giustificano

Se le scansioni analitiche estese restano costose e interferiscono con gli acquisti, valuta un archivio analitico adatto al carico. Includi ritardo di sincronizzazione, recupero e costo operativo di un altro sistema.

Una replica di lettura può isolare il carico dei report, ma la stessa query può restare costosa anche lì. La separazione cambia dove si paga il costo; non necessariamente lo elimina.

Confronta ogni modifica con un risultato affidabile e lo stesso carico di prova. Controlla tempi di risposta, risorse, aggiornamento dei dati e impatto sulle scritture.

**Da ricordare:** risultato corretto → collo di bottiglia misurato → meno lavoro inutile → riepiloghi precalcolati se adatti → analisi separata se ancora necessaria.

Osservazione	Domanda da approfondire
Vengono lette molte righe irrilevanti	Filtri o esclusione delle partizioni possono evitarle?
Il numero effettivo di righe differisce molto dalle stime	Le statistiche rappresentano questi dati?
Un join ripete il lavoro molte volte	L'input è troppo grande o il percorso di accesso è inadatto?
Raggruppamento o ordinamento scrivono dati temporanei su disco	È possibile ridurre l'input prima di modificare i limiti di memoria?

1. Conferma il risultato prima di renderlo più veloce

2. Trova la parte costosa dell'esecuzione

3. Riduci il lavoro nel database attuale

4. Decidi se preparare il risultato in anticipo

5. Separa la reportistica quando le misure lo giustificano

Domande correlate