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 raporttini PGConf.Russia 2020 -tapahtumassaja siirry vasta sitten kunkin esimerkin yksityiskohtaiseen analyysiin:
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;
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ä artikkelissani tehottomien indeksien löytämisestä). 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 Scan
Suositukset
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); -- отбор по конкретной паре
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 Scan
Suositukset
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;
(
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!
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;
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 automaattinen tyhjiö hienosäätämällä sen parametreja, mukaan lukien tietylle pöydälle.
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 oppia toistamaan arvojaan.
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.
Suuren tietuemäärän kertaluonteinen käsittely (lajittelu tai yksilöiminen) ei mahdu tähän varattuun muistiin.
Kuinka tunnistaa
-> *
&& temp written > 0
Suositukset
Jos toiminnon käyttämän muistin määrä ei ylitä suuresti parametrin asetettua arvoa work_mem, 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;
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. täällä и täällä.