Daim ntawv qhia rau ailing SQL queries

Lub hli dhau los peb tshaj tawm piav.tensor.ru - pej xeem kev pab rau parsing thiab visualizing query plan rau PostgreSQL.

Koj twb tau siv nws ntau dua 6000 zaug, tab sis ib qho khoom siv tau zoo uas yuav tau mus tsis pom yog cov lus qhia, uas zoo ib yam li no:

Mloog lawv, thiab koj qhov kev thov yuav "ua kom du thiab silky." 🙂

Tab sis qhov tseeb, ntau qhov xwm txheej uas ua rau qhov kev thov qeeb thiab cov peev txheej tshaib plab yog qhov raug thiab tuaj yeem lees paub los ntawm cov qauv thiab cov ntaub ntawv ntawm txoj kev npaj.

Hauv qhov no, txhua tus neeg tsim tawm tsis tas yuav nrhiav kev xaiv kom zoo ntawm nws tus kheej, tso siab rau nws qhov kev paub dhau los - peb tuaj yeem qhia nws tias qhov tshwm sim ntawm no, dab tsi tuaj yeem yog vim li cas, thiab yuav ua li cas mus cuag kev daws teeb meem. Qhov ntawd yog qhov peb tau ua.

Cia peb saib ze dua ntawm cov xwm txheej no - lawv txhais li cas thiab cov lus pom zoo uas lawv coj mus rau.

Txhawm rau kom nkag siab zoo dua rau koj tus kheej hauv lub ncauj lus, koj tuaj yeem ua ntej mloog cov ntawv thaiv los ntawm Kuv tsab ntawv ceeb toom ntawm PGConf.Russia 2020, thiab tsuas yog tom qab ntawd txav mus rau cov ncauj lus kom ntxaws ntawm txhua qhov piv txwv:

# 1: index "undersorting"

Thaum tshwm sim

Qhia daim ntawv them nqi kawg rau tus neeg siv khoom "LLC Kolokolchik".

Yuav txheeb xyuas li cas

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

tswv yim pom zoo

Index siv nthuav nrog cov teb.

Piv Txwv:

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;

[saib ntawm piav qhia.tensor.ru]

Koj tuaj yeem pom tam sim ntawd tias ntau tshaj 100 cov ntaub ntawv raug rho tawm ntawm qhov ntsuas, uas tau muab tag nrho cov txheeb cais, thiab tom qab ntawd tsuas yog ib qho xwb.

Kho kom raug:

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

[saib ntawm piav qhia.tensor.ru]

Txawm nyob rau hauv xws li ib tug primitive qauv - 8.5 npaug nrawm dua thiab 33 npaug tsawg dua nyeem. Qhov ntau "qhov tseeb" koj muaj rau txhua tus nqi, qhov txiaj ntsig pom tseeb dua fk.

Kuv nco ntsoov tias qhov ntsuas no yuav ua haujlwm li "prefix" Performance index tsis phem dua li ua ntej rau lwm cov lus nug nrog fk, qhov twg txheeb pk tsis muaj thiab tsis muaj (koj tuaj yeem nyeem ntxiv txog qhov no nyob rau hauv kuv tsab xov xwm hais txog nrhiav ineffective indexes). Xws li, nws yuav muab qhov qub kev txhawb nqa tseem ceeb txawv teb chaws ntawm daim teb no.

# 2: index kev sib tshuam (BitmapAnd)

Thaum tshwm sim

Qhia tag nrho cov lus pom zoo rau cov neeg siv khoom "LLC Kolokolchik", xaus rau sawv cev ntawm "NAO Buttercup".

Yuav txheeb xyuas li cas

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

tswv yim pom zoo

tsim Composite Index los ntawm cov teb los ntawm ob qho tib si thawj los yog nthuav ib qho ntawm cov uas twb muaj lawm nrog cov teb los ntawm qhov thib ob.

Piv Txwv:

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

[saib ntawm piav qhia.tensor.ru]

Kho kom raug:

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

[saib ntawm piav qhia.tensor.ru]

Qhov nyiaj them ntawm no yog me dua, txij li Bitmap Heap Scan tau zoo heev ntawm nws tus kheej. Txawm li cas los xij 7 npaug nrawm dua thiab 2.5 npaug tsawg dua nyeem.

#3: Merge indexes (BitmapOr)

Thaum tshwm sim

Qhia thawj 20 tus laus tshaj plaws "peb" lossis cov ntawv thov tsis tau muab rau kev ua tiav, nrog rau koj qhov tseem ceeb.

Yuav txheeb xyuas li cas

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

tswv yim pom zoo

Siv UNION [Txhua] los ua ke subqueries rau txhua qhov OR-blocks ntawm tej yam kev mob.

Piv Txwv:

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;

[saib ntawm piav qhia.tensor.ru]

Kho kom raug:

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

[saib ntawm piav qhia.tensor.ru]

Peb coj kom zoo dua ntawm qhov tseeb tias tag nrho 20 cov ntaub ntawv yuav tsum tau txais tam sim ntawd hauv thawj ntu, yog li qhov thib ob, nrog qhov ntau "kim" Bitmap Heap Scan, tseem tsis tau ua tiav - thaum kawg 22x sai dua, 44x nyeem tsawg dua!

Ib zaj dab neeg ntxaws ntxiv txog txoj kev ua kom zoo dua no siv cov piv txwv tshwj xeeb tuaj yeem nyeem hauv cov ntawv PostgreSQL Antipatterns: Kev Koom Tes Ua Tsis Zoo thiab ORs и PostgreSQL Antipatterns: ib zaj dab neeg ntawm kev ua kom zoo dua ntawm kev tshawb nrhiav los ntawm lub npe, lossis "Kev ua kom zoo rov qab".

Generalized version xaj xaiv raws li ntau tus yuam sij (thiab tsis yog tus const/NULL khub) tau tham hauv kab lus SQL HowTo: sau ib lub sij hawm voj ncaj qha rau hauv cov lus nug, los yog "Elementary peb-kauj ruam".

#4: Peb nyeem ntau yam tsis tsim nyog

Thaum tshwm sim

Raws li txoj cai, nws tshwm sim thaum koj xav "ntxiv lwm lim" rau qhov kev thov uas twb muaj lawm.

“Thiab koj tsis muaj ib qho, tab sis nrog niam-ntawm-pearl nyees khawm? » movie "Lub Pob Zeb Diamond"

Piv txwv li, hloov kho cov hauj lwm saum toj no, qhia thawj 20 qhov qub tshaj plaws "tseem ceeb" thov rau kev ua, tsis hais lawv lub hom phiaj.

Yuav txheeb xyuas li cas

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

tswv yim pom zoo

Tsim [ntxiv] tshwj xeeb index nrog qhov chaw nyob los yog suav nrog cov teb ntxiv hauv qhov ntsuas.

Yog tias lub lim dej yog "static" rau koj lub hom phiaj - yog tsis txhais hais tias expansion daim ntawv teev cov txiaj ntsig yav tom ntej - nws yog qhov zoo dua los siv qhov ntsuas qhov twg. Ntau yam boolean / enum xwm txheej haum zoo rau hauv pawg no.

Yog qhov kev lim dej tuaj yeem ua rau lub ntsiab lus sib txawv, ces nws yog qhov zoo dua los nthuav qhov Performance index nrog cov teb no - zoo li hauv qhov xwm txheej nrog BitmapAnd saum toj no.

Piv Txwv:

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;

[saib ntawm piav qhia.tensor.ru]

Kho kom raug:

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

[saib ntawm piav qhia.tensor.ru]

Raws li koj tuaj yeem pom, kev lim dej tau ploj tag nrho los ntawm txoj kev npaj, thiab qhov kev thov tau dhau los 5 lub sij hawm sai dua.

#5: sparse table

Thaum tshwm sim

Ntau qhov kev sim los tsim koj tus kheej cov haujlwm ua haujlwm, thaum ntau qhov hloov tshiab / tshem tawm cov ntaub ntawv ntawm lub rooj ua rau muaj qhov xwm txheej loj ntawm "tuag" cov ntaub ntawv.

Yuav txheeb xyuas li cas

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

tswv yim pom zoo

Ua manually tsis tu ncua VACUUM [FULL] los yog ua tiav kev cob qhia tsis tu ncua txaus autovacuum los ntawm fine-tuning nws parameters, nrog rau rau ib lub rooj tshwj xeeb.

Feem ntau, cov teeb meem no tshwm sim los ntawm cov lus nug tsis zoo thaum hu los ntawm kev lag luam logic zoo li cov uas tau tham hauv PostgreSQL Antipatterns: sib ntaus sib tua hordes ntawm "tuag".

Tab sis koj yuav tsum nkag siab tias txawm tias VACUUM FULL yuav tsis pab. Rau cov xwm txheej zoo li no, nws tsim nyog paub koj tus kheej nrog cov algorithm los ntawm kab lus DBA: thaum lub tshuab nqus tsev tsis ua haujlwm, peb ntxuav lub rooj ntawm tes.

# 6: Nyeem los ntawm "nruab nrab" ntawm qhov ntsuas

Thaum tshwm sim

Nws zoo nkaus li tias peb nyeem me ntsis, thiab txhua yam tau txheeb xyuas, thiab peb tsis tau lim tawm leej twg ntau dhau - tab sis tseem peb nyeem ntau nplooj ntawv ntau dua li peb xav.

Yuav txheeb xyuas li cas

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

tswv yim pom zoo

Ua tib zoo saib cov qauv ntawm qhov ntsuas tau siv thiab cov ntsiab lus tseem ceeb tau teev tseg hauv cov lus nug - feem ntau yuav ib feem ntawm qhov ntsuas tsis tau teeb tsa. Feem ntau koj yuav tau tsim qhov ntsuas zoo sib xws, tab sis tsis muaj qhov ua ntej lossis kawm iterate lawv qhov tseem ceeb.

Piv Txwv:

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;

[saib ntawm piav qhia.tensor.ru]

Txhua yam zoo li zoo, txawm tias raws li qhov ntsuas, tab sis nws yog qhov tsis txaus ntseeg - rau txhua qhov ntawm 20 cov ntaub ntawv nyeem, peb yuav tsum rho tawm 4 nplooj ntawv ntawm cov ntaub ntawv, 32KB ib cov ntaub ntawv - tsis yog qhov ua siab tawv? Thiab lub npe index tbl_fk_org_fk_cli_idx kev xav.

Kho kom raug:

CREATE INDEX ON tbl(fk_cli);

[saib ntawm piav qhia.tensor.ru]

Suddenly - 10 lub sij hawm sai dua, thiab 4 zaug nyeem tsawg dua!

Lwm cov piv txwv ntawm cov xwm txheej ntawm kev siv tsis zoo ntawm cov indexes tuaj yeem pom hauv kab lus DBA: nrhiav tsis muaj txiaj ntsig indexes.

#7: CTE × CTE

Thaum tshwm sim

Hauv kev thov tau qhab nia "rog" CTE los ntawm cov rooj sib txawv, thiab tom qab ntawd txiav txim siab ua nws ntawm lawv JOIN.

Cov ntaub ntawv muaj feem xyuam rau cov versions hauv qab v12 lossis thov nrog WITH MATERIALIZED.

Yuav txheeb xyuas li cas

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

tswv yim pom zoo

Ua tib zoo txheeb xyuas qhov kev thov - thiab Puas xav tau CTEs ntawm no?? Yog tias muaj, ces siv "dictionary" hauv hstore/json raws li tus qauv piav qhia hauv PostgreSQL Antipatterns: cia peb ntaus JOIN hnyav nrog phau ntawv txhais lus.

#8: swap rau disk (temp sau)

Thaum tshwm sim

Kev ua haujlwm ib zaug (kev cais lossis kev tsis sib xws) ntawm ntau cov ntaub ntawv tsis haum rau hauv lub cim xeeb faib rau qhov no.

Yuav txheeb xyuas li cas

-> *
   && temp written > 0

tswv yim pom zoo

Yog hais tias tus nqi ntawm lub cim xeeb siv los ntawm lub lag luam tsis zoo heev tshaj qhov teev tus nqi ntawm parameter work_mem, nws tsim nyog kho nws. Koj tuaj yeem tam sim ntawd hauv config rau txhua tus, lossis koj tuaj yeem dhau los SET [LOCAL] rau ib qho kev thov tshwj xeeb / kev lag luam.

Piv Txwv:

SHOW work_mem;
-- "16MB"

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

[saib ntawm piav qhia.tensor.ru]

Kho kom raug:

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

[saib ntawm piav qhia.tensor.ru]

Rau cov laj thawj pom tseeb, yog tias tsuas yog siv lub cim xeeb thiab tsis disk, ces cov lus nug yuav raug tua sai dua. Nyob rau tib lub sijhawm, ib feem ntawm kev thauj khoom los ntawm HDD kuj raug tshem tawm.

Tab sis koj yuav tsum nkag siab tias koj yuav tsis muaj peev xwm faib ntau ntau thiab ntau lub cim xeeb - tsuas yog yuav tsis txaus rau txhua tus.

# 9: Cov txheeb cais tsis cuam tshuam

Thaum tshwm sim

Lawv nchuav ntau rau hauv cov ntaub ntawv ib zaug, tab sis tsis muaj sijhawm los tsav nws mus ANALYZE.

Yuav txheeb xyuas li cas

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

tswv yim pom zoo

Nqa nws tawm ANALYZE.

Qhov xwm txheej no tau piav qhia ntau ntxiv hauv PostgreSQL Antipatterns: txheeb cais yog txhua yam.

# 10: "ib yam dab tsi tsis ncaj ncees lawm"

Thaum tshwm sim

Muaj kev tos rau lub xauv yuam kev los ntawm kev thov sib tw, lossis tsis muaj peev txheej CPU / hypervisor kho vajtse.

Yuav txheeb xyuas li cas

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

tswv yim pom zoo

Siv sab nraud saib xyuas qhov system server rau thaiv lossis siv cov peev txheej txawv txav. Peb twb tau tham txog peb version ntawm kev npaj cov txheej txheem no rau ntau pua servers S, SѓS, и S, SѓS,.

Tau qhov twg los: www.hab.com