O le a le FA'AMANATU e le leoa ma pe fa'afefea ona tautala

O le fesili masani e aumai e se tagata e faia i lana DBA poʻo se tagata e ana pisinisi e aumai i se faufautua PostgreSQL toetoe lava tutusa le leo: “Aisea e umi ai le fa'atumu o talosaga ile fa'amaumauga?"

Seti masani o mafuaaga:

• algorithm le lelei
  pe a e filifili e AUAI i le tele o CTE i luga o le lua sefulu afe o faamaumauga
• fuainumera le talafeagai
  pe afai o le tufatufaina moni o faʻamatalaga i le laulau ua matua ese lava mai le mea na aoina e ANALYZE i le taimi mulimuli
• "pulu" i punaoa
  ma e le o toe lava le malosi faʻapipiʻi tuʻufaʻatasia o le PPU, gigabytes manatua o loʻo pamu pea, pe le mafai e le disk ona faʻatasi ma "manaʻo" uma o le database.
• poloka mai faiga tauva

Ma afai o poloka e faigata tele ona puʻeina ma auʻiliʻili, ona mo isi mea uma tatou te manaʻomia fuafuaga fesili, lea e mafai ona maua i le faʻaaogaina FAAMATALA ELEELE (E sili atu, ioe, ia fa'amanino loa (FA'AALI'E, PU'U) ...) poʻo auto_explain module.

Ae, e pei ona taʻua i le pepa lava e tasi,

"O le malamalama i se fuafuaga o se faatufugaga, ma o le aʻoaʻoina e manaʻomia ai se tele o le poto masani..."

Ae e mafai ona e faia e aunoa ma lena pe afai e te faʻaogaina le meafaigaluega saʻo!

O le a le foliga masani o se fuafuaga o fesili? O se mea faapena:

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=1

pe pei o lenei:

"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"

Ae o le faitauina o le fuafuaga i tusitusiga "mai le laupepa" e matua faigata lava ma le manino:

• o lo'o fa'aalia i le node aofa'i i puna'oa laiti
  o lona uiga, ina ia malamalama i le tele o le taimi e faʻatino ai se node faapitoa, pe o le a le tele o lenei faitauga mai le laulau na aumaia faʻamatalaga mai le disk, e tatau ona e toesea le tasi mai le isi.
• e manaʻomia le taimi node faatele i matasele
  ioe, o le toesea e le o se gaioiga sili ona faigata e tatau ona faia "i le ulu" - pe a uma, o le taimi o le faʻatinoga o loʻo faʻaalia o le averesi mo le tasi faʻataunuʻuina o se node, ma e mafai ona i ai le faitau selau o latou.
• lelei, ma o nei mea uma e taofia ai i tatou mai le taliina o le fesili autu - o ai la "le sootaga sili ona vaivai"?

Ina ua matou taumafai e faʻamatalaina nei mea uma i le faitau selau o matou atinaʻe, na matou iloa mai fafo e foliga mai e pei o lenei:

Ma o lona uiga tatou te manaʻomia...

Meafaigaluega

I totonu na matou taumafai e aoina uma masini autu e fesoasoani e malamalama ai "o ai e tuuaia ma le mea e fai" e tusa ai ma le fuafuaga ma le talosaga. Ia, ma fa'asoa se vaega o lou poto masani i le nu'u.
Feiloai ma faaaoga - explain.tensor.ru

Va'aia o fuafuaga

E faigofie ona malamalama i le fuafuaga pe a fa'apea?

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

E leai.

Ae pei o lenei, i le faapuupuupe a tuueseese faailoga autu, e sili atu ona manino:

Ae afai e sili atu le lavelave o le fuafuaga, o le a ia sau e laveai fa'asoaina taimi i nodes:

Ia, mo filifiliga sili ona faigata o loʻo ia faanatinati e fesoasoani siata o le alualu i luma:

Mo se faʻataʻitaʻiga, o loʻo i ai ni tulaga e le taua tele pe a mafai e se fuafuaga ona sili atu ma le tasi le aʻa moni:

Fa'ailoga fa'avae

Ia, afai o le faʻatulagaga atoa o le fuafuaga ma ona faʻamaʻi tiga ua uma ona faʻataʻatia ma iloa, aisea e le faʻamaonia ai i latou i le atinaʻe ma faʻamatalaina i latou i le "gagana Rusia"?

Ua uma ona matou aoina ni nai taseni ia ituaiga faʻataʻitaʻiga.

Fa'amatalaga fesili laina-i-laina

Ia, afai e te faʻapipiʻi le uluaʻi fesili i luga o le fuafuaga suʻesuʻe, e mafai ona e iloa le tele o le taimi na faʻaalu i faʻamatalaga taʻitasi - e pei o lenei:

...pe pei o lenei:

Suia fa'amaufa'ailoga i se talosaga

Afai e te "faʻapipiʻi" e le gata o se talosaga i le fuafuaga, ae faʻapea foʻi ona faʻasologa mai le DETAIL laina o le ogalaau, e mafai foi ona e kopiina i se tasi o filifiliga:

• fa'atasi ai ma suiga tau i le fesili
  mo le faʻatinoina saʻo i luga o lou faʻavae ma faʻamatalaga atili

  SELECT 'const', 'param'::text;

• fa'atasi ai ma suiga tau e ala ile SAUNIA/FA'ATA'I
  e faʻataʻitaʻi le galuega a le faʻatulagaina, pe a le amanaʻia le vaega parametric - mo se faʻataʻitaʻiga, pe a galue i luga o laulau vaeluaga

  DEALLOCATE ALL;
  PREPARE q(text) AS SELECT 'const', $1::text;
  EXECUTE q('param'::text);
  

Fa'amaumauga o fuafuaga

Fa'apipi'i, au'ili'ili, fa'asoa ma au uo! O fuafuaga o le a tumau le teuina ma e mafai ona e toe foi atu ia i latou mulimuli ane: explain.tensor.ru/archive

Ae afai e te le manaʻo i isi e vaʻai i lau fuafuaga, aua neʻi galo e siaki le pusa "aua neʻi faʻasalalau i le archive".

I tala o loʻo mulimuli mai o le a ou talanoa e uiga i faigata ma filifiliga e tulaʻi mai pe a suʻesuʻeina se fuafuaga.

puna: www.habr.com