Resep kanggo pitakon SQL sing lara

Pirang wulan kepungkur kita ngumumake nerangake.tensor.ru - umum layanan kanggo parsing lan nggambarake rencana pitakon menyang PostgreSQL.

Sampeyan wis nggunakake luwih saka 6000 kaping, nanging siji fitur praktis sing bisa uga ora digatekake yaiku pitunjuk struktural, sing katon kaya iki:

Rungokake, lan panjaluk sampeyan bakal "dadi alus lan sutra". 🙂

Nanging serius, akeh kahanan sing nggawe panjalukan alon lan keluwen sumber daya khas lan bisa dikenali dening struktur lan data rencana.

Ing kasus iki, saben pangembang individu ora kudu nggoleki pilihan optimasi dhewe, mung gumantung ing pengalaman - kita bisa ngomong apa sing kedadeyan ing kene, apa sing bisa dadi alesan, lan carane pendekatan solusi. Sing apa kita nindakake.

Ayo goleki kanthi luwih rinci babagan kasus kasebut - kepiye ditetepake lan rekomendasi apa sing ditindakake.

Kanggo luwih kacemplungaken dhewe ing topik, sampeyan bisa pisanan ngrungokake pamblokiran cocog saka laporanku ing PGConf.Russia 2020, lan banjur pindhah menyang analisis rinci saben conto:

#1: indeks "undersorting"

Nalika iku

Tampilake invoice paling anyar kanggo klien "LLC Kolokolchik".

Carane ngenali

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

Rekomendasi

Indeks digunakake nggedhekake karo kolom ngurutake.

Conto:

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;

[deleng ing explain.tensor.ru]

Sampeyan bisa langsung sok dong mirsani sing luwih saka 100 cathetan dikurangi saka indeks, kang banjur kabeh diurutake, banjur mung siji kiwa.

Mbenerake:

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

[deleng ing explain.tensor.ru]

Malah ing sampel primitif kuwi - 8.5 kaping luwih cepet lan 33 kaping kurang maca. Sing liyane "fakta" sampeyan duwe kanggo saben nilai, efek liyane ketok fk.

Aku nyathet yen indeks kasebut bakal digunakake minangka indeks "awalan" ora luwih elek tinimbang sadurunge kanggo pitakon liyane fk, ngendi urut miturut pk ora ana lan ora ana (sampeyan bisa maca liyane babagan iki ing artikelku babagan nemokake indeks sing ora efektif). Kalebu, bakal nyedhiyakake normal dhukungan kunci asing sing eksplisit ing lapangan iki.

#2: persimpangan indeks (BitmapAnd)

Nalika iku

Tampilake kabeh persetujuan kanggo klien "LLC Kolokolchik", rampung atas jenenge "NAO Buttercup".

Carane ngenali

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

Rekomendasi

nggawe indeks komposit dening kolom saka loro sing asli utawa nggedhekake salah siji sing wis ana karo kolom saka kaloro.

Conto:

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

[deleng ing explain.tensor.ru]

Mbenerake:

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

[deleng ing explain.tensor.ru]

Pembayaran ing kene luwih cilik, amarga Bitmap Heap Scan cukup efektif. Nanging piye wae 7 kaping luwih cepet lan 2.5 kaping kurang maca.

#3: Gabung indeks (BitmapOr)

Nalika iku

Tampilake 20 sing paling tuwa "kita" utawa panjaluk sing ora ditugasake kanggo diproses, kanthi prioritas sampeyan.

Carane ngenali

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

Rekomendasi

Gunakake UNION [kabeh] kanggo gabungke subqueries kanggo saben UTAWA-pamblokiran kahanan.

Conto:

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;

[deleng ing explain.tensor.ru]

Mbenerake:

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

[deleng ing explain.tensor.ru]

Kita njupuk kauntungan saka kasunyatan manawa kabeh 20 cathetan sing dibutuhake langsung ditampa ing blok pertama, mula sing nomer loro, kanthi Pindai Heap Bitmap sing luwih "larang", malah ora dieksekusi - ing pungkasan. 22x luwih cepet, 44x luwih sithik maca!

Crita sing luwih rinci babagan cara optimasi iki ing conto konkrit bisa diwaca ing artikel Antipattern PostgreSQL: GABUNGAN lan OR sing mbebayani и Antipattern PostgreSQL: crita babagan refinement iteratif saka telusuran kanthi jeneng, utawa "Optimisasi bolak-balik".

Versi umum pilihan dhawuh adhedhasar sawetara tombol (lan ora mung const / pasangan NULL) rembugan ing artikel SQL HowTo: nulis daur ulang nalika langsung ing pitakon, utawa "Telung langkah dhasar".

# 4: Kita maca akeh perkara sing ora perlu

Nalika iku

Minangka aturan, muncul nalika sampeyan pengin "masang filter liyane" menyang panyuwunan sing wis ana.

"Lan sampeyan ora duwe sing padha, nanging karo tombol mutiara? " film "The Diamond Arm"

Contone, ngowahi tugas ing ndhuwur, nuduhake 20 paling tuwa "kritis" panjalukan kanggo Processing, preduli saka tujuane.

Carane ngenali

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

Rekomendasi

Nggawe [liyane] khusus indeks kanthi kondisi WHERE utawa kalebu lapangan tambahan ing indeks.

Yen kondisi panyaring "statis" kanggo tujuan sampeyan - yaiku ora ateges expansion dhaptar nilai ing mangsa ngarep - luwih becik nggunakake indeks WHERE. Macem-macem status boolean/enum pas karo kategori iki.

Yen kahanan nyaring bisa njupuk macem-macem makna, banjur luwih apik kanggo nggedhekake indeks kanthi kolom kasebut - kaya ing kahanan BitmapAnd ndhuwur.

Conto:

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;

[deleng ing explain.tensor.ru]

Mbenerake:

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

[deleng ing explain.tensor.ru]

Nalika sampeyan bisa ndeleng, nyaring wis rampung ilang saka rencana, lan panjalukan wis dadi 5 kaping luwih cepet.

# 5: meja jarang

Nalika iku

Various nyoba kanggo nggawe antrian Processing tugas dhewe, nalika nomer akeh nganyari / pambusakan cathetan ing meja mimpin kanggo kahanan nomer akeh "mati" cathetan.

Carane ngenali

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

Rekomendasi

Nindakake kanthi manual kanthi rutin VACUUM [PENUH] utawa entuk latihan sing cukup kerep autovakum dening fine-tuning sawijining paramèter, kalebu kanggo tabel tartamtu.

Umume kasus, masalah kasebut disebabake dening komposisi pitakon sing ora apik nalika nelpon saka logika bisnis kaya sing dibahas Antipattern PostgreSQL: nglawan gerombolan "mati".

Nanging sampeyan kudu ngerti manawa VACUUM FULL bisa uga ora mbantu. Kanggo kasus kaya mengkono, iku worth familiarizing dhewe karo algoritma saka artikel DBA: nalika VACUUM gagal, kita ngresiki meja kanthi manual.

# 6: Maca saka "tengah" indeks

Nalika iku

Kayane kita maca sethithik, lan kabeh wis diindeks, lan kita ora nyaring sapa wae sing berlebihan - nanging isih maca luwih akeh kaca tinimbang sing dikarepake.

Carane ngenali

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

Rekomendasi

Deleng kanthi cetha ing struktur indeks sing digunakake lan kolom kunci sing ditemtokake ing pitakon - paling mungkin bagean saka indeks ora kasebut. Kemungkinan sampeyan kudu nggawe indeks sing padha, nanging tanpa kolom awalan utawa sinau kanggo ngulang nilai-nilai kasebut.

Conto:

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;

[deleng ing explain.tensor.ru]

Kabeh katon apik, sanajan miturut indeks, nanging ana sing curiga - kanggo saben 20 cathetan sing diwaca, kita kudu nyuda 4 halaman data, 32KB saben rekaman - apa ora wani? Lan jeneng indeks tbl_fk_org_fk_cli_idx nggugah pikiran.

Mbenerake:

CREATE INDEX ON tbl(fk_cli);

[deleng ing explain.tensor.ru]

Dumadakan- 10 kaping luwih cepet, lan 4 kaping kurang kanggo maca!

Conto liyane saka kahanan panggunaan indeks sing ora efektif bisa dideleng ing artikel kasebut DBA: nemokake indeks sing ora ana gunane.

#7: CTE × CTE

Nalika iku

Ing panyuwunan ngetung "lemak" CTE saka tabel beda, lan banjur mutusaké kanggo nindakaken antarane wong-wong mau JOIN.

Kasus iki cocog kanggo versi ngisor v12 utawa panjalukan karo WITH MATERIALIZED.

Carane ngenali

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

Rekomendasi

Kasebut kanthi teliti, njelasno panjalukan - lan Apa CTE dibutuhake ing kene?? Yen ya, banjur aplikasi "kamus" ing hstore/json miturut model sing diterangake ing PostgreSQL Antipatterns: ayo kenek sing abot JOIN karo kamus.

#8: swap menyang disk (temp ditulis)

Nalika iku

Processing siji-wektu (ngurutake utawa uniqueization) saka nomer akeh cathetan ora pas menyang memori diparengake kanggo iki.

Carane ngenali

-> *
   && temp written > 0

Rekomendasi

Yen jumlah memori digunakake dening operasi ora nemen ngluwihi Nilai kasebut parameter kerja_mem, iku worth mbenerake. Sampeyan bisa langsung ing config for everyone, utawa sampeyan bisa liwat SET [LOCAL] kanggo panjalukan / transaksi tartamtu.

Conto:

SHOW work_mem;
-- "16MB"

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

[deleng ing explain.tensor.ru]

Mbenerake:

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

[deleng ing explain.tensor.ru]

Kanggo alasan sing jelas, yen mung memori sing digunakake lan dudu disk, pitakon kasebut bakal dieksekusi luwih cepet. Ing wektu sing padha, bagéan saka beban saka HDD uga dibusak.

Nanging sampeyan kudu ngerti manawa sampeyan ora bakal bisa nyedhiyakake akeh memori - ora bakal cukup kanggo kabeh wong.

# 9: statistik sing ora cocog

Nalika iku

Padha diwutahake akeh menyang database bebarengan, nanging ora duwe wektu kanggo drive adoh ANALYZE.

Carane ngenali

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

Rekomendasi

Nindakake ANALYZE.

Kahanan iki diterangake kanthi luwih rinci ing PostgreSQL Antipatterns: statistik iku kabeh.

#10: "Ana sing salah"

Nalika iku

Ana Enteni kanggo kunci dileksanakake dening request saingan, utawa ora cukup CPU / sumber hardware hypervisor.

Carane ngenali

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

Rekomendasi

Gunakake njaba sistem monitoring server kanggo mblokir utawa konsumsi sumber ora normal. Kita wis ngomong babagan versi ngatur proses iki kanggo atusan server kene и kene.

Source: www.habr.com