Néhány hónappal ezelőtt - nyilvános a PostgreSQL-hez.
Már több mint 6000-szer használta, de egy praktikus funkció, amely észrevétlen maradt: szerkezeti nyomok, amelyek valahogy így néznek ki:
Hallgassa meg őket, és kérései „simává és selymessé válnak”. 🙂
De komolyan, sok olyan helyzet, amely lassúvá és erőforrásigényessé teszi a kérést jellemzőek, és a terv szerkezetéről és adatairól felismerhetők.
Ebben az esetben minden egyes fejlesztőnek nem kell egyedül keresnie az optimalizálási lehetőséget, pusztán a saját tapasztalataira hagyatkozva - elmondhatjuk neki, mi történik itt, mi lehet az oka, ill. hogyan kell megközelíteni a megoldást. Mi ezt tettük.
Nézzük meg közelebbről ezeket az eseteket – hogyan határozzák meg őket, és milyen ajánlásokhoz vezetnek.
Ahhoz, hogy jobban elmerüljön a témában, először meghallgathatja a megfelelő blokkot , és csak ezután folytassa az egyes példák részletes elemzését:
#1: index „alulsorolás”
Amikor felmerül
Mutassa meg az "LLC Kolokolchik" ügyfél legújabb számláját.
Hogyan lehet azonosítani
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
Ajánlások
Használt index rendezési mezőkkel bővíteni.
Példa:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key
SELECT
*
FROM
tbl
WHERE
fk_cli = 1 -- отбор по конкретной связи
ORDER BY
pk DESC -- хотим всего одну "последнюю" запись
LIMIT 1;
Azonnal észrevehető, hogy több mint 100 rekordot vontak ki az indexből, amiket aztán mind leválogattak, majd az egyetlen maradt.
Javítás:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Még egy ilyen primitív mintán is 8.5-szer gyorsabb és 33-szor kevesebb olvasás. Minél több „tény” van az egyes értékekhez, annál nyilvánvalóbb a hatás fk.
Megjegyzem, hogy egy ilyen index „előtag” indexként nem rosszabb, mint korábban más lekérdezések esetén fk, ahol rendezés szerint pk nem volt és nincs is (erről bővebben olvashat ). Beleértve a normális explicit idegen kulcs támogatás ezen a mezőn.
#2: index metszéspontja (BitmapAnd)
Amikor felmerül
Mutassa meg az „LLC Kolokolchik” ügyfél számára a „NAO Buttercup” nevében kötött összes megállapodást.
Hogyan lehet azonosítani
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index ScanAjánlások
teremt összetett index mezők szerint mindkét eredeti mezőből, vagy bővítse ki a meglévők egyikét a második mezőivel.
Példa:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 100)::integer fk_org -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_org); -- индекс для foreign key
CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key
SELECT
*
FROM
tbl
WHERE
(fk_org, fk_cli) = (1, 999); -- отбор по конкретной паре
Javítás:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
A megtérülés itt kisebb, mivel a Bitmap Heap Scan önmagában meglehetősen hatékony. De egyébként is 7-szer gyorsabb és 2.5-szor kevesebb olvasás.
#3: Indexek egyesítése (BitmapOr)
Amikor felmerül
Mutassa meg az első 20 legrégebbi „mi” vagy hozzá nem rendelt kérelmet a feldolgozásra, az Önét prioritásként.
Hogyan lehet azonosítani
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index ScanAjánlások
Használat UNIÓ [MINDEN] az egyes feltételblokkokhoz tartozó allekérdezések kombinálásához.
Példa:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, CASE
WHEN random() < 1::real/16 THEN NULL -- с вероятностью 1:16 запись "ничья"
ELSE (random() * 100)::integer -- 100 разных внешних ключей
END fk_own;
CREATE INDEX ON tbl(fk_own, pk); -- индекс с "вроде как подходящей" сортировкой
SELECT
*
FROM
tbl
WHERE
fk_own = 1 OR -- свои
fk_own IS NULL -- ... или "ничьи"
ORDER BY
pk
, (fk_own = 1) DESC -- сначала "свои"
LIMIT 20;
Javítás:
(
SELECT
*
FROM
tbl
WHERE
fk_own = 1 -- сначала "свои" 20
ORDER BY
pk
LIMIT 20
)
UNION ALL
(
SELECT
*
FROM
tbl
WHERE
fk_own IS NULL -- потом "ничьи" 20
ORDER BY
pk
LIMIT 20
)
LIMIT 20; -- но всего - 20, больше и не надо
Kihasználtuk, hogy az első blokkban mind a 20 szükséges rekord azonnal megérkezett, így a másodikat, a „drágább” Bitmap Heap Scannel nem is sikerült végrehajtani – végül 22x gyorsabb, 44x kevesebb olvasás!
Részletesebb történet erről az optimalizálási módszerről konkrét példákon cikkekben olvasható и .
Általánosított változat több kulcs alapján rendezett kiválasztás (és nem csak a const/NULL párt) tárgyalja a cikk .
#4: Sok felesleges dolgot olvasunk
Amikor felmerül
Általános szabály, hogy ez akkor fordul elő, ha egy már létező kérelemhez szeretne „másik szűrőt csatolni”.
„És neked nem ugyanaz van, de gyöngygombokkal? " film "A gyémánt kar"
Például a fenti feladat módosításával jelenítse meg az első 20 legrégebbi „kritikus” feldolgozási kérelmet, függetlenül azok céljától.
Hogyan lehet azonosítani
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
Ajánlások
Hozzon létre [tovább] speciális index WHERE feltétellel vagy vegyen fel további mezőket az indexbe.
Ha a szűrő állapota "statikus" az Ön céljainak megfelelően - az nem jelent bővítést értékek listája a jövőben - jobb a WHERE index használata. Különféle logikai/enum állapotok jól illeszkednek ebbe a kategóriába.
Ha a szűrési feltétel különböző jelentéseket vehet fel, akkor jobb az indexet ezekkel a mezőkkel bővíteni – mint a fenti BitmapAnd esetében.
Példa:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, CASE
WHEN random() < 1::real/16 THEN NULL
ELSE (random() * 100)::integer -- 100 разных внешних ключей
END fk_own
, (random() < 1::real/50) critical; -- 1:50, что заявка "критичная"
CREATE INDEX ON tbl(pk);
CREATE INDEX ON tbl(fk_own, pk);
SELECT
*
FROM
tbl
WHERE
critical
ORDER BY
pk
LIMIT 20;
Javítás:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Mint látható, a szűrés teljesen eltűnt a tervből, és a kérés lett 5-ször gyorsabb.
#5: ritka asztal
Amikor felmerül
Különféle kísérletek saját feladatfeldolgozási sor létrehozására, amikor a táblán lévő rekordok nagyszámú frissítése/törlése nagyszámú „halott” rekordhoz vezet.
Hogyan lehet azonosítani
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Ajánlások
Végezze el rendszeresen manuálisan VÁKUUM [TELJES] vagy kellően gyakori edzést érjen el paramétereinek finomhangolásával, beleértve .
A legtöbb esetben az ilyen problémákat a rossz lekérdezés-összetétel okozza, amikor üzleti logikából hívunk, például a cikkben tárgyaltakhoz .
De meg kell értened, hogy még a VÁKUUM TELE nem mindig segít. Ilyen esetekben érdemes megismerkedni a cikkben szereplő algoritmussal .
#6: Olvasás az index „középéről”.
Amikor felmerül
Úgy tűnik, keveset olvastunk, és minden indexelve volt, és senkit sem szűrtünk ki túlzottan - de így is lényegesen több oldalt olvastunk el, mint szeretnénk.
Hogyan lehet azonosítani
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Ajánlások
Nézze meg alaposan a használt index szerkezetét és a lekérdezésben megadott kulcsmezőket - nagy valószínűséggel az index egy része nincs beállítva. Valószínűleg hasonló indexet kell létrehoznia, de az előtag mezők nélkül vagy .
Példa:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 100)::integer fk_org -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_org, fk_cli); -- все почти как в #2
-- только вот отдельный индекс по fk_cli мы уже посчитали лишним и удалили
SELECT
*
FROM
tbl
WHERE
fk_cli = 999 -- а fk_org не задано, хотя стоит в индексе раньше
LIMIT 20;
Úgy tűnik, minden rendben van, még az index alapján is, de ez valahogy gyanús - a 20 beolvasott rekord mindegyikéből 4 oldal adatot kellett levonnunk, rekordonként 32 KB - nem félkövér? És az index neve tbl_fk_org_fk_cli_idx elgondolkodtató.
Javítás:
CREATE INDEX ON tbl(fk_cli);
Hirtelen - 10-szer gyorsabb, és 4-szer kevésbé olvasható!
A cikkben további példák is találhatók az indexek nem hatékony használatára .
#7: CTE × CTE
Amikor felmerül
Kérésre „kövér” CTE-t szerzett különböző asztalokból, majd úgy döntött, hogy közöttük csinálja JOIN.
Az eset a v12-nél régebbi verziókra vagy a következővel rendelkező kérésekre vonatkozik WITH MATERIALIZED.
Hogyan lehet azonosítani
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
Ajánlások
Gondosan elemezze a kérést - és ? Ha igen, akkor alkalmazza a "szótárt" a hstore/json-ban pontjában leírt modell szerint .
#8: csere lemezre (temp írva)
Amikor felmerül
A nagyszámú rekord egyszeri feldolgozása (rendezése vagy egyedivé tétele) nem fér bele az erre lefoglalt memóriába.
Hogyan lehet azonosítani
-> *
&& temp written > 0Ajánlások
Ha a művelet által használt memória mennyisége nem haladja meg nagymértékben a paraméter megadott értékét , érdemes kijavítani. Azonnal a konfigurációban mindenki számára, vagy át is SET [LOCAL] konkrét kérésre/tranzakcióra.
Példa:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
Javítás:
SET work_mem = '128MB'; -- перед выполнением запроса
Nyilvánvaló okokból, ha csak memóriát használ, és nem lemezt, akkor a lekérdezés sokkal gyorsabban fog végrehajtódni. Ezzel egyidejűleg a HDD terhelésének egy része is megszűnik.
De meg kell értened, hogy nem mindig tudsz sok-sok memóriát lefoglalni – egyszerűen nem lesz elég mindenkinek.
#9: irreleváns statisztikák
Amikor felmerül
Egyszerre sokat öntöttek az adatbázisba, de nem volt idejük elűzni ANALYZE.
Hogyan lehet azonosítani
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10Ajánlások
Végezze el ANALYZE.
Ezt a helyzetet részletesebben a .
#10: „valami elromlott”
Amikor felmerül
Várakozás volt egy versengő kérés által kiszabott zárolásra, vagy nem volt elegendő CPU/hipervizor hardvererőforrás.
Hogyan lehet azonosítani
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
Ajánlások
Használjon külső Megfigyelő-rendszer szerver blokkolásához vagy rendellenes erőforrás-felhasználásához. Korábban már beszéltünk arról, hogy miként ezt a folyamatot több száz szerverre szervezzük и .
Forrás: will.com