Hoiduge toimingute eest, mis toovad puhvreid...
Kasutades näitena väikest päringut, vaatame mõningaid universaalseid lähenemisviise päringute optimeerimiseks PostgreSQL-is. See, kas te neid kasutate või mitte, on teie otsustada, kuid tasub neist teada.
Mõnes järgnevas PG versioonis võib olukord ajakava targemaks muutudes muutuda, kuid 9.4/9.6 puhul näeb see välja ligikaudu sama, nagu siinsetes näidetes.
Võtame väga tõelise palve:
SELECT
TRUE
FROM
"Документ" d
INNER JOIN
"ДокументРасширение" doc_ex
USING("@Документ")
INNER JOIN
"ТипДокумента" t_doc ON
t_doc."@ТипДокумента" = d."ТипДокумента"
WHERE
(d."Лицо3" = 19091 or d."Сотрудник" = 19091) AND
d."$Черновик" IS NULL AND
d."Удален" IS NOT TRUE AND
doc_ex."Состояние"[1] IS TRUE AND
t_doc."ТипДокумента" = 'ПланРабот'
LIMIT 1;
tabelite ja väljade nimede kohtaPõldude ja tabelite “venekeelseid” nimetusi võib käsitleda erinevalt, kuid see on maitse asi. Kuna välismaiseid arendajaid pole ja PostgreSQL võimaldab meil nimesid anda isegi hieroglüüfides, kui need jutumärkides, siis eelistame nimetada objekte üheselt ja selgelt, et ei tekiks lahknevusi.
Vaatame saadud plaani:
144 ms ja peaaegu 53K puhvrid - see tähendab rohkem kui 400 MB andmemahtu! Ja meil veab, kui need kõik on meie päringu ajaks vahemälus, muidu läheb kettalt lugemisel kordades kauem aega.
Algoritm on kõige tähtsam!
Mis tahes päringu kuidagi optimeerimiseks peate kõigepealt mõistma, mida see peaks tegema.
Jätkem andmebaasistruktuuri enda arendamine praegu sellest artiklist väljapoole ja lepime kokku, et saame suhteliselt "odavalt" kirjutage taotlus ümber ja/või veereme alusele mõned asjad, mida vajame Indexes.
Seega palve:
— kontrollib vähemalt mõne dokumendi olemasolu
- meile vajalikus seisukorras ja teatud tüüpi
- kus autor või esitaja on meile vajalik töötaja
LIITU + LIIT 1
Üsna sageli on arendajal lihtsam kirjutada päring, kus esmalt liidetakse suur hulk tabeleid ja siis jääb kogu sellest komplektist alles vaid üks kirje. Kuid arendajale lihtsam ei tähenda andmebaasi jaoks tõhusamat.
Meie puhul oli ainult 3 tabelit - ja milline on mõju...
Vabaneme esmalt ühendusest tabeliga "Dokumendi tüüp" ja samal ajal ütleme andmebaasile, et meie tüübikirje on ainulaadne (me teame seda, kuid planeerijal pole veel aimugi):
WITH T AS (
SELECT
"@ТипДокумента"
FROM
"ТипДокумента"
WHERE
"ТипДокумента" = 'ПланРабот'
LIMIT 1
)
...
WHERE
d."ТипДокумента" = (TABLE T)
...Jah, kui tabel/CTE koosneb ühe kirje ühest väljast, siis PG-s saab kirjutada isegi nii, mitte
d."ТипДокумента" = (SELECT "@ТипДокумента" FROM T LIMIT 1)Laisk hindamine PostgreSQL päringutes
BitmapOr vs UNION
Mõnel juhul läheb Bitmap Heap Scan meile palju maksma – näiteks meie olukorras, kui päris palju kirjeid vastavad nõutavale tingimusele. Saime selle, sest VÕI tingimus muudeti BitmapOr-iks- tegevus plaanis.
Pöördume tagasi algse probleemi juurde – peame leidma vastava kirje keegi tingimustest - see tähendab, et mõlemal tingimusel pole vaja otsida kõiki 59K kirjeid. On olemas viis ühe tingimuse väljatöötamiseks ja minge teise juurde alles siis, kui esimesest midagi ei leitud. Järgmine disain aitab meid:
(
SELECT
...
LIMIT 1
)
UNION ALL
(
SELECT
...
LIMIT 1
)
LIMIT 1“Väline” LIMIT 1 tagab, et otsing lõpeb, kui leitakse esimene kirje. Ja kui see on juba esimesest plokist leitud, siis teist plokki ei käivitata (pole kunagi hukatud suhtes).
"Keeruliste tingimuste peitmine CASE-i all"
Algses päringus on äärmiselt ebamugav hetk - oleku kontrollimine seotud tabeliga “DocumentExtension”. Sõltumata avaldise muude tingimuste õigsusest (näiteks d.“Kustutatud” EI OLE TÕE), see ühendus täidetakse alati ja see "kulub ressursse". Enam-vähem neist kulub – oleneb selle tabeli suurusest.
Kuid saate päringut muuta nii, et seotud kirje otsimine toimuks ainult siis, kui see on tõesti vajalik:
SELECT
...
FROM
"Документ" d
WHERE
... /*index cond*/ AND
CASE
WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
SELECT
"Состояние"[1] IS TRUE
FROM
"ДокументРасширение"
WHERE
"@Документ" = d."@Документ"
)
END
Ükskord lingitud tabelist meile tulemuse jaoks pole vaja ühtegi välja, siis on meil võimalus muuta JOIN alampäringu tingimuseks.
Jätame indekseeritud väljad “CASE-sulgudest väljapoole”, lisame kirjest lihtsad tingimused plokki WHEN - ja nüüd täidetakse “raske” päring ainult siis, kui minnakse üle TEHNI.
Minu perekonnanimi on "Kokku"
Kogume saadud päringu kõigi ülalkirjeldatud mehaanikatega:
WITH T AS (
SELECT
"@ТипДокумента"
FROM
"ТипДокумента"
WHERE
"ТипДокумента" = 'ПланРабот'
)
(
SELECT
TRUE
FROM
"Документ" d
WHERE
("Лицо3", "ТипДокумента") = (19091, (TABLE T)) AND
CASE
WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
SELECT
"Состояние"[1] IS TRUE
FROM
"ДокументРасширение"
WHERE
"@Документ" = d."@Документ"
)
END
LIMIT 1
)
UNION ALL
(
SELECT
TRUE
FROM
"Документ" d
WHERE
("ТипДокумента", "Сотрудник") = ((TABLE T), 19091) AND
CASE
WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
SELECT
"Состояние"[1] IS TRUE
FROM
"ДокументРасширение"
WHERE
"@Документ" = d."@Документ"
)
END
LIMIT 1
)
LIMIT 1;Indeksite [vastavalt] kohandamine
Treenitud silm märkas, et UNION-i alamplokkide indekseeritud tingimused on pisut erinevad – seda seetõttu, et meil on juba tabelis sobivad indeksid. Ja kui neid poleks, tasuks luua: Dokument (isik3, dokumendi tüüp) и Dokument (dokumendi tüüp, töötaja).
väljade järjekorra kohta ROW tingimustesPlaneerija seisukohalt võib muidugi kirjutada (A, B) = (constA, constB)Ja (B, A) = (constB, constA). Aga salvestamisel indeksi väljade järjekorras, on sellist taotlust hiljem lihtsalt mugavam siluda.
Mis on plaanis?
Kahjuks meil ei vedanud ja esimesest UNION-i plokist midagi ei leitud, nii et teine siiski hukati. Kuid isegi nii - ainult 0.037 ms ja 11 puhvrit!
Oleme päringu esitamist kiirendanud ja vähendanud andmete mällu pumpamist mitu tuhat korda, kasutades üsna lihtsaid võtteid – hea tulemus vähese copy-paste’ga. 🙂
Allikas: www.habr.com