Lub hli dhau los - pej xeem rau PostgreSQL.
Koj twb tau siv nws ntau dua 6000 zaug, tab sis ib qho khoom siv tau zoo uas yuav tau mus tsis pom yog cov lus qhia, uas zoo ib yam li no:
Mloog lawv, thiab koj qhov kev thov yuav "ua kom du thiab silky." 🙂
Tab sis qhov tseeb, ntau qhov xwm txheej uas ua rau qhov kev thov qeeb thiab cov peev txheej tshaib plab yog qhov raug thiab tuaj yeem lees paub los ntawm cov qauv thiab cov ntaub ntawv ntawm txoj kev npaj.
Hauv qhov no, txhua tus neeg tsim tawm tsis tas yuav nrhiav kev xaiv kom zoo ntawm nws tus kheej, tso siab rau nws qhov kev paub dhau los - peb tuaj yeem qhia nws tias qhov tshwm sim ntawm no, dab tsi tuaj yeem yog vim li cas, thiab yuav ua li cas mus cuag kev daws teeb meem. Qhov ntawd yog qhov peb tau ua.
Cia peb saib ze dua ntawm cov xwm txheej no - lawv txhais li cas thiab cov lus pom zoo uas lawv coj mus rau.
Txhawm rau kom nkag siab zoo dua rau koj tus kheej hauv lub ncauj lus, koj tuaj yeem ua ntej mloog cov ntawv thaiv los ntawm , thiab tsuas yog tom qab ntawd txav mus rau cov ncauj lus kom ntxaws ntawm txhua qhov piv txwv:
# 1: index "undersorting"
Thaum tshwm sim
Qhia daim ntawv them nqi kawg rau tus neeg siv khoom "LLC Kolokolchik".
Yuav txheeb xyuas li cas
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
tswv yim pom zoo
Index siv nthuav nrog cov teb.
Piv Txwv:
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; 
Koj tuaj yeem pom tam sim ntawd tias ntau tshaj 100 cov ntaub ntawv raug rho tawm ntawm qhov ntsuas, uas tau muab tag nrho cov txheeb cais, thiab tom qab ntawd tsuas yog ib qho xwb.
Kho kom raug:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Txawm nyob rau hauv xws li ib tug primitive qauv - 8.5 npaug nrawm dua thiab 33 npaug tsawg dua nyeem. Qhov ntau "qhov tseeb" koj muaj rau txhua tus nqi, qhov txiaj ntsig pom tseeb dua fk.
Kuv nco ntsoov tias qhov ntsuas no yuav ua haujlwm li "prefix" Performance index tsis phem dua li ua ntej rau lwm cov lus nug nrog fk, qhov twg txheeb pk tsis muaj thiab tsis muaj (koj tuaj yeem nyeem ntxiv txog qhov no ). Xws li, nws yuav muab qhov qub kev txhawb nqa tseem ceeb txawv teb chaws ntawm daim teb no.
# 2: index kev sib tshuam (BitmapAnd)
Thaum tshwm sim
Qhia tag nrho cov lus pom zoo rau cov neeg siv khoom "LLC Kolokolchik", xaus rau sawv cev ntawm "NAO Buttercup".
Yuav txheeb xyuas li cas
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index Scantswv yim pom zoo
tsim Composite Index los ntawm cov teb los ntawm ob qho tib si thawj los yog nthuav ib qho ntawm cov uas twb muaj lawm nrog cov teb los ntawm qhov thib ob.
Piv Txwv:
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); -- отбор по конкретной паре 
Kho kom raug:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
Qhov nyiaj them ntawm no yog me dua, txij li Bitmap Heap Scan tau zoo heev ntawm nws tus kheej. Txawm li cas los xij 7 npaug nrawm dua thiab 2.5 npaug tsawg dua nyeem.
#3: Merge indexes (BitmapOr)
Thaum tshwm sim
Qhia thawj 20 tus laus tshaj plaws "peb" lossis cov ntawv thov tsis tau muab rau kev ua tiav, nrog rau koj qhov tseem ceeb.
Yuav txheeb xyuas li cas
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index Scantswv yim pom zoo
Siv UNION [Txhua] los ua ke subqueries rau txhua qhov OR-blocks ntawm tej yam kev mob.
Piv Txwv:
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;
Kho kom raug:
(
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, больше и не надо 
Peb coj kom zoo dua ntawm qhov tseeb tias tag nrho 20 cov ntaub ntawv yuav tsum tau txais tam sim ntawd hauv thawj ntu, yog li qhov thib ob, nrog qhov ntau "kim" Bitmap Heap Scan, tseem tsis tau ua tiav - thaum kawg 22x sai dua, 44x nyeem tsawg dua!
Ib zaj dab neeg ntxaws ntxiv txog txoj kev ua kom zoo dua no siv cov piv txwv tshwj xeeb tuaj yeem nyeem hauv cov ntawv и .
Generalized version xaj xaiv raws li ntau tus yuam sij (thiab tsis yog tus const/NULL khub) tau tham hauv kab lus .
#4: Peb nyeem ntau yam tsis tsim nyog
Thaum tshwm sim
Raws li txoj cai, nws tshwm sim thaum koj xav "ntxiv lwm lim" rau qhov kev thov uas twb muaj lawm.
“Thiab koj tsis muaj ib qho, tab sis nrog niam-ntawm-pearl nyees khawm? » movie "Lub Pob Zeb Diamond"
Piv txwv li, hloov kho cov hauj lwm saum toj no, qhia thawj 20 qhov qub tshaj plaws "tseem ceeb" thov rau kev ua, tsis hais lawv lub hom phiaj.
Yuav txheeb xyuas li cas
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
tswv yim pom zoo
Tsim [ntxiv] tshwj xeeb index nrog qhov chaw nyob los yog suav nrog cov teb ntxiv hauv qhov ntsuas.
Yog tias lub lim dej yog "static" rau koj lub hom phiaj - yog tsis txhais hais tias expansion daim ntawv teev cov txiaj ntsig yav tom ntej - nws yog qhov zoo dua los siv qhov ntsuas qhov twg. Ntau yam boolean / enum xwm txheej haum zoo rau hauv pawg no.
Yog qhov kev lim dej tuaj yeem ua rau lub ntsiab lus sib txawv, ces nws yog qhov zoo dua los nthuav qhov Performance index nrog cov teb no - zoo li hauv qhov xwm txheej nrog BitmapAnd saum toj no.
Piv Txwv:
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; 
Kho kom raug:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Raws li koj tuaj yeem pom, kev lim dej tau ploj tag nrho los ntawm txoj kev npaj, thiab qhov kev thov tau dhau los 5 lub sij hawm sai dua.
#5: sparse table
Thaum tshwm sim
Ntau qhov kev sim los tsim koj tus kheej cov haujlwm ua haujlwm, thaum ntau qhov hloov tshiab / tshem tawm cov ntaub ntawv ntawm lub rooj ua rau muaj qhov xwm txheej loj ntawm "tuag" cov ntaub ntawv.
Yuav txheeb xyuas li cas
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
tswv yim pom zoo
Ua manually tsis tu ncua VACUUM [FULL] los yog ua tiav kev cob qhia tsis tu ncua txaus los ntawm fine-tuning nws parameters, nrog rau .
Feem ntau, cov teeb meem no tshwm sim los ntawm cov lus nug tsis zoo thaum hu los ntawm kev lag luam logic zoo li cov uas tau tham hauv .
Tab sis koj yuav tsum nkag siab tias txawm tias VACUUM FULL yuav tsis pab. Rau cov xwm txheej zoo li no, nws tsim nyog paub koj tus kheej nrog cov algorithm los ntawm kab lus .
# 6: Nyeem los ntawm "nruab nrab" ntawm qhov ntsuas
Thaum tshwm sim
Nws zoo nkaus li tias peb nyeem me ntsis, thiab txhua yam tau txheeb xyuas, thiab peb tsis tau lim tawm leej twg ntau dhau - tab sis tseem peb nyeem ntau nplooj ntawv ntau dua li peb xav.
Yuav txheeb xyuas li cas
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
tswv yim pom zoo
Ua tib zoo saib cov qauv ntawm qhov ntsuas tau siv thiab cov ntsiab lus tseem ceeb tau teev tseg hauv cov lus nug - feem ntau yuav ib feem ntawm qhov ntsuas tsis tau teeb tsa. Feem ntau koj yuav tau tsim qhov ntsuas zoo sib xws, tab sis tsis muaj qhov ua ntej lossis .
Piv Txwv:
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; 
Txhua yam zoo li zoo, txawm tias raws li qhov ntsuas, tab sis nws yog qhov tsis txaus ntseeg - rau txhua qhov ntawm 20 cov ntaub ntawv nyeem, peb yuav tsum rho tawm 4 nplooj ntawv ntawm cov ntaub ntawv, 32KB ib cov ntaub ntawv - tsis yog qhov ua siab tawv? Thiab lub npe index tbl_fk_org_fk_cli_idx kev xav.
Kho kom raug:
CREATE INDEX ON tbl(fk_cli); 
Suddenly - 10 lub sij hawm sai dua, thiab 4 zaug nyeem tsawg dua!
Lwm cov piv txwv ntawm cov xwm txheej ntawm kev siv tsis zoo ntawm cov indexes tuaj yeem pom hauv kab lus .
#7: CTE × CTE
Thaum tshwm sim
Hauv kev thov tau qhab nia "rog" CTE los ntawm cov rooj sib txawv, thiab tom qab ntawd txiav txim siab ua nws ntawm lawv JOIN.
Cov ntaub ntawv muaj feem xyuam rau cov versions hauv qab v12 lossis thov nrog WITH MATERIALIZED.
Yuav txheeb xyuas li cas
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
tswv yim pom zoo
Ua tib zoo txheeb xyuas qhov kev thov - thiab ? Yog tias muaj, ces siv "dictionary" hauv hstore/json raws li tus qauv piav qhia hauv .
#8: swap rau disk (temp sau)
Thaum tshwm sim
Kev ua haujlwm ib zaug (kev cais lossis kev tsis sib xws) ntawm ntau cov ntaub ntawv tsis haum rau hauv lub cim xeeb faib rau qhov no.
Yuav txheeb xyuas li cas
-> *
&& temp written > 0tswv yim pom zoo
Yog hais tias tus nqi ntawm lub cim xeeb siv los ntawm lub lag luam tsis zoo heev tshaj qhov teev tus nqi ntawm parameter , nws tsim nyog kho nws. Koj tuaj yeem tam sim ntawd hauv config rau txhua tus, lossis koj tuaj yeem dhau los SET [LOCAL] rau ib qho kev thov tshwj xeeb / kev lag luam.
Piv Txwv:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1; 
Kho kom raug:
SET work_mem = '128MB'; -- перед выполнением запроса 
Rau cov laj thawj pom tseeb, yog tias tsuas yog siv lub cim xeeb thiab tsis disk, ces cov lus nug yuav raug tua sai dua. Nyob rau tib lub sijhawm, ib feem ntawm kev thauj khoom los ntawm HDD kuj raug tshem tawm.
Tab sis koj yuav tsum nkag siab tias koj yuav tsis muaj peev xwm faib ntau ntau thiab ntau lub cim xeeb - tsuas yog yuav tsis txaus rau txhua tus.
# 9: Cov txheeb cais tsis cuam tshuam
Thaum tshwm sim
Lawv nchuav ntau rau hauv cov ntaub ntawv ib zaug, tab sis tsis muaj sijhawm los tsav nws mus ANALYZE.
Yuav txheeb xyuas li cas
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10tswv yim pom zoo
Nqa nws tawm ANALYZE.
Qhov xwm txheej no tau piav qhia ntau ntxiv hauv .
# 10: "ib yam dab tsi tsis ncaj ncees lawm"
Thaum tshwm sim
Muaj kev tos rau lub xauv yuam kev los ntawm kev thov sib tw, lossis tsis muaj peev txheej CPU / hypervisor kho vajtse.
Yuav txheeb xyuas li cas
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
tswv yim pom zoo
Siv sab nraud saib xyuas qhov system server rau thaiv lossis siv cov peev txheej txawv txav. Peb twb tau tham txog peb version ntawm kev npaj cov txheej txheem no rau ntau pua servers и .
Tau qhov twg los: www.hab.com
