Sick SQL kontsultak egiteko errezetak

Orain dela hilabete batzuk iragarri genuen azaldu.tentsorea.ru - publiko kontsulta-planak analizatzeko eta ikusteko zerbitzua PostgreSQL-ra.

Dagoeneko 6000 aldiz baino gehiago erabili duzu, baina oharkabean pasa daitekeen funtzio erabilgarri bat da egiturazko arrastoak, hau bezalako itxura dutenak:

Entzun itzazu, eta zure eskaerak "leun eta zetatsu bihurtuko dira". 🙂

Baina serioski, eskaera bat motel eta baliabide gose egiten duten egoera asko tipikoak dira eta planaren egituraren eta datuen arabera antzeman daitezke.

Kasu honetan, garatzaile bakoitzak ez du bere kabuz optimizazio-aukerarik bilatu behar, bere esperientzian soilik oinarrituz - hemen gertatzen ari dena esan dezakegu, zein izan daitekeen arrazoia eta konponbide bati nola heldu. Horixe egin genuen.

Ikus ditzagun kasu hauek hurbilagotik: nola definitzen diren eta zer gomendiotara eramaten duten.

Gaian hobeto murgiltzeko, lehenik eta behin dagokion blokea entzun dezakezu Nire txostena PGConf.Russia 2020-n, eta gero bakarrik adibide bakoitzaren azterketa zehatza egitera pasa:

#1: indizea "gutxietsia"

Sortzen denean

Erakutsi "LLC Kolokolchik" bezeroaren azken faktura.

Nola identifikatu

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

Gomendioak

Erabilitako indizea zabaldu ordena-eremuekin.

Adibidea:

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;

[ikusi explain.tensor.ru helbidean]

Berehala ohar zaitezke indizeari 100 erregistro baino gehiago kendu zitzaizkiola, gero guztiak ordenatu zirela, eta gero bakarra geratu zela.

Zuzentzen:

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

[ikusi explain.tensor.ru helbidean]

Halako lagin primitibo batean ere - 8.5 aldiz azkarragoa eta 33 aldiz irakurketa gutxiago. Zenbat eta "gertaera" gehiago izan balio bakoitzeko, orduan eta nabariagoa izango da eragina fk.

Kontuan izan dut indize horrek "aurrizkia" indize gisa funtzionatuko duela lehen baino okerrago beste kontsultetarako fk, non ordenatu pk ez zegoen eta ez dago (honi buruz gehiago irakur dezakezu Eraginkortasunik gabeko indizeak aurkitzeari buruzko nire artikuluan). Barne, normal emango du atzerriko gakoen laguntza esplizitua eremu honetan.

#2: indize-ebakidura (BitmapAnd)

Sortzen denean

Erakutsi "LLC Kolokolchik" bezeroarentzako akordio guztiak, "NAO Buttercup"-en izenean egindakoak.

Nola identifikatu

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

Gomendioak

sortzen indize konposatua jatorrizko bi eremuen arabera edo lehendik daudenetako bat zabaldu bigarreneko eremuekin.

Adibidea:

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

[ikusi explain.tensor.ru helbidean]

Zuzentzen:

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

[ikusi explain.tensor.ru helbidean]

Hemen ordaina txikiagoa da, Bitmap Heap Scan nahiko eraginkorra baita. Baina hala ere 7 aldiz azkarragoa eta 2.5 aldiz irakurketa gutxiago.

#3: Batu indizeak (BitmapOr)

Sortzen denean

Erakutsi "gu" zaharrenak edo esleitu gabeko lehen 20 eskaera prozesatzeko, zureak lehentasunez.

Nola identifikatu

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

Gomendioak

erabiltzea BATASUNA [GUZTIAK] baldintza-or-bloke bakoitzeko azpikontsultak konbinatzeko.

Adibidea:

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;

[ikusi explain.tensor.ru helbidean]

Zuzentzen:

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

[ikusi explain.tensor.ru helbidean]

Beharrezko 20 erregistro guztiak lehen blokean berehala jaso zirela aprobetxatu genuen, beraz, bigarrena, Bitmap Heap Scan "garestiago"arekin, ez zen exekutatu ere egin - azkenean. 22 aldiz azkarragoa, 44 aldiz irakurketa gutxiago!

Optimizazio metodo honi buruzko istorio zehatzagoa adibide zehatzak erabiliz artikuluetan irakur daiteke PostgreSQL Antipatterns: JOIN eta OR kaltegarriak и PostgreSQL Antipatterns: bilaketaren finketa errepikakor baten istorioa, edo "Optimizazioa aurrera eta atzera".

Bertsio orokortua hainbat gakotan oinarritutako hautaketa ordenatua (eta ez bakarrik const/NULL bikotea) eztabaidatzen da artikuluan SQL HowTo: while begizta bat idaztea zuzenean kontsultan, edo "Oinarrizko hiru urrats".

#4: Alferrikako gauza asko irakurtzen ditugu

Sortzen denean

Oro har, lehendik dagoen eskaera bati "beste iragazki bat erantsi" nahi duzunean sortzen da.

«Eta ez duzu berdina, baina perlazko botoiekin? " "The Diamond Arm" filma

Adibidez, goiko zeregina aldatuz, erakutsi prozesatzeko lehen 20 eskaera "kritiko" zaharrenak, haien helburua edozein dela ere.

Nola identifikatu

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

Gomendioak

Sortu [gehiago] espezializatua NON baldintza duen indizea edo gehitu eremu gehigarriak aurkibidean.

Iragazkiaren egoera "estatikoa" bada zure helburuetarako, hau da ez du hedapena suposatzen baloreen zerrenda etorkizunean - hobe da NON indizea erabiltzea. Hainbat egoera boolear/enumeratu ondo sartzen dira kategoria honetan.

Iragazte-baldintza bada esanahi desberdinak har ditzake, orduan hobe da indizea eremu hauekin zabaltzea - ​​goiko BitmapAnd-ekin gertatzen den bezala.

Adibidea:

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;

[ikusi explain.tensor.ru helbidean]

Zuzentzen:

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

[ikusi explain.tensor.ru helbidean]

Ikusten duzunez, iragazketa erabat desagertu da planetik, eta eskaera bihurtu da 5 aldiz azkarrago.

#5: mahai eskasa

Sortzen denean

Zereginen prozesatzeko ilara propioa sortzeko hainbat saiakerak, mahai gainean dauden erregistroen eguneratze/ezabatze kopuru handi batek "hildako" erregistro kopuru handi bat eragiten duenean.

Nola identifikatu

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

Gomendioak

Egin eskuz aldizka HUTSEAN [BETEA] edo behar bezain maiz prestakuntza lortzea autohutsean bere parametroak finkatuz, besteak beste mahai zehatz baterako.

Kasu gehienetan, horrelako arazoak kontsulta-diseinu eskasak eragiten ditu negozio-logikatik deitzean eztabaidatutakoak bezala PostgreSQL Antipatterns: "hildakoen" horden aurka borrokatzea.

Baina ulertu behar duzu VACUUM FULL-ek ere agian ez duela beti lagunduko. Horrelako kasuetarako, merezi du artikuluko algoritmoa ezagutzea DBA: HUTSAK huts egiten duenean, mahaia eskuz garbitzen dugu.

#6: aurkibidearen "erditik" irakurtzea

Sortzen denean

Badirudi pixka bat irakurri dugula, eta dena indexatu egin dela, eta ez dugu gehiegizko inor iragazi, baina hala ere nahi baino orrialde gehiago irakurtzen ditugu.

Nola identifikatu

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

Gomendioak

Begiratu arretaz erabilitako indizearen egitura eta kontsultan zehaztutako gako-eremuak - ziurrenik indizearen zati bat ez dago ezarrita. Ziurrenik antzeko indize bat sortu beharko duzu, baina aurrizki-eremurik gabe edo haien balioak errepikatzen ikasi.

Adibidea:

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;

[ikusi explain.tensor.ru helbidean]

Badirudi dena ondo dagoela, baita indizearen arabera ere, baina nolabait susmagarria da - irakurritako 20 erregistro bakoitzeko 4 datu kendu behar izan ditugu, 32 KB erregistro bakoitzeko - ez al da lodia? Eta indizearen izena tbl_fk_org_fk_cli_idx gogoeta eragitea.

Zuzentzen:

CREATE INDEX ON tbl(fk_cli);

[ikusi explain.tensor.ru helbidean]

Bat-batean - 10 aldiz azkarrago, eta 4 aldiz gutxiago irakurtzeko!

Indizeen erabilera ez-eraginkorra duten egoeren beste adibide batzuk ikus daitezke artikuluan DBA: alferrikako indizeak aurkitzea.

#7: CTE × CTE

Sortzen denean

Eskaeran CTE "koipea" lortu zuen mahai ezberdinetatik, eta gero beraien artean egitea erabaki zuten JOIN.

Kasua garrantzitsua da v12 azpiko bertsioetarako edo eskaerarekin WITH MATERIALIZED.

Nola identifikatu

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

Gomendioak

Arretaz aztertu eskaera - eta Hemen beharrezkoak al dira CTEak?? Bai bada, orduan aplikatu "hiztegia" hstore/json-en atalean azaldutako ereduaren arabera PostgreSQL Antipatterns: sakatu dezagun JOIN astuna hiztegi batekin.

#8: aldatu diskora (tenperatura idatzita)

Sortzen denean

Erregistro kopuru handi baten behin-behineko prozesatzea (ordenatzea edo bakartzea) ez da horretarako esleitutako memorian sartzen.

Nola identifikatu

-> *
   && temp written > 0

Gomendioak

Eragiketak erabiltzen duen memoria kopurua parametroaren zehaztutako balioa asko gainditzen ez badu lan_mem, merezi du zuzentzea. Berehala konfigurazioan sartu dezakezu guztiontzat, edo egin dezakezu SET [LOCAL] eskaera/transakzio zehatz baterako.

Adibidea:

SHOW work_mem;
-- "16MB"

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

[ikusi explain.tensor.ru helbidean]

Zuzentzen:

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

[ikusi explain.tensor.ru helbidean]

Ageriko arrazoiengatik, memoria bakarrik erabiltzen bada eta ez diskoa, orduan kontsulta askoz azkarrago exekutatuko da. Aldi berean, HDDko kargaren zati bat ere kentzen da.

Baina ulertu behar duzu ez duzula beti memoria asko eta asko esleitu ahal izango; besterik gabe, ez da denentzat nahikoa izango.

#9: garrantzirik gabeko estatistikak

Sortzen denean

Datu-basera asko isurtzen zuten aldi berean, baina ez zuten urruntzeko astirik izan ANALYZE.

Nola identifikatu

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

Gomendioak

Egin ezazu ANALYZE.

Egoera hau zehatzago azaltzen da PostgreSQL Antipatterns: estatistikak dena dira.

#10: "zerbait gaizki joan da"

Sortzen denean

Eskaera lehiakide batek ezarritako blokeo baten itxaron bat egon zen, edo ez zegoen CPU/hipervisorearen hardware baliabide nahikorik.

Nola identifikatu

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

Gomendioak

Erabili kanpokoa jarraipen-sistema baliabideak blokeatzeko edo anormalaren kontsumoa egiteko zerbitzaria. Dagoeneko hitz egin dugu ehunka zerbitzarietarako prozesu hau antolatzeko dugun bertsioari buruz Hemen и Hemen.

Iturria: www.habr.com