Recipe ho an'ny fanontaniana SQL marary

Volana maromaro lasa izay nanambara izahay explain.tensor.ru - ampahibemaso serivisy ho an'ny famakafakana sy fijerena drafitry ny fangatahana mankany amin'ny PostgreSQL.

Efa in-6000 XNUMX mahery no nampiasanao azy, fa ny endri-javatra iray azo ampiasaina izay mety tsy voamarika dia famantarana ara-drafitra, izay toa izao:

Henoy izy ireo, dia “ho malama sy landy” ny fangatahanao. 🙂

Fa ny tena zava-dehibe, toe-javatra maro izay mahatonga ny fangatahana miadana sy mandany loharanon-karena dia mahazatra ary azo fantarina amin'ny rafitra sy ny angon-drakitra momba ny drafitra.

Amin'ity tranga ity, ny mpamorona tsirairay dia tsy mila mitady safidy optimization ho azy manokana, miantehitra fotsiny amin'ny traikefany - azontsika lazaina aminy ny zava-mitranga eto, inona no mety ho antony, ary ny fomba hanatonana vahaolana. Izany no nataonay.

Andeha hojerentsika akaiky ireo tranga ireo - ny fomba hamaritana azy ireo sy ny tolo-kevitra entiny.

Mba hiroboka tsara kokoa amin'ny lohahevitra dia azonao atao aloha ny mihaino ny sakana mifandraika amin'izany ny tatitro tao amin'ny PGConf.Russia 2020, ary avy eo dia miroso amin'ny famakafakana antsipirihan'ny ohatra tsirairay:

1. индексная «недосортировка»
2. пересечение индексов (BitmapAnd)
3. объединение индексов (BitmapOr)
4. читаем много лишнего
5. разреженная таблица
6. чтение с «середины» индекса
7. CTE × CTE
8. swap на диск (temp written)
9. antontan'isa tsy misy ifandraisany
10. «что-то пошло не так»

#1: fanondroana "undersorting"

Rehefa mipoitra

Asehoy ny faktiora farany ho an'ny mpanjifa "LLC Kolokolchik".

Ahoana no hamantarana

-> Limit
   -> Sort
      -> Index [Only] Scan [Backward] | Bitmap Heap Scan

tolo-kevitra

Index ampiasaina mivelatra miaraka amin'ny sahan'asa.

ohatra:

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;

[jereo ao amin'ny explain.tensor.ru]

Hitanao avy hatrany fa mihoatra ny 100 ny firaketana no nesorina tao amin'ny index, izay nalahatra daholo avy eo, ary avy eo dia ny iray ihany no tavela.

Ahitsio

DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки

[jereo ao amin'ny explain.tensor.ru]

Na dia amin'ny santionany voalohany toy izany aza - 8.5 heny haingana kokoa ary in-33 heny ny famakiana. Arakaraka ny "zava-misy" anananao isaky ny sandany no miharihary kokoa ny vokany fk.

Marihiko fa ny fanondroana toy izany dia hiasa toy ny fanondroana "prefix" tsy ratsy noho ny teo aloha ho an'ny fanontaniana hafa miaraka fk, aiza no alamina pk tsy nisy ary tsy nisy (afaka mamaky bebe kokoa momba an'io ianao ao amin'ny lahatsoratro momba ny fitadiavana indeksa tsy mahomby). Anisan'izany, dia hanome ara-dalàna fanohanana fanalahidy vahiny mazava amin'ity sehatra ity.

#2: fihaonan'ny fanondro (BitmapAnd)

Rehefa mipoitra

Asehoy ny fifanarahana rehetra ho an'ny mpanjifa "LLC Kolokolchik", namarana tamin'ny anaran'ny "NAO Buttercup".

Ahoana no hamantarana

-> BitmapAnd
   -> Bitmap Index Scan
   -> Bitmap Index Scan

tolo-kevitra

mamorona fanondroana mitambatra amin'ny saha avy amin'ny tany am-boalohany na manitatra ny iray amin'ireo efa misy miaraka amin'ny saha avy amin'ny faharoa.

ohatra:

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); -- отбор по конкретной паре

[jereo ao amin'ny explain.tensor.ru]

Ahitsio

DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);

[jereo ao amin'ny explain.tensor.ru]

Ny karama eto dia kely kokoa, satria ny Bitmap Heap Scan dia mahomby amin'ny tenany manokana. Saingy na izany aza 7 heny haingana kokoa ary in-2.5 heny ny famakiana.

#3: Manambatra tondro (BitmapOr)

Rehefa mipoitra

Asehoy ireo 20 tranainy indrindra “isika” na fangatahana tsy voatendry ho an'ny fanodinana, ka ny anao no laharam-pahamehana.

Ahoana no hamantarana

-> BitmapOr
   -> Bitmap Index Scan
   -> Bitmap Index Scan

tolo-kevitra

Ampiasao UNION [REHETRA] mba hanambatra ny subqueries ho an'ny tsirairay amin'ny OR-bloka ny fepetra.

ohatra:

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;

[jereo ao amin'ny explain.tensor.ru]

Ahitsio

(
  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, больше и не надо

[jereo ao amin'ny explain.tensor.ru]

Nohararaotinay fa voaray avy hatrany tao amin'ny bloc voalohany avokoa ireo firaketana 20 takiana rehetra, ka ny faharoa, miaraka amin'ny Bitmap Heap Scan "lafo" kokoa, dia tsy novonoina akory - tamin'ny farany. 22x haingana kokoa, 44x vitsy kokoa ny famakiana!

Tantara amin'ny antsipiriany bebe kokoa momba ity fomba fanatsarana ity mampiasa ohatra manokana azo vakiana amin'ny lahatsoratra Antipatterns PostgreSQL: fiaraha-miasa sy OR manimba и PostgreSQL Antipatterns: tantara momba ny fanatsarana ny fikarohana amin'ny anarana, na "Optimization mandroso sy miverina".

Dikan-teny ankapobeny nibaiko fifantenana mifototra amin'ny fanalahidy maromaro (ary tsy ny mpivady const/NULL ihany) no resahina ao amin'ny lahatsoratra SQL HowTo: manoratra tsipika fohy mivantana amin'ny fangatahana, na "dingana telo fototra".

#4: Mamaky zavatra tsy ilaina be dia be izahay

Rehefa mipoitra

Amin'ny maha-fitsipika azy dia mipoitra izany rehefa te "hampiditra sivana hafa" amin'ny fangatahana efa misy ianao.

“Ary tsy manana iray ianao, fa miaraka amin'ny bokotra renin'ny perla? " sarimihetsika "The Diamond Arm"

Ohatra, ny fanovana ny asa etsy ambony, dia asehoy ny fangatahana "kritika" 20 voalohany indrindra ho an'ny fanodinana, na inona na inona tanjon'izy ireo.

Ahoana no hamantarana

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && 5 × rows < RRbF -- отфильтровано >80% прочитанного
   && loops × RRbF > 100 -- и при этом больше 100 записей суммарно

tolo-kevitra

Mamorona [more] manokana fanondro misy fepetra WHERE na ampidiro saha fanampiny ao amin'ny fanondroana.

Raha "static" ho an'ny tanjonao ny fepetra sivana - izany hoe tsy midika fanitarana lisitry ny soatoavina amin'ny ho avy - tsara kokoa ny mampiasa index WHERE. Ny sata boolean/enum isan-karazany dia mifanentana tsara amin'ity sokajy ity.

Raha ny fepetra sivana afaka mitondra dikany samihafa, dia tsara kokoa ny manitatra ny index miaraka amin'ireo saha ireo - toy ny amin'ny toe-javatra misy an'i BitmapAnd etsy ambony.

ohatra:

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;

[jereo ao amin'ny explain.tensor.ru]

Ahitsio

CREATE INDEX ON tbl(pk)
  WHERE critical; -- добавили "статичное" условие фильтрации

[jereo ao amin'ny explain.tensor.ru]

Araka ny hitanao, ny sivana dia nanjavona tanteraka tamin'ny drafitra, ary lasa ny fangatahana 5 heny haingana kokoa.

#5: latabatra kely

Rehefa mipoitra

Andrana isan-karazany hamorona filaharana fanodinana asa anao manokana, rehefa misy fanavaozana/famafana rakitra marobe eo amin'ny latabatra dia mitarika amin'ny toe-javatra misy rakitra "maty" marobe.

Ahoana no hamantarana

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && loops × (rows + RRbF) < (shared hit + shared read) × 8
      -- прочитано больше 1KB на каждую запись
   && shared hit + shared read > 64

tolo-kevitra

Manaova tanana tsy tapaka VACUUM [FENO] na manatratra fiofanana matetika autovacuum amin'ny alàlan'ny fanitsiana tsara ireo paramètre, ao anatin'izany ho an'ny latabatra manokana.

Amin'ny ankabeazan'ny toe-javatra, ny olana toy izany dia vokatry ny fandrafetana fanontaniana ratsy rehefa miantso avy amin'ny lojikan'ny fandraharahana tahaka ireo noresahina tao Antipatterns PostgreSQL: miady amin'ny andian'ny "maty".

Saingy mila takatrao fa na ny VACUUM FULL aza dia mety tsy hanampy foana. Ho an'ny tranga toy izany dia ilaina ny mahafantatra ny algorithm amin'ny lahatsoratra DBA: rehefa tsy mahomby ny VACUUM dia manadio ny latabatra amin'ny tanana izahay.

#6: Famakiana avy amin'ny "afovoany" amin'ny fanondroana

Rehefa mipoitra

Toa namaky kely izahay, ary nasiana index ny zava-drehetra, ary tsy nanasivana olona be loatra izahay - fa mbola mamaky pejy betsaka kokoa noho izay tadiavinay.

Ahoana no hamantarana

-> Index [Only] Scan [Backward]
   && loops × (rows + RRbF) < (shared hit + shared read) × 8
      -- прочитано больше 1KB на каждую запись
   && shared hit + shared read > 64

tolo-kevitra

Jereo akaiky ny firafitry ny tondro ampiasaina sy ny saha fototra voatondro ao amin'ny fangatahana - azo inoana fa tsy voafaritra ny ampahany amin'ny fanondroana. Azo inoana fa tsy maintsy mamorona tondro mitovy amin'izany ianao, saingy tsy misy saha misy prefix na mianatra mamerina ny soatoaviny.

ohatra:

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;

[jereo ao amin'ny explain.tensor.ru]

Toa tsara daholo ny zava-drehetra, na dia araka ny fanondroana aza, saingy mampiahiahy izany - ho an'ny rakitsoratra 20 tsirairay novakiana, dia tsy maintsy nesorinay ny angon-drakitra pejy 4, 32KB isaky ny rakitra - tsy sahy ve izany? Ary ny anarana fanondro tbl_fk_org_fk_cli_idx mampieritreritra.

Ahitsio

CREATE INDEX ON tbl(fk_cli);

[jereo ao amin'ny explain.tensor.ru]

Tampoka teo - 10 heny haingana kokoa, ary in-4 latsaka ny mamaky!

Ohatra hafa amin'ny toe-javatra tsy mahomby amin'ny fampiasana index dia hita ao amin'ny lahatsoratra DBA: fitadiavana index tsy misy ilana azy.

#7: CTE × CTE

Rehefa mipoitra

Amin'ny fangatahana nahazo isa "matavy" CTE avy amin'ny latabatra samihafa, ary avy eo dia nanapa-kevitra ny hanao izany eo anelanelan'izy ireo JOIN.

Ny tranga dia mifandraika amin'ny dikan-teny ambany v12 na fangatahana miaraka amin'ny WITH MATERIALIZED.

Ahoana no hamantarana

-> CTE Scan
   && loops > 10
   && loops × (rows + RRbF) > 10000
      -- слишком большое декартово произведение CTE

tolo-kevitra

Diniho tsara ny fangatahana - ary Tena ilaina ve ny CTE eto?? Raha eny, dia ampiharo "diksionera" ao amin'ny hstore/json araka ny modely voalaza ao amin'ny PostgreSQL Antipatterns: andao hidona amin'ny rakibolana mavesatra.

#8: ampifamadiho amin'ny kapila (tempo voasoratra)

Rehefa mipoitra

Ny fanodinana indray mandeha (fanasokajiana na fanavahana) ny rakitra marobe dia tsy mifanaraka amin'ny fitadidiana natokana ho an'izany.

Ahoana no hamantarana

-> *
   && temp written > 0

tolo-kevitra

Raha ny habetsaky ny fahatsiarovana ampiasain'ny fandidiana dia tsy mihoatra ny sanda voafaritra amin'ny parameter work_mem, mendrika ny hanitsiana izany. Azonao atao avy hatrany ao amin'ny config ho an'ny rehetra, na azonao atao SET [LOCAL] ho an'ny fangatahana / fifampiraharahana manokana.

ohatra:

SHOW work_mem;
-- "16MB"

SELECT
  random()
FROM
  generate_series(1, 1000000)
ORDER BY
  1;

[jereo ao amin'ny explain.tensor.ru]

Ahitsio

SET work_mem = '128MB'; -- перед выполнением запроса

[jereo ao amin'ny explain.tensor.ru]

Noho ny antony mazava, raha fitadidiana ihany no ampiasaina fa tsy kapila, dia ho haingana kokoa ny fangatahana. Mandritra izany fotoana izany, esorina ihany koa ny ampahany amin'ny entana avy amin'ny HDD.

Saingy mila takatrao fa tsy ho afaka hanokana fahatsiarovana be dia be ianao - tsy ho ampy ho an'ny rehetra.

#9: antontan'isa tsy misy ifandraisany

Rehefa mipoitra

Nandatsaka betsaka tao anaty tahiry indray mandeha izy ireo, saingy tsy nanam-potoana handroahana izany ANALYZE.

Ahoana no hamantarana

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && ratio >> 10

tolo-kevitra

Tanteraho izany ANALYZE.

Ity toe-javatra ity dia voafaritra amin'ny antsipiriany kokoa ao amin'ny PostgreSQL Antipatterns: ny antontan'isa dia ny zava-drehetra.

#10: “nisy zavatra tsy nety”

Rehefa mipoitra

Nisy ny fiandrasana hidin-trano napetraky ny fangatahana mifaninana, na tsy ampy ny loharanon-karena CPU/hypervisor.

Ahoana no hamantarana

-> *
   && (shared hit / 8K) + (shared read / 1K) < time / 1000
      -- RAM hit = 64MB/s, HDD read = 8MB/s
   && time > 100ms -- читали мало, но слишком долго

tolo-kevitra

Ampiasao ivelany rafitra fanaraha-maso mpizara ho fanakanana na fanjifana loharano tsy ara-dalàna. Efa niresaka momba ny dikan-tsika amin'ny fikarakarana ity dingana ity ho an'ny mpizara an-jatony izahay eto и eto.

Source: www.habr.com