🥇PostgreSQLren aurkako ereduak: JOIN eta OR kaltegarriak

Kontuz buffer-ak ekartzen dituzten eragiketekin...
Kontsulta txiki bat adibide gisa erabiliz, ikus ditzagun PostgreSQL-n kontsultak optimizatzeko planteamendu unibertsal batzuk. Erabili ala ez zure esku dago, baina merezi du horiek ezagutzea.

PGren ondorengo bertsio batzuetan egoera alda daiteke antolatzailea adimentsuagoa den heinean, baina 9.4/9.6rako gutxi gorabehera itxura berdina du, hemengo adibideetan bezala.

Har dezagun eskaera oso erreal bat:

SELECT
  TRUE
FROM
  "Документ" d
INNER JOIN
  "ДокументРасширение" doc_ex
    USING("@Документ")
INNER JOIN
  "ТипДокумента" t_doc ON
    t_doc."@ТипДокумента" = d."ТипДокумента"
WHERE
  (d."Лицо3" = 19091 or d."Сотрудник" = 19091) AND
  d."$Черновик" IS NULL AND
  d."Удален" IS NOT TRUE AND
  doc_ex."Состояние"[1] IS TRUE AND
  t_doc."ТипДокумента" = 'ПланРабот'
LIMIT 1;

taula eta eremu izenei buruzEremu eta taulen "errusiako" izenak modu ezberdinean trata daitezke, baina hau gustu kontua da. Zeren hemen Tensor-en ez dago atzerriko garatzailerik, eta PostgreSQL-k izenak hieroglifoetan ere emateko aukera ematen digu, baldin eta komatxo artean sartuta, orduan nahiago dugu objektuak anbiguotasunik gabe eta argi izendatzea, desadostasunik egon ez dadin.
Ikus dezagun ondoriozko plana:

[ikusi explain.tensor.ru helbidean]

144 ms eta ia 53K buffer - hau da, 400 MB datu baino gehiago! Eta zortea izango dugu denak cachean badaude gure eskaeraren unean, bestela diskotik irakurtzean askoz gehiago beharko da.

Algoritmoa da garrantzitsuena!

Edozein eskaera nolabait optimizatzeko, lehenik zer egin behar duen ulertu behar duzu.
Utz dezagun oraingoz datu-basearen egituraren garapena artikulu honen esparrutik kanpo, eta adostu dezagun nahiko "merke" egin dezakegula. eskaera berridatzi eta/edo behar ditugun gauza batzuk oinarrira jaurti indizeak.

Beraz, eskaera:
— gutxienez dokumenturen bat dagoen egiaztatzen du
- behar dugun egoeran eta mota jakin batean
- non egilea edo interpretea behar dugun langilea den

BATU + MUGA 1

Askotan errazagoa da garatzaile batentzat kontsulta bat idaztea, non taula kopuru handia lehenik batzen den, eta ondoren multzo oso horretatik erregistro bakarra geratzen da. Baina garatzailearentzat errazagoa ez da esan nahi datu-basearentzat eraginkorragoa.
Gure kasuan 3 mahai baino ez zeuden - eta zein da eragina...

Ezabatu dezagun lehenik "Dokumentu mota" taularekin duen konexioa, eta aldi berean esan dezagun datu-baseari gure motako erregistro bakarra da (hau badakigu, baina programatzaileak oraindik ez du ideiarik):

WITH T AS (
  SELECT
    "@ТипДокумента"
  FROM
    "ТипДокумента"
  WHERE
    "ТипДокумента" = 'ПланРабот'
  LIMIT 1
)
...
WHERE
  d."ТипДокумента" = (TABLE T)
...

Bai, taula/CTE erregistro bakarreko eremu bakar batez osatuta badago, orduan PG-n honela idatz dezakezu, ordez

d."ТипДокумента" = (SELECT "@ТипДокумента" FROM T LIMIT 1)

Ebaluazio alferra PostgreSQL kontsultetan

BitmapOr vs UNION

Zenbait kasutan, Bitmap Heap Scan asko kostatuko zaigu, adibidez, gure egoeran, erregistro asko eskatzen diren baldintzak betetzen dituztenean. Lortu dugu zeren EDO egoera BitmapOr bihurtu da- Planaren funtzionamendua.
Itzuli gaitezen jatorrizko arazora - dagokion erregistro bat aurkitu behar dugu edozein baldintzetatik - hau da, ez dago 59K erregistro guztiak bi baldintzetan bilatu beharrik. Baldintza bat lantzeko modua dago, eta joan bigarrenera lehenengoan ezer aurkitu ez denean bakarrik. Ondorengo diseinuak lagunduko digu:

(
  SELECT
    ...
  LIMIT 1
)
UNION ALL
(
  SELECT
    ...
  LIMIT 1
)
LIMIT 1

"Kanpoko" MUGA 1 bermatzen du bilaketa amaitzen dela lehen erregistroa aurkitzen denean. Eta lehen blokean aurkitzen bada, bigarren blokea ez da exekutatuko (inoiz exekutatu dagokionez).

"Baldintza zailak ezkutatzen CASE-n"

Jatorrizko kontsultan oso une deserosoa dago: egoera erlazionatutako "DocumentExtension" taularekin egiaztatzea. Adierazpeneko beste baldintza batzuen egia gorabehera (adibidez, d.“Ezabatua” EZ DA EGIA), konexio hori beti gauzatzen da eta "baliabideak kostatzen ditu". Horietako gehiago edo gutxiago gastatuko dira - taula honen tamainaren araberakoa da.
Baina kontsulta alda dezakezu erlazionatutako erregistro baten bilaketa benetan beharrezkoa denean soilik gerta dadin:

SELECT
  ...
FROM
  "Документ" d
WHERE
  ... /*index cond*/ AND
  CASE
    WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
      SELECT
        "Состояние"[1] IS TRUE
      FROM
        "ДокументРасширение"
      WHERE
        "@Документ" = d."@Документ"
    )
  END

Lotutako taulatik behin gurera Emaitza lortzeko eremuetako bat ere ez da beharrezkoa, orduan JOIN baldintza bihurtzeko aukera dugu azpikontsulta batean.
Utz ditzagun indexatutako eremuak "KASU parentesietatik kanpo", gehitu baldintza errazak erregistrotik NOIZ blokera - eta orain kontsulta "astuna" THEN-ra pasatzen denean bakarrik exekutatzen da.

Nire abizena "Guztira" da

Lortutako kontsulta goian deskribatutako mekanika guztiekin biltzen dugu:

WITH T AS (
  SELECT
    "@ТипДокумента"
  FROM
    "ТипДокумента"
  WHERE
    "ТипДокумента" = 'ПланРабот'
)
  (
    SELECT
      TRUE
    FROM
      "Документ" d
    WHERE
      ("Лицо3", "ТипДокумента") = (19091, (TABLE T)) AND
      CASE
        WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
          SELECT
            "Состояние"[1] IS TRUE
          FROM
            "ДокументРасширение"
          WHERE
            "@Документ" = d."@Документ"
        )
      END
    LIMIT 1
  )
UNION ALL
  (
    SELECT
      TRUE
    FROM
      "Документ" d
    WHERE
      ("ТипДокумента", "Сотрудник") = ((TABLE T), 19091) AND
      CASE
        WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
          SELECT
            "Состояние"[1] IS TRUE
          FROM
            "ДокументРасширение"
          WHERE
            "@Документ" = d."@Документ"
        )
      END
    LIMIT 1
  )
LIMIT 1;

Indizeetara doitzea

Begi trebatu batek UNION azpiblokeetan indexatutako baldintzak zertxobait desberdinak direla ohartu zen; hori da dagoeneko indize egokiak ditugulako mahai gainean. Eta existituko ez balira, mereziko luke sortzea: Dokumentua (Pertson3, DocumentType) и Dokumentua (DocumentType, Langilea).
ROW baldintzetan eremuen ordenari buruzAntolatzailearen ikuspuntutik, noski, idatzi dezakezu (A, B) = (konstA, constB)Eta (B, A) = (konstB, konstA). Baina grabatzerakoan aurkibideko eremuen ordenan, eskaera hori erosoagoa da geroago arazketa egiteko.
Zer dago planean?

[ikusi explain.tensor.ru helbidean]

Zoritxarrez, zorte txarra izan genuen eta lehen UNION blokean ez zen ezer aurkitu, beraz, bigarrena oraindik exekutatu zen. Baina hala ere - bakarrik 0.037 ms eta 11 buffer!
Eskaera azkartu egin dugu eta memorian datuen ponpaketa murriztu dugu hainbat mila aldiz, nahiko teknika sinpleak erabiliz - emaitza ona kopiatu-itsatsi pixka batekin. 🙂

Iturria: www.habr.com

PostgreSQL Antipatterns: JOIN eta OR kaltegarriak