Kuukausia sitten - julkinen PostgreSQL:ään.
Olet käyttänyt sitä jo yli 6000 XNUMX kertaa sen jälkeen, mutta yksi kätevistä ominaisuuksista on ehkä jäänyt huomaamatta rakenteellisia vihjeitä, jotka näyttävät suunnilleen tältä:
Kuuntele niitä ja pyyntösi "tulevat silkinpehmeiksi". 🙂
Mutta vakavasti, monet tilanteet, jotka tekevät pyynnön hitaaksi ja "ahmattimaksi" resurssien suhteen, ovat tyypillisiä ja ne voidaan tunnistaa suunnitelman rakenteesta ja tiedoista.
Tässä tapauksessa jokaisen yksittäisen kehittäjän ei tarvitse etsiä optimointivaihtoehtoa yksinään luottaen vain omaan kokemukseensa - voimme kertoa hänelle, mitä täällä tapahtuu, mikä voisi olla syy ja kuinka keksiä ratkaisu. Mitä me teimme.
Tarkastellaanpa näitä tapauksia tarkemmin – miten ne määritellään ja mihin suosituksiin ne johtavat.
Jos haluat syventyä paremmin aiheeseen, voit ensin kuunnella vastaavan lohkon ja siirry vasta sitten kunkin esimerkin yksityiskohtaiseen analyysiin:
#1: hakemisto "alilajittelu"
Kun syntyy
Näytä viimeinen lasku asiakkaalle "LLC Kolokolchik".
Kuinka tunnistaa
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
Suositukset
Indeksi käytetty laajenna lajittelukentillä.
Esimerkiksi:
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;
Voit heti huomata, että hakemistosta vähennettiin yli 100 tietuetta, jotka sitten kaikki lajiteltiin ja sitten jäi ainoa.
Korjaamme:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Jopa niin primitiivisellä näytteellä - 8.5x nopeampi ja 33x vähemmän lukua. Vaikutus on selkeämpi, mitä enemmän "faktoja" sinulla on jokaisesta arvosta. fk.
Huomaan, että tällainen indeksi toimii "etuliite"-indeksinä, joka ei ole huonompi kuin edellinen muille kyselyille fk, jossa lajittelun mukaan pk ei ollut eikä ole (voit lukea lisää tästä ). Erityisesti se tarjoaa normaalia eksplisiittisen vieraan avaimen tuki tällä alalla.
#2: Indeksin leikkauspiste (BitmapAnd)
Kun syntyy
Näytä kaikki sopimukset asiakkaalle "LLC Kolokolchik", jotka on tehty "NJSC Lyutik" puolesta.
Kuinka tunnistaa
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index ScanSuositukset
luoda yhdistetty indeksi kenttien mukaan molemmista lähteistä tai laajentaa yhtä olemassa olevista kentistä toisesta.
Esimerkiksi:
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); -- отбор по конкретной паре
Korjaamme:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
Tässä voitto on pienempi, koska Bitmap Heap Scan on varsin tehokas yksinään. Mutta joka tapauksessa 7x nopeampi ja 2.5x vähemmän lukua.
#3: Indeksien yhdistäminen (BitmapOr)
Kun syntyy
Näytä ensimmäiset 20 vanhinta "omaa" tai määrittämätöntä käsittelypyyntöä, omalla etusijalla.
Kuinka tunnistaa
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index ScanSuositukset
Käytä UNIONI [KAIKKI] yhdistää alikyselyt jokaiselle ehto TAI -lohkolle.
Esimerkiksi:
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;
Korjaamme:
(
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, больше и не надо
Käytimme hyväksemme sitä, että kaikki 20 tarvittavaa tietuetta vastaanotettiin heti ensimmäisessä lohkossa, joten toista, "kallimmalla" Bitmap Heap Scan -skannauksella, ei edes suoritettu - seurauksena 22x nopeampi, 44x vähemmän lukua!
Tarkempi tarina tästä optimointimenetelmästä konkreettisilla esimerkeillä voi lukea artikkeleista и .
Yleistetty versio tilattu valinta useilla näppäimillä (eikä vain parille const / NULL) käsitellään artikkelissa .
#4: Luemme liikaa
Kun syntyy
Yleensä se tapahtuu, kun haluat "liittää toisen suodattimen" olemassa olevaan pyyntöön.
"Eikä sinulla ole samaa, mutta helminäppäimillä? " elokuva "Timanttikäsi"
Jos esimerkiksi muokkaat yllä olevaa tehtävää, näytä ensimmäiset 20 vanhinta "kriittistä" käsittelypyyntöä niiden tarkoituksesta riippumatta.
Kuinka tunnistaa
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
Suositukset
Luo [lisää] erikoistuneita hakemisto WHERE-lauseella tai sisällytä hakemistoon lisäkenttiä.
Jos suodatusehto on "staattinen" tehtävillesi - se on ei sisällä laajennusta arvoluettelo tulevaisuudessa - on parempi käyttää WHERE-indeksiä. Erilaiset boolean/enum-tilat sopivat hyvin tähän luokkaan.
Jos suodatusolosuhteet voi saada erilaisia arvoja, on parempi laajentaa hakemistoa näillä kentillä - kuten edellä BitmapAndin tapauksessa.
Esimerkiksi:
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;
Korjaamme:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Kuten näet, suodatus suunnitelmasta on kokonaan poissa ja pyyntö on tullut 5 kertaa nopeampi.
#5: harva pöytä
Kun syntyy
Erilaisia yrityksiä tehdä oma tehtävänkäsittelyjono, kun suuri määrä tietueiden päivityksiä/poistoja taulukossa johtaa tilanteeseen, jossa suuri määrä "kuolleita" tietueita.
Kuinka tunnistaa
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Suositukset
Suorita manuaalisesti säännöllisesti IMPI [TÄYSI] tai saavuttaa riittävän tiheän käsittelyn hienosäätämällä sen parametreja, mukaan lukien .
Useimmissa tapauksissa tällaiset ongelmat johtuvat huonosta kyselyn asettelusta, kun niitä kutsutaan liiketoimintalogiikasta, kuten niistä, joita käsitellään .
Mutta meidän on ymmärrettävä, että edes VACUUM FULL ei voi aina auttaa. Tällaisissa tapauksissa sinun tulee tutustua artikkelin algoritmiin. .
#6: lukeminen indeksin "keskeltä".
Kun syntyy
Näyttää siltä, että he lukivat vähän ja kaikki indeksoitiin, eivätkä he suodattaneet ketään ylimääräistä - mutta silti luettiin huomattavasti enemmän sivuja kuin haluaisimme.
Kuinka tunnistaa
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Suositukset
Tarkastele tarkasti käytetyn indeksin rakennetta ja kyselyssä määritettyjä avainkenttiä - todennäköisimmin indeksiosaa ei ole asetettu. Sinun on todennäköisesti luotava samanlainen hakemisto, mutta ilman etuliitekenttiä tai .
Esimerkiksi:
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;
Kaikki näyttää olevan kunnossa, jopa indeksin suhteen, mutta jotenkin epäilyttävää - jokaisesta 20 luetusta tietueesta oli vähennettävä 4 sivua tietoa, 32 kt per tietue - eikö olekin lihavoitu? Kyllä ja indeksin nimi tbl_fk_org_fk_cli_idx johtaa ajatukseen.
Korjaamme:
CREATE INDEX ON tbl(fk_cli);
Yhtäkkiä - 10 kertaa nopeampi ja 4 kertaa vähemmän luettava!
Lisää esimerkkejä indeksien tehottomasta käytöstä on artikkelissa .
#7: CTE × CTE
Kun syntyy
Pyynnöstä teki "rasva" CTE eri pöydistä ja päätti sitten tehdä niiden välillä JOIN.
Tapaus on merkityksellinen versioille, jotka ovat vanhempia v12:ta tai pyyntöjä kanssa WITH MATERIALIZED.
Kuinka tunnistaa
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
Suositukset
Analysoi pyyntö huolellisesti ? Jos kyllä, niin sitten käytä sanakirjaa hstore/jsonissa kohdassa kuvatun mallin mukaan .
#8: Vaihda levylle (kirjoitettu tilapäinen)
Kun syntyy
Suuren tietuemäärän kertaluonteinen käsittely (lajittelu tai yksilöiminen) ei mahdu tähän varattuun muistiin.
Kuinka tunnistaa
-> *
&& temp written > 0Suositukset
Jos toiminnon käyttämän muistin määrä ei ylitä suuresti parametrin asetettua arvoa , se pitäisi korjata. Voit heti kaikille asetuksissa tai voit läpi SET [LOCAL] tiettyä pyyntöä/tapahtumaa varten.
Esimerkiksi:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
Korjaamme:
SET work_mem = '128MB'; -- перед выполнением запроса
Ilmeisistä syistä, jos käytetään vain muistia eikä levyä, kysely on paljon nopeampi. Samalla osa kuormasta poistetaan myös kiintolevyltä.
Mutta sinun on ymmärrettävä, että myös suuren muistin varaaminen ei aina toimi - se ei yksinkertaisesti riitä kaikille.
#9: Epäolennaiset tilastot
Kun syntyy
Alustaan kaadettiin paljon kerralla, mutta he eivät ehtineet ajaa sitä pois ANALYZE.
Kuinka tunnistaa
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10Suositukset
Kuluta saman verran ANALYZE.
Tämä tilanne on kuvattu tarkemmin kohdassa .
#10: "jotain meni pieleen"
Kun syntyy
Lukko odotti kilpailevaa pyyntöä tai CPU/hypervisor-laitteistoresurssit eivät riittäneet.
Kuinka tunnistaa
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
Suositukset
Käytä ulkoista valvontajärjestelmä palvelimen estämistä tai epänormaalia resurssien kulutusta varten. Olemme jo puhuneet versiostamme tämän prosessin järjestämisestä sadoille palvelimille. и .
Lähde: will.com