Hoe optimaliseer je trage aggregaties op een grote tabel?

Question

IT Interview Practice · Accepted Answer

Bepaal bij een trage aggregatie eerst **wat het resultaat betekent en welke bewerking de tijd verbruikt**. Een aggregatie combineert rijen tot een samenvatting, zoals omzet per verkoper. De kosten hangen af van hoeveel data moet worden gelezen en gecombineerd.

Denk aan een dashboard met de omzet van vorige maand. Gebruik dit voorbeeld om te kiezen tussen de query verbeteren, een vooraf berekende samenvatting opslaan of rapportage elders uitvoeren.

## 1. Bevestig het antwoord voordat je het versnelt

Verduidelijk of omzet betaalde of aangemaakte bestellingen betekent, hoe terugbetalingen meetellen en welke valuta zijn inbegrepen. Vraag ook hoe actueel het dashboard moet zijn. Dit verandert zowel de query als de mogelijke optimalisaties.

Een veelgemaakte fout is een betaling koppelen aan meerdere bestelregels en daarna het betalingsbedrag optellen:

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

De getallen zijn voorbeelden. De query heeft de betaling vermenigvuldigd; versnellen levert dus alleen sneller een onjuist totaal op. Aggregeer op het bedoelde bedrijfsniveau of wijzig de join voordat je de prestaties beoordeelt.

## 2. Zoek het dure deel van de uitvoering

Een uitvoeringsplan toont de bewerkingen waarmee het resultaat wordt verkregen. Bekijk dure stappen en hun invoergrootte in plaats van aan te nemen dat de tabel alleen een extra index nodig heeft.

| Waarneming | Te onderzoeken vraag |
| --- | --- |
| Veel irrelevante rijen worden gelezen | Kunnen filters of partition pruning deze uitsluiten? |
| Werkelijke aantallen wijken sterk af van schattingen | Zijn de statistieken representatief voor deze data? |
| Een join herhaalt veel werk | Is de invoer te groot of het toegangspad ongeschikt? |
| Groeperen of sorteren schrijft tijdelijke data naar schijf | Kan de invoer worden verkleind voordat geheugenlimieten worden verhoogd? |

In PostgreSQL voert EXPLAIN (ANALYZE, BUFFERS) de query uit en rapporteert het uitvoeringsinformatie. Doe kostbaar onderzoek in een gecontroleerde omgeving of met een bewust gekozen productiebudget. [PostgreSQL EXPLAIN](https://www.postgresql.org/docs/current/using-explain.html).

## 3. Verminder werk in de huidige database

Selecteer voor het dashboard de vereiste maand en kolommen. Controleer of het plan daadwerkelijk ongerelateerde partities overslaat en of joins data toevoegen die de samenvatting niet nodig heeft.

Een index is nuttig als hij voldoende leeswerk voorkomt. Als het rapport de meeste rijen nodig heeft, kan een sequentiële scan passend zijn. Beoordeel het resulterende plan en de gemeten kosten, niet alleen of er een index wordt gebruikt.

Als sorteren naar schijf uitwijkt, kan minder invoer het probleem oplossen. Meer geheugen per query kan juist het RAM uitputten wanneer veel rapporten tegelijk draaien. Test met representatieve gelijktijdige belasting.

## 4. Beslis of je het resultaat vooraf berekent

Als veel gebruikers dezelfde totalen opvragen en enige vertraging acceptabel is, verspilt herhaald scannen van ruwe bestellingen werk. Een samenvattingstabel kan totalen per dag, verkoper en valuta bewaren; het dashboard combineert deze kleinere resultaten.

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

Het voordeel ontstaat doordat gedeelde berekeningen één keer plaatsvinden in plaats van bij elk verzoek. Een materialized view bewaart eveneens een queryresultaat, met een eigen verversingsmechanisme.

Die besparing brengt verantwoordelijkheden mee:

- Bepaal hoe vaak resultaten verversen en toon gebruikers hoe actueel ze zijn.
- Verwerk terugbetalingen, late bestellingen en correcties zonder dubbeltellingen.
- Maak herhaalde verwerking veilig en behoud een manier om samenvattingen opnieuw op te bouwen.
- Bewaar som en aantal wanneer je gemiddelden combineert; het gemiddelde van gemiddelden kan onjuist zijn.

Voor dit dashboard zijn vooraf berekende samenvattingen alleen geschikt als hun actualiteit en correctieregels voldoen aan de eis uit stap één.

## 5. Scheid rapportage als metingen dat rechtvaardigen

Als brede analytische scans duur blijven en aankopen verstoren, beoordeel dan een analytische datastore voor die belasting. Neem synchronisatievertraging, herstel en de beheerkosten van een extra systeem mee.

Een leesreplica kan rapportagebelasting isoleren, maar dezelfde query kan daar nog steeds duur zijn. Scheiding verplaatst de kosten; ze verdwijnen niet noodzakelijk.

Vergelijk iedere wijziging met een betrouwbaar resultaat en dezelfde testbelasting. Controleer responstijd, middelen, actualiteit en invloed op schrijfbewerkingen.

**Onthoud:** correct resultaat → gemeten knelpunt → minder onnodig werk → vooraf berekende samenvattingen indien passend → aparte analyse als dat nog nodig is.

Waarneming	Te onderzoeken vraag
Veel irrelevante rijen worden gelezen	Kunnen filters of partition pruning deze uitsluiten?
Werkelijke aantallen wijken sterk af van schattingen	Zijn de statistieken representatief voor deze data?
Een join herhaalt veel werk	Is de invoer te groot of het toegangspad ongeschikt?
Groeperen of sorteren schrijft tijdelijke data naar schijf	Kan de invoer worden verkleind voordat geheugenlimieten worden verhoogd?

1. Bevestig het antwoord voordat je het versnelt

2. Zoek het dure deel van de uitvoering

3. Verminder werk in de huidige database

4. Beslis of je het resultaat vooraf berekent

5. Scheid rapportage als metingen dat rechtvaardigen

Gerelateerde vragen