ʻO ka nīnau maʻamau a ka mea hoʻomohala e nīnau ai i kā lākou DBA a i ʻole ka mea nona ka ʻoihana e nīnau ai i kahi kākā'ōlelo PostgreSQL he aneane like mau ia: "No ke aha i lōʻihi ai ka hoʻokō ʻana o nā nīnau ma ka waihona ʻikepili?"
Nā kumu kuʻuna:
- algorithm kūpono ʻole
ke hoʻoholo ʻoe e hui pū me kekahi mau CTE ma waena o kekahi mau ʻumi tausani o nā moʻolelo - ʻikepili pili ʻole
inā ʻokoʻa loa ka hoʻolaha maoli ʻana o ka ʻikepili ma ka papa mai ka mea i hōʻiliʻili ʻia e ANALYZE i ka manawa hope loa - "hoʻopili" i nā kumuwaiwai
a ʻaʻohe lawa ka mana helu CPU i hoʻolaʻa ʻia, ke hoʻonui mau ʻia nei nā gigabytes o ka hoʻomanaʻo, a i ʻole ʻaʻole hiki i ka disk ke mālama i nā "makemake" āpau o ka waihona ʻikepili. - ke ālai ʻana mai nā kaʻina hana hoʻokūkū
A inā paʻakikī ka pale ʻana e hopu a kālailai, a laila no nā mea ʻē aʻe a pau e pono ai mākou hoʻolālā nīnau, hiki ke loaʻa me ka hoʻohana ʻana (ʻOi aku ka maikaʻi, ʻoiaʻiʻo, e WEHEWEHE koke (ANALYZE, BUFFERS) …) ai ʻole .
Akā, e like me ka mea i ʻōlelo ʻia ma ka palapala like,
"He hana noʻeau ka hoʻomaopopo ʻana i kahi hoʻolālā, a no ke akamai ʻana, pono ke loaʻa ʻana o kahi ʻike nui, ..."
Akā hiki iā ʻoe ke hana me ka ʻole o ia inā ʻoe e hoʻohana i ka mea hana kūpono!
Pehea ke ʻano o kahi hoʻolālā nīnau? E like me kēia:
Index Scan using pg_class_relname_nsp_index on pg_class (actual time=0.049..0.050 rows=1 loops=1)
Index Cond: (relname = $1)
Filter: (oid = $0)
Buffers: shared hit=4
InitPlan 1 (returns $0,$1)
-> Limit (actual time=0.019..0.020 rows=1 loops=1)
Buffers: shared hit=1
-> Seq Scan on pg_class pg_class_1 (actual time=0.015..0.015 rows=1 loops=1)
Filter: (relkind = 'r'::"char")
Rows Removed by Filter: 5
Buffers: shared hit=1a i ʻole penei:
"Append (cost=868.60..878.95 rows=2 width=233) (actual time=0.024..0.144 rows=2 loops=1)"
" Buffers: shared hit=3"
" CTE cl"
" -> Seq Scan on pg_class (cost=0.00..868.60 rows=9972 width=537) (actual time=0.016..0.042 rows=101 loops=1)"
" Buffers: shared hit=3"
" -> Limit (cost=0.00..0.10 rows=1 width=233) (actual time=0.023..0.024 rows=1 loops=1)"
" Buffers: shared hit=1"
" -> CTE Scan on cl (cost=0.00..997.20 rows=9972 width=233) (actual time=0.021..0.021 rows=1 loops=1)"
" Buffers: shared hit=1"
" -> Limit (cost=10.00..10.10 rows=1 width=233) (actual time=0.117..0.118 rows=1 loops=1)"
" Buffers: shared hit=2"
" -> CTE Scan on cl cl_1 (cost=0.00..997.20 rows=9972 width=233) (actual time=0.001..0.104 rows=101 loops=1)"
" Buffers: shared hit=2"
"Planning Time: 0.634 ms"
"Execution Time: 0.248 ms"Akā, he mea paʻakikī loa ka heluhelu ʻana i kahi hoʻolālā mai ka wā ʻōpiopio a he mea maʻalahi hoʻi:
- hōʻike ʻia ma ka node huina o nā kumuwaiwai lāʻau liʻiliʻi
ʻo ia hoʻi, no ka hoʻomaopopo ʻana i ka nui o ka manawa i hoʻokō ʻia ai kahi kikowaena kikoʻī, a i ʻole ka nui o ka ʻikepili i kēia heluhelu ʻana mai ka papa i kiʻi ʻia mai ka disk, pono ʻoe e unuhi i kekahi mai kekahi. - pono ka manawa node hoʻonui ʻia e nā loops
ʻAe, ʻaʻole ka unuhi ʻana ka hana paʻakikī loa e hana "i loko o kou poʻo" - ma hope o nā mea āpau, ua hōʻike ʻia ka manawa hoʻokō ma ke ʻano he awelika no hoʻokahi hoʻokō ʻana o kahi node, a hiki ke loaʻa nā haneli o lākou - ʻĀ, ʻo kēia mau mea a pau e pale aku iā mākou mai ka pane ʻana i ka nīnau nui - no laila ʻo wai ka loulou nāwaliwali loa?
I ko mākou ho'āʻo ʻana e wehewehe i kēia mau mea a pau i kekahi mau haneli o kā mākou mau mea hoʻomohala, ua ʻike mākou mai waho mai he ʻano like ia me kēia:
A ʻo ia hoʻi, pono mākou…
Nā mea hana
Ma loko, ua hoʻāʻo mākou e hōʻiliʻili i nā ʻano hana koʻikoʻi a pau e kōkua iā mākou e hoʻomaopopo, ma muli o ka hoʻolālā a me ke noi, "ʻo wai ka mea e hewa ai a me ka mea e hana ai." A, ʻoiaʻiʻo hoʻi, ua kaʻana like mākou i kekahi o ko mākou ʻike me ke kaiāulu.
E hui a hauʻoli -
ʻIke ʻia o nā hoʻolālā
He mea maʻalahi anei ke hoʻomaopopo i kahi hoʻolālā ke ʻano like me kēia?
Seq Scan on pg_class (actual time=0.009..1.304 rows=6609 loops=1)
Buffers: shared hit=263
Planning Time: 0.108 ms
Execution Time: 1.800 ms
ʻAʻole naʻe.
Akā, pēlā nō ia, ma ke ʻano pōkole, ke hoʻokaʻawale ʻia nā ʻōkuhi koʻikoʻi, ʻoi aku ka maopopo:
Akā inā ʻoi aku ka paʻakikī o ka hoʻolālā, e hiki mai ke kōkua pakuhi hoʻokaʻawale manawa e nā kikowaena:
ʻĀ, no nā koho paʻakikī loa, wikiwiki ʻo ia e hoʻopakele kiʻikuhi hoʻokō:
Eia kekahi laʻana, aia kekahi mau kūlana koʻikoʻi ʻole i ka wā e loaʻa ai i kahi hoʻolālā he ʻoi aku ma mua o hoʻokahi kumu ʻoiaʻiʻo:
Nā hōʻailona kūkulu
ʻĀ, inā ua hoʻonohonoho mua ʻia a ʻike ʻia ke ʻano holoʻokoʻa o ka hoʻolālā a me kona mau nāwaliwali, no ke aha e hōʻike ʻole ai iā lākou i ka mea hoʻomohala a wehewehe iā lākou ma ka ʻōlelo Pelekania maʻalahi?
Ua hōʻiliʻili mua mākou i kekahi mau kakini o kēia mau mamana ʻōlelo paipai.
Mea hoʻopili nīnau lālani-ma-laina
I kēia manawa, inā ʻoe e kau i ka nīnau mua ma ka hoʻolālā e kālailai ʻia nei, hiki iā ʻoe ke ʻike i ka nui o ka manawa i hoʻolilo ʻia ma kēlā me kēia mea hana pākahi—e like me kēia:
...a i ʻole e like me kēia:
Ke pani nei i nā palena i loko o kahi nīnau
Inā ʻaʻole ʻoe i hoʻopili wale i ka nīnau akā ʻo kāna mau palena hoʻi mai ka laina DETAIL o ka log i ka hoʻolālā, hiki iā ʻoe ke kope hou iā ia me ka hoʻohana ʻana i kekahi o nā koho aʻe:
- me ka hoʻololi ʻana o nā waiwai i loko o ka nīnau
no ka hoʻokō pololei ʻana ma luna o kona kumu ponoʻī a me ka hoʻopili hou ʻanaSELECT 'const', 'param'::text; - me ka hoʻololi ʻana o nā waiwai ma o PREPARE/EXECUTE
e hoʻohālike i ka hana o ka mea hoʻonohonoho manawa, i ka wā e hiki ai ke hoʻowahāwahā ʻia ka ʻāpana parametric - no ka laʻana, i ka wā e hana ana ma nā papa i māhele ʻiaDEALLOCATE ALL; PREPARE q(text) AS SELECT 'const', $1::text; EXECUTE q('param'::text);
Waihona o nā hoʻolālā
E hoʻopili, kālailai, a kaʻana like me nā hoa hana! E waiho ʻia kāu mau hoʻolālā ma ka waihona, a hiki iā ʻoe ke hoʻi iā lākou ma hope:
Akā inā ʻaʻole ʻoe makemake i nā poʻe ʻē aʻe e ʻike i kāu hoʻolālā, mai poina e kaha i ka pahu "mai hoʻolaha ma ka waihona".
Ma nā ʻatikala aʻe, e kūkākūkā wau i nā pilikia a me nā hoʻonā e kū mai ana i ka wā e kālailai ai i kahi hoʻolālā.
Source: www.habr.com
