Jak optimalizovat pomalé agregace nad velkou tabulkou?

Question

IT Interview Practice · Accepted Answer

Při optimalizaci pomalé agregace nejprve zjistěte, **co výsledek znamená a která operace spotřebovává čas**. Agregace spojuje řádky do souhrnu, například tržeb podle obchodníka. Její cena závisí na množství čtených a slučovaných dat.

Uvažujme přehled tržeb za minulý měsíc. Na tomto příkladu rozhodněte, zda zlepšit dotaz, ukládat připravený souhrn, nebo přesunout reporting jinam.

## 1. Před zrychlením potvrďte správnost

Vyjasněte, zda tržby znamenají zaplacené, nebo vytvořené objednávky, jak se započítávají vratky a které měny jsou zahrnuty. Určete také požadovanou čerstvost přehledu. Tyto volby mění dotaz i možnosti optimalizace.

Častou chybou je spojit platbu s několika položkami objednávky a potom sečíst částku platby:

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

Čísla jsou ilustrační. Dotaz platbu znásobil, takže zrychlení pouze rychleji vrátí nesprávný součet. Před měřením výkonu agregujte na zamýšlené obchodní úrovni nebo upravte spojení.

## 2. Najděte drahou část provádění

Plán provádění ukazuje operace vedoucí k výsledku. Prověřte náročné kroky a velikosti jejich vstupů místo předpokladu, že tabulka potřebuje další index.

| Pozorování | Otázka k prověření |
| --- | --- |
| Čte se mnoho nepotřebných řádků | Lze je vyloučit filtrem nebo vynecháním oddílů? |
| Skutečné počty řádků se výrazně liší od odhadů | Odpovídají statistiky těmto datům? |
| Spojení mnohokrát opakuje práci | Je vstup příliš velký nebo přístupová cesta nevhodná? |
| Seskupování či řazení zapisuje dočasná data na disk | Lze zmenšit vstup před změnou paměťových limitů? |

V PostgreSQL příkaz EXPLAIN (ANALYZE, BUFFERS) dotaz skutečně spustí a vypíše údaje o běhu. Náročné zkoumání provádějte v řízeném prostředí nebo s vědomě stanoveným rozpočtem produkčních zdrojů. [PostgreSQL EXPLAIN](https://www.postgresql.org/docs/current/using-explain.html).

## 3. Omezte práci v současné databázi

Pro přehled vyberte potřebný měsíc a sloupce. Zjistěte, zda plán opravdu přeskakuje nesouvisející oddíly a zda spojení nepřináší data, která souhrn nepotřebuje.

Index pomáhá, když ušetří dostatečné čtení. Jestliže report potřebuje většinu řádků, může být sekvenční průchod vhodný. Posuzujte výsledný plán a naměřenou cenu, ne pouhou přítomnost indexu.

Při přetékání řazení na disk může pomoci menší vstup. Zvýšení paměti pro každý dotaz naopak může při souběhu reportů vyčerpat RAM. Testujte s reprezentativní souběžnou zátěží.

## 4. Rozhodněte o předpočítání výsledku

Pokud mnoho uživatelů žádá stejné součty a zpoždění je přijatelné, opakované procházení původních objednávek plýtvá prací. Souhrnná tabulka může ukládat součty podle dne, obchodníka a měny; přehled pak spojuje menší výsledky.

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

Úspora vzniká tím, že společný výpočet proběhne jednou místo při každém požadavku. Materializovaný pohled je další způsob ukládání výsledku dotazu s vlastními pravidly obnovování.

Úspora však přináší povinnosti:

- Určete četnost obnovy a uživateli ukažte relevantní čerstvost výsledku.
- Zpracujte vratky, opožděné objednávky a opravy bez dvojího započtení.
- Zajistěte bezpečné opakování zpracování a možnost souhrny přepočítat.
- Při slučování průměrů ukládejte součet i počet; průměr průměrů může být chybný.

Pro tento přehled jsou předpočítané souhrny vhodné pouze tehdy, když jejich čerstvost a opravy splní požadavek z prvního kroku.

## 5. Oddělte reporting, když to odůvodní měření

Pokud rozsáhlé analytické průchody zůstávají drahé a ruší nákupy, zvažte analytické úložiště vhodné pro tuto práci. Zahrňte zpoždění synchronizace, obnovu a provozní cenu dalšího systému.

Čtecí replika může izolovat reportingovou zátěž, ale stejný dotaz na ní může být stále drahý. Oddělení mění místo nákladů; nemusí je odstranit.

Každou změnu porovnejte s důvěryhodným výsledkem při stejné zátěži. Sledujte odezvu, zdroje, čerstvost a dopad na zápisy.

**Zapamatujte si:** správný výsledek → změřené úzké místo → méně zbytečné práce → vhodné souhrny → případně oddělená analytika.

Pozorování	Otázka k prověření
Čte se mnoho nepotřebných řádků	Lze je vyloučit filtrem nebo vynecháním oddílů?
Skutečné počty řádků se výrazně liší od odhadů	Odpovídají statistiky těmto datům?
Spojení mnohokrát opakuje práci	Je vstup příliš velký nebo přístupová cesta nevhodná?
Seskupování či řazení zapisuje dočasná data na disk	Lze zmenšit vstup před změnou paměťových limitů?

1. Před zrychlením potvrďte správnost

2. Najděte drahou část provádění

3. Omezte práci v současné databázi

4. Rozhodněte o předpočítání výsledku

5. Oddělte reporting, když to odůvodní měření

Související otázky