Likhoeli tse 'maloa tse fetileng - setjhaba ho PostgreSQL.
U se u e sebelisitse makhetlo a fetang 6000, empa tšobotsi e le 'ngoe e sebetsang eo e kanna eaba ha e ea hlokomeloa ke lintlha tsa sebopeho, tse shebahalang tjena:
Ba mamele, ’me likōpo tsa hao li tla “ba boreleli le tse boreleli.” 🙂
Empa ka botebo, maemo a mangata a etsang hore kopo e liehe ebile e lape lisebelisoa li tloaelehile 'me li ka lemohuoa ka sebopeho le lintlha tsa moralo.
Tabeng ena, moqapi e mong le e mong ha a tlameha ho batla khetho ea ho ntlafatsa a le mong, a itšetlehile feela ka phihlelo ea hae - re ka mo bolella se etsahalang mona, lebaka e ka ba eng, 'me mokhoa oa ho atamela tharollo. Ke seo re se entseng.
A re ke re hlahlobisiseng linyeoe tsena - hore na li hlalosoa joang le hore na li lebisa ho likhothaletso life.
Ho ikakhela ka setotsoana sehloohong, u ka qala ho mamela block e tsamaisanang le eona , ebe joale u fetela tlhahlobong e qaqileng ea mohlala ka mong:
#1: index "undersorting"
Ha e hlaha
Bontša invoice ea morao-rao bakeng sa moreki "LLC Kolokolchik".
Mokhoa oa ho khetholla
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
likhothaletso
Index e sebelisitsoeng atolosa ka masimo a mefuta.
Mohlala:
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;
Hang-hang u ka hlokomela hore lirekoto tse fetang 100 li ile tsa ntšoa ho index, tse ileng tsa hlophisoa kaofela, eaba ho sala e le ’ngoe feela.
Ho lokisa:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Leha ho le joalo ka mohlala oa khale - Ka makhetlo a 8.5 ka potlako le makhetlo a 33 ka makhetlo a fokolang. Ha u e-na le "linnete" tse ngata bakeng sa boleng bo bong le bo bong, phello e totobetse haholoanyane fk.
Ke hlokomela hore index e joalo e tla sebetsa joalo ka index ea "prefix" ha e mpe ho feta pele bakeng sa lipotso tse ling fk, moo hlopha ka pk e ne e le sieo ebile ha e eo (o ka bala haholoanyane ka sena ). Ho kenyelletsa, e tla fana ka ntho e tloaelehileng tshehetso ya senotlolo sa kantle ho naha e hlakileng lebaleng lena.
#2: mateano a litsela (BitmapAnd)
Ha e hlaha
Bontša litumellano tsohle bakeng sa mofani "LLC Kolokolchik", e phethiloeng molemong oa "NAO Buttercup".
Mokhoa oa ho khetholla
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index Scanlikhothaletso
bopa index ea motsoako ka masimo ho tloha ka bobeli ba pele kapa ho atolosa enngwe ya tse teng ka masimo ho tloha ho ya bobedi.
Mohlala:
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); -- отбор по конкретной паре
Ho lokisa:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
Meputso mona e nyane, kaha Bitmap Heap Scan e sebetsa ka bo eona. Leha ho le joalo Ka makhetlo a 7 ka potlako le makhetlo a 2.5 ka makhetlo a fokolang.
#3: Kopanya li-index (BitmapOr)
Ha e hlaha
Hlahisa tse 20 tsa pele tsa "rona" kapa likopo tse sa abuoang tsa ho sebetsa, 'me tsa hau e le tsa bohlokoa.
Mokhoa oa ho khetholla
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index Scanlikhothaletso
Sebelisa UNION [TSOHLE] ho kopanya subqueries bakeng sa e 'ngoe le e 'ngoe ea OR-li-blocks tsa maemo.
Mohlala:
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;
Ho lokisa:
(
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, больше и не надо
Re nkile monyetla ka taba ea hore lirekoto tsohle tse 20 tse hlokahalang li ile tsa amoheloa hang-hang sebakeng sa pele, kahoo ea bobeli, e nang le "Bitmap Heap Scan" e "turu haholo", ha ea ka ea bolaoa - qetellong. 22x kapele, 44x palo e fokolang!
Pale e qaqileng haholoanyane mabapi le mokhoa ona oa ho ntlafatsa ho sebelisa mehlala e khethehileng e ka baloa lihloohong и .
Phetolelo e akaretsang khetho e laetsoeng e thehiloeng ho linotlolo tse 'maloa (mme eseng feela const/NULL para) ho buisanoa ka eona sengolong .
#4: Re bala lintho tse ngata tse sa hlokahaleng
Ha e hlaha
E le molao, e hlaha ha u batla ho "hokela sefahla se seng" ho kopo e seng e ntse e le teng.
“Mme ha o na e tshwanang, empa e nang le likonopo tsa 'm'a perela? » filimi "The Diamond Arm"
Ka mohlala, ho fetola mosebetsi o ka holimo, bonts'a likopo tsa pele tse 20 tsa khale "tse mahlonoko" tsa ho sebetsa, ho sa tsotelehe morero oa tsona.
Mokhoa oa ho khetholla
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
likhothaletso
Theha [tse ling] tse khethehileng index with WHERE boemo kapa u kenyelletse likarolo tse ling ho index.
Haeba boemo ba filthara bo "static" molemong oa hau - ke hore ha e bolele katoloso lethathamo la litekanyetso nakong e tlang - ho molemo ho sebelisa index ea WHERE. Maemo a fapaneng a boolean/enum a kena hantle sehlopheng sena.
Haeba boemo ba ho sefa e ka ba le meelelo e fapaneng, joale ho molemo ho atolosa index ka masimo ana - joalo ka boemong ba BitmapAnd ka holimo.
Mohlala:
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;
Ho lokisa:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Joalokaha u ka bona, ho sefa ho nyametse ka ho feletseng morerong, 'me kopo e fetohile Ka makhetlo a 5 ka potlako.
#5: tafole e nyane
Ha e hlaha
Boiteko bo fapaneng ba ho iketsetsa mokoloko oa ts'ebetso ea mosebetsi, ha palo e kholo ea lisebelisoa / ho tlosoa ha litlaleho tse tafoleng e lebisa boemong ba palo e kholo ea litlaleho tse "shoeleng".
Mokhoa oa ho khetholla
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
likhothaletso
Etsa ka letsoho kamehla HLOKO [FULL] kapa fumana koetliso e lekaneng khafetsa ka ho lokisa liparamente tsa eona hantle, ho kenyeletsoa .
Maemong a mangata, mathata a joalo a bakoa ke tlhahiso e fosahetseng ea lipotso ha o letsetsa ho tsoa ho logic ea khoebo joalo ka tseo ho buisanoeng ka tsona .
Empa o hloka ho utloisisa hore le VACUUM FULL e kanna ea se ke ea thusa kamehla. Bakeng sa maemo a joalo, ho bohlokoa ho itloaetsa algorithm e tsoang ho sengoloa .
#6: Ho bala ho tsoa "bohareng" ba index
Ha e hlaha
Ho bonahala eka re balile hanyenyane, 'me tsohle li ne li ngotsoe,' me ha rea ka ra sefa mang kapa mang - empa leha ho le joalo re balile maqephe a mangata ho feta ao re ka ratang.
Mokhoa oa ho khetholla
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
likhothaletso
Sheba ka hloko sebopeho sa index e sebelisitsoeng le likarolo tsa bohlokoa tse boletsoeng potsong - mohlomong haholo karolo ea index ha e so beoe. Mohlomong u tla tlameha ho theha index e ts'oanang, empa ntle le li-prefix masimo kapa .
Mohlala:
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;
Ntho e 'ngoe le e' ngoe e bonahala e le ntle, esita le ho ea ka index, empa ka tsela e itseng e belaella - bakeng sa e 'ngoe le e' ngoe ea lirekoto tse 20 tse baloang, re ile ra tlameha ho tlosa maqephe a 4 a data, 32KB ka tlaleho - na seo ha se sebete? Le lebitso la index tbl_fk_org_fk_cli_idx e susumetsang monahano.
Ho lokisa:
CREATE INDEX ON tbl(fk_cli);
Hanghang - Ka makhetlo a 10 ka potlako, le makhetlo a 4 ka tlase ho bala!
Mehlala e meng ea maemo a tšebeliso e sa sebetseng ea li-index e ka bonoa sehloohong .
#7: CTE × CTE
Ha e hlaha
Ka kopo e fumane "mafura" a CTE ho tloha litafoleng tse fapaneng, ebe o etsa qeto ea ho e etsa pakeng tsa bona JOIN.
Nyeoe e bohlokoa bakeng sa liphetolelo tse ka tlase ho v12 kapa likopo tse nang le WITH MATERIALIZED.
Mokhoa oa ho khetholla
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
likhothaletso
Hlahloba kopo ka hloko - le ? Haeba ho joalo, joale sebelisa "dictionary" ho hstore/json ho latela mohlala o hlalositsoeng ho .
#8: fetohela ho disk (tempe e ngotsoe)
Ha e hlaha
Ts'ebetso ea nako e le 'ngoe (ho hlopha kapa ho ikhetholla) ea palo e kholo ea lirekoto ha e kenelle mohopolong o fanoeng bakeng sa sena.
Mokhoa oa ho khetholla
-> *
&& temp written > 0likhothaletso
Haeba palo ea memori e sebelisoang ke ts'ebetso ha e fete haholo boleng bo boletsoeng ba parameter , ho loketse ho e lokisa. O ka khona hang-hang ho config bakeng sa motho e mong le e mong, kapa u ka khona ho feta SET [LOCAL] bakeng sa kopo/transekshene e itseng.
Mohlala:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
Ho lokisa:
SET work_mem = '128MB'; -- перед выполнением запроса
Ka mabaka a totobetseng, haeba mohopolo o sebelisoa feela eseng disk, joale potso e tla etsoa ka potlako haholo. Ka nako e ts'oanang, karolo ea mojaro o tsoang ho HDD e boetse e tlosoa.
Empa u lokela ho utloisisa hore u ke ke ua khona ho fana ka lintlha tse ngata le mehopolo e mengata - ho ke ke ha lekana motho e mong le e mong.
#9: lipalo-palo tse sa sebetseng
Ha e hlaha
Ba ile ba tšela haholo ka har'a database ka nako e le 'ngoe, empa ba ne ba se na nako ea ho e leleka ANALYZE.
Mokhoa oa ho khetholla
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10likhothaletso
E phethe ANALYZE.
Boemo bona bo hlalosoa ka ho qaqileng haholoanyane ka .
#10: "ho na le phoso"
Ha e hlaha
Ho ne ho emetse senotlolo se behiloeng ke kopo ea tlholisano, kapa ho ne ho se na lisebelisoa tse lekaneng tsa CPU / hypervisor hardware.
Mokhoa oa ho khetholla
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
likhothaletso
Sebelisa kantle tsamaiso ea ho beha leihlo seva bakeng sa ho thibela kapa tshebediso e sa tloaelehang ya mohlodi. Re se re buile ka mofuta oa rona oa ho hlophisa ts'ebetso ena bakeng sa li-server tse makholo и .
Source: www.habr.com