🥇PostgreSQL antipatterns: kaitīgi JOIN un VAI

Uzmanieties no darbībām, kas rada buferus...
Izmantojot nelielu vaicājumu kā piemēru, apskatīsim dažas universālas pieejas vaicājumu optimizēšanai programmā PostgreSQL. Tas, vai jūs tos izmantojat, ir atkarīgs no jums, taču ir vērts par tiem zināt.

Dažās turpmākajās PG versijās situācija var mainīties, jo plānotājs kļūst gudrāks, taču versijām 9.4/9.6 tas izskatās aptuveni tāds pats kā šeit esošajos piemēros.

Pieņemsim ļoti reālu pieprasījumu:

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;

par tabulu un lauku nosaukumiemLauku un tabulu nosaukumus “krieviski” var traktēt dažādi, taču tas ir gaumes jautājums. Tāpēc ka šeit, Tensorā nav ārzemju izstrādātāju, un PostgreSQL ļauj mums dot vārdus pat hieroglifos, ja tie ielikts pēdiņās, tad mēs labprātāk nosaucam objektus nepārprotami un skaidri, lai nebūtu neatbilstību.
Apskatīsim iegūto plānu:

[apskatiet skaidro.tensor.ru]

144ms un gandrīz 53K buferi - tas ir, vairāk nekā 400 MB datu! Un mums veiksies, ja līdz mūsu pieprasījuma brīdim tie visi būs kešatmiņā, pretējā gadījumā lasīšana no diska prasīs vairākas reizes ilgāku laiku.

Algoritms ir vissvarīgākais!

Lai kaut kā optimizētu jebkuru pieprasījumu, vispirms ir jāsaprot, kas tam jādara.
Pašas datu bāzes struktūras izstrādi pagaidām atstāsim ārpus šī raksta darbības jomas un piekrītam, ka varam salīdzinoši “lēti” pārrakstīt pieprasījumu un/vai uzripiniet uz pamatnes dažas mums nepieciešamās lietas Indeksi.

Tātad pieprasījums:
— pārbauda vismaz kāda dokumenta esamību
- mums vajadzīgajā stāvoklī un noteikta veida
- kur autors vai izpildītājs ir mums vajadzīgais darbinieks

PIEVIENOTIES + IEROBEŽOTS 1

Diezgan bieži izstrādātājam ir vieglāk uzrakstīt vaicājumu, kurā vispirms tiek savienots liels skaits tabulu, un tad no visas šīs kopas paliek tikai viens ieraksts. Bet vieglāk izstrādātājam nenozīmē efektīvāku datu bāzi.
Mūsu gadījumā bija tikai 3 tabulas - un kāds ir efekts...

Vispirms atbrīvosimies no savienojuma ar tabulu "Dokumenta tips" un tajā pašā laikā paziņosim datubāzei, ka mūsu tipa ieraksts ir unikāls (mēs to zinām, bet plānotājam vēl nav ne jausmas):

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

Jā, ja tabula/CTE sastāv no viena ieraksta viena lauka, tad PG var pat rakstīt šādi, nevis

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

Slinks novērtējums PostgreSQL vaicājumos

BitmapOr vs UNION

Dažos gadījumos Bitmap Heap Scan mums izmaksās dārgi – piemēram, mūsu situācijā, kad diezgan daudz ierakstu atbilst vajadzīgajam nosacījumam. Mēs to saņēmām, jo VAI nosacījums pārvērtās par BitmapOr- darbība plānā.
Atgriezīsimies pie sākotnējās problēmas – jāatrod atbilstošs ieraksts jebkurš no nosacījumiem - tas ir, nav jāmeklē visi 59K ieraksti abos apstākļos. Ir veids, kā izstrādāt vienu nosacījumu, un iet uz otro tikai tad, kad pirmajā nekas netika atrasts. Mums palīdzēs šāds dizains:

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

“Ārējais” LIMIT 1 nodrošina, ka meklēšana beidzas, kad tiek atrasts pirmais ieraksts. Un, ja tas jau ir atrasts pirmajā blokā, otrais bloks netiks izpildīts (nekad nav izpildīts Cieņā).

“Sarežģītu apstākļu slēpšana saskaņā ar CASE”

Sākotnējā vaicājumā ir ārkārtīgi neērts brīdis - statusa pārbaude ar saistīto tabulu “DocumentExtension”. Neatkarīgi no citu izteiksmes nosacījumu patiesuma (piemēram, d. “Izdzēsts” NAV PATIESA), šis savienojums vienmēr tiek izpildīts un “maksā resursus”. Vairāk vai mazāk no tiem tiks iztērēti - atkarīgs no šī galda izmēra.
Bet jūs varat modificēt vaicājumu tā, lai saistītā ieraksta meklēšana notiktu tikai tad, kad tas patiešām ir nepieciešams:

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

Vienreiz no saistītās tabulas mums neviens no laukiem nav nepieciešams rezultātam, tad mums ir iespēja pārvērst JOIN par nosacījumu apakšvaicājumā.
Atstāsim indeksētos laukus “ārpus CASE iekavās”, pievienosim vienkāršus nosacījumus no ieraksta blokam WHEN - un tagad “smagais” vaicājums tiek izpildīts tikai pārejot uz THEN.

Mans uzvārds ir "Kopā"

Mēs apkopojam iegūto vaicājumu ar visu iepriekš aprakstīto mehāniku:

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;

Indeksu pielāgošana [uz]

Apmācīta acs pamanīja, ka indeksētie nosacījumi UNION apakšblokos ir nedaudz atšķirīgi - tas ir tāpēc, ka mums jau ir piemēroti indeksi uz tabulas. Un, ja tie nepastāvētu, būtu vērts izveidot: Dokuments (3. persona, dokumenta veids) и Dokuments (dokumenta veids, darbinieks).
par lauku secību ROW nosacījumosNo plānotāja viedokļa, protams, var rakstīt (A, B) = (constA, constB)Un (B, A) = (constB, constA). Bet ierakstot rādītāja lauku secībā, šādu pieprasījumu ir vienkārši ērtāk vēlāk atkļūdot.
Kas ir plānā?

[apskatiet skaidro.tensor.ru]

Diemžēl mums nepaveicās un pirmajā UNION blokā nekas netika atrasts, tāpēc otro tomēr izpildīja. Bet pat tā - tikai 0.037 ms un 11 buferi!
Mēs esam paātrinājuši pieprasījumu un samazinājuši datu iesūknēšanu atmiņā vairākus tūkstošus reižu, izmantojot diezgan vienkāršus paņēmienus - labs rezultāts ar nelielu copy-paste. 🙂

Avots: www.habr.com

PostgreSQL antipatterns: kaitīgi JOIN un OR