Miyezi yapitayo - pagulu ku PostgreSQL.
Mwaigwiritsa ntchito kale kuposa nthawi 6000, koma chinthu chimodzi chothandiza chomwe mwina sichinadziwike zizindikiro structural, zomwe zikuwoneka motere:
Mvetserani, ndipo zopempha zanu “zidzakhala zosalala ndi zosalala.” 🙂
Koma mozama, zinthu zambiri zomwe zimapangitsa kuti pempho likhale lochedwa komanso losowa zofunikira ndizofanana ndipo zimatha kuzindikirika ndi kapangidwe kake ndi deta ya pulaniyo.
Pankhaniyi, wopanga aliyense sayenera kuyang'ana njira yokwaniritsira yekha, kudalira zomwe adakumana nazo - titha kumuuza zomwe zikuchitika pano, chomwe chingakhale chifukwa, komanso momwe mungayankhire yankho. Ndi zomwe tinachita.
Tiyeni tiwone mwatsatanetsatane milandu iyi - momwe imafotokozedwera komanso malingaliro omwe amatsogolera.
Kuti mulowetse bwino pamutuwu, mutha kumvetsera kaye chipika chofananacho , ndipo pokhapo ndikupita kukasanthula mwatsatanetsatane chitsanzo chilichonse:
#1: index "undersorting"
Pamene kutero
Onetsani ma invoice aposachedwa a kasitomala "LLC Kolokolchik".
Momwe mungadziwire
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
ayamikira
Index yogwiritsidwa ntchito onjezerani ndi magawo osiyanasiyana.
Chitsanzo:
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;
Mutha kuzindikira nthawi yomweyo kuti zolemba zopitilira 100 zidachotsedwa pamndandanda, zomwe zidasanjidwa, kenako imodzi yokha idatsala.
Kukonza:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Ngakhale pachitsanzo choyambirira chotere - Nthawi 8.5 mwachangu komanso kuwerengeka kochepera 33. Mukakhala ndi "zowona" zambiri pa mtengo uliwonse, zotsatira zake zimakhala zoonekeratu fk.
Ndikuwona kuti index yotereyi imagwira ntchito ngati "prefix" index osati yoyipa kuposa kale pamafunso ena fk, kumene kusankha pk panalibe ndipo palibe (mutha kuwerenga zambiri za izi ). Kuphatikiza, ipereka zabwinobwino kuthandizira makiyi akunja pamunda uwu.
#2: mphambano ya index (BitmapAnd)
Pamene kutero
Onetsani mapangano onse kwa kasitomala "LLC Kolokolchik", anamaliza m'malo mwa "NAO Buttercup".
Momwe mungadziwire
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index Scanayamikira
kulenga composite index ndi minda kuchokera ku zonse zoyambirira kapena kukulitsa imodzi mwazomwe zilipo ndi minda kuchokera pachiwiri.
Chitsanzo:
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); -- отбор по конкретной паре
Kukonza:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
Zopindulitsa apa ndizochepa, popeza Bitmap Heap Scan ndiyothandiza yokha. Koma mulimonse Nthawi 7 mwachangu komanso kuwerengeka kochepera 2.5.
#3: Phatikizani ma index (BitmapOr)
Pamene kutero
Onetsani zoyamba 20 zoyamba za "ife" kapena zopempha zomwe sizinagawidwe kuti zikonzedwe, ndikuyika zanu patsogolo.
Momwe mungadziwire
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index Scanayamikira
Gwiritsani ntchito UNION [ONSE] kuphatikiza ma subqueries amtundu uliwonse wa OR-blocks.
Chitsanzo:
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;
Kukonza:
(
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, больше и не надо
Tidatengerapo mwayi kuti zolemba zonse 20 zomwe zimafunikira zidalandiridwa nthawi yomweyo mu block yoyamba, kotero yachiwiri, yokhala ndi Bitmap Heap Scan "yokwera mtengo kwambiri, sinaphedwe nkomwe - pamapeto pake. 22x mwachangu, 44x zowerengera zochepa!
Nkhani yowonjezereka ya njira yokwaniritsira iyi pogwiritsa ntchito zitsanzo zenizeni tingawerenge m'nkhani и .
Mtundu wamba analamula kusankha kutengera makiyi angapo (osati ma const/NULL awiri okha) akukambidwa m'nkhaniyi .
#4: Timawerenga zinthu zambiri zosafunikira
Pamene kutero
Monga lamulo, zimachitika pamene mukufuna "kulumikiza fyuluta ina" ku pempho lomwe liripo kale.
“Ndipo mulibe yemweyo, koma ndi mabatani amayi-wa-ngale? " filimu "The Diamond Arm"
Mwachitsanzo, posintha ntchito yomwe ili pamwambapa, wonetsani zopempha 20 zoyambirira "zofunikira" zokonzedwa, mosasamala kanthu za cholinga chake.
Momwe mungadziwire
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
ayamikira
Pangani [zambiri] zapadera index yokhala ndi WHERE kapena phatikizani magawo owonjezera mu index.
Ngati zosefera zili "static" pazolinga zanu - ndiye sizikutanthauza kukulitsa mndandanda wa mfundo za m'tsogolo - ndi bwino ntchito WHERE index. Mitundu yosiyanasiyana ya boolean/enum ikugwirizana bwino ndi gululi.
Ngati sefa chikhalidwe akhoza kukhala ndi matanthauzo osiyanasiyana, ndiye kuti ndi bwino kukulitsa ndondomekoyi ndi minda iyi - monga momwe zilili ndi BitmapAnd pamwamba.
Chitsanzo:
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;
Kukonza:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Monga mukuwonera, kusefa kwasowa kwathunthu pamalingaliro, ndipo pempho lakhala Nthawi 5 mwachangu.
#5: tebulo laling'ono
Pamene kutero
Kuyesera kosiyanasiyana kuti mupange mzere wanu wokonza ntchito, pamene kuchuluka kwa zosintha / kuchotsedwa kwa zolemba patebulo kumapangitsa kuti pakhale zolemba zambiri "zakufa".
Momwe mungadziwire
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
ayamikira
Chitani pamanja pafupipafupi VACUUM [FULL] kapena kupeza maphunziro okwanira pafupipafupi mwa kukonza bwino magawo ake, kuphatikiza .
Nthawi zambiri, zovuta zotere zimayambitsidwa ndi kusanja bwino kwamafunso mukayimba kuchokera pamabizinesi monga momwe tafotokozera .
Koma muyenera kumvetsetsa kuti ngakhale VACUUM FULL sikungathandize nthawi zonse. Pazifukwa zotere, ndikofunikira kuti mudzidziwe bwino ndi algorithm yochokera m'nkhaniyi .
#6: Kuwerenga kuchokera "pakati" pa index
Pamene kutero
Zikuwoneka kuti tidawerenga pang'ono, ndipo zonse zidalembedwa, ndipo sitinasefe aliyense mopitilira - komabe timawerenga masamba ochulukirapo kuposa momwe timafunira.
Momwe mungadziwire
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
ayamikira
Yang'anani mozama pamapangidwe a index yomwe yagwiritsidwa ntchito komanso magawo ofunikira omwe akufunsidwa - mwina gawo la index silinakhazikitsidwe. Nthawi zambiri muyenera kupanga index yofananira, koma popanda minda yachiyambi kapena .
Chitsanzo:
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;
Chilichonse chikuwoneka bwino, ngakhale molingana ndi ndondomekoyi, koma ndizokayikitsa - pa zolemba 20 zomwe zawerengedwa, tinayenera kuchotsa masamba 4 a deta, 32KB pa mbiri iliyonse - sicholimba mtima? Ndipo index name tbl_fk_org_fk_cli_idx zopatsa chidwi.
Kukonza:
CREATE INDEX ON tbl(fk_cli);
Mwadzidzidzi - Nthawi 10 mwachangu, ndi zina 4 zochepa kuti muwerenge!
Zitsanzo zina za zochitika zosagwira ntchito bwino za indexes zitha kuwoneka m'nkhaniyi .
#7: CTE × CTE
Pamene kutero
Mwa pempho adapeza "mafuta" CTE kuchokera ku matebulo osiyanasiyana, ndiyeno adaganiza zochita pakati pawo JOIN.
Mlanduwu ndi wofunikira pamitundu yomwe ili pansipa v12 kapena zopempha ndi WITH MATERIALIZED.
Momwe mungadziwire
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
ayamikira
santhulani mosamala pempholo - ndi ? Ngati inde, ndiye gwiritsani ntchito "dictionary" mu hstore/json malinga ndi chitsanzo chomwe chafotokozedwa mu .
#8: sinthani ku disk ( temp olembedwa)
Pamene kutero
Kukonza nthawi imodzi (kusanja kapena kusanja) kwamarekodi ambiri sikukwanira kukumbukira komwe kwaperekedwa kwa izi.
Momwe mungadziwire
-> *
&& temp written > 0ayamikira
Ngati kuchuluka kwa kukumbukira komwe kumagwiritsidwa ntchito sikudutsa kwambiri mtengo wotchulidwa wa parameter , m'pofunika kuwongolera. Mutha kusinthira nthawi yomweyo kwa aliyense, kapena mutha kudutsa SET [LOCAL] pa pempho/kanthu kena kake.
Chitsanzo:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
Kukonza:
SET work_mem = '128MB'; -- перед выполнением запроса
Pazifukwa zodziwikiratu, ngati kukumbukira kokha kumagwiritsidwa ntchito osati disk, ndiye kuti funsolo lidzachitidwa mofulumira kwambiri. Nthawi yomweyo, gawo la katundu kuchokera ku HDD limachotsedwanso.
Koma muyenera kumvetsetsa kuti simungathe kugawa zambiri komanso kukumbukira zambiri - sikungakhale zokwanira kwa aliyense.
#9: ziwerengero zosafunikira
Pamene kutero
Anatsanulira zambiri mu database nthawi imodzi, koma analibe nthawi yoti athamangitse ANALYZE.
Momwe mungadziwire
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10ayamikira
Chitani izo ANALYZE.
Izi zikufotokozedwa mwatsatanetsatane mu .
#10: "chinachake chalakwika"
Pamene kutero
Panali kudikirira loko komwe kunaperekedwa ndi pempho lopikisana, kapena panalibe zida zokwanira za CPU/hypervisor hardware.
Momwe mungadziwire
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
ayamikira
Gwiritsani ntchito zakunja dongosolo lowunika seva yotsekereza kapena kugwiritsa ntchito zinthu molakwika. Talankhula kale za mtundu wathu wokonzekera njirayi kwa ma seva mazana и .
Source: www.habr.com