ProHoster > Blogg > Administration > PostgreSQL Antipatterns: skadliga JOIN och OR

PostgreSQL Antipatterns: skadliga JOIN och OR

Var försiktig med operationer, buffers som ger...
LÄt oss titta pÄ ett litet exempel för att diskutera nÄgra universella metoder för att optimera frÄgor i PostgreSQL. Huruvida du vill anvÀnda dem eller inte Àr upp till dig, men det Àr viktigt att kÀnna till dem.

I senare versioner av PG kan situationen med "intelligent" planeringsÀndringar se annorlunda ut, men för 9.4/9.6 ser den ungefÀr likadan ut som exemplen hÀr.

HÀr Àr en helt verklig frÄga:

SELECT
  TRUE
FROM
  "Dokument" d
INNER JOIN
  "DokumentUtvidgning" doc_ex
    USING("@Dokument")
INNER JOIN
  "TypDokument" t_doc ON
    t_doc."@TypDokument" = d."TypDokument"
WHERE
  (d."Person3" = 19091 or d."AnstÀlld" = 19091) AND
  d."$Utkast" IS NULL AND
  d."Borttagen" IS NOT TRUE AND
  doc_ex."TillstÄnd"[1] IS TRUE AND
  t_doc."TypDokument" = 'Arbetsplan'
LIMIT 1;

om tabell- och fÀltnamnMan kan ha olika Äsikter om "ryska" namn pÄ fÀlt och tabeller, men det Àr en smakfrÄga. Eftersom vi i "Tensor" inte har utlÀndska utvecklare, och PostgreSQL tillÄter oss att anvÀnda namn som kan vara hieroglyfer, om de Àr omgivna av citattecken, sÄ föredrar vi att namnge objekt tydligt för att undvika missförstÄnd.
LÄt oss titta pÄ den resulterande planen:
PostgreSQL Antipatterns: skadliga JOIN och OR
[titta pÄ explain.tensor.ru]

144ms och nĂ€stan 53K buffers — det vill sĂ€ga mer Ă€n 400MB data! Och vi har tur om allt detta hamnar i cachen vid vĂ„r frĂ„ga, annars blir den betydligt lĂ„ngsammare nĂ€r vi lĂ€ser frĂ„n disken.

Algoritmen Àr viktigast av allt!

För att pÄ nÄgot sÀtt optimera en frÄga mÄste vi först förstÄ vad den överhuvudtaget ska göra.
Vi lÀmnar utvecklingen av databasstrukturen utanför denna artikel för nu, och vi enas om att vi kan relativt "billigt" skriva om frÄgan och/eller applicera relevanta index pÄ databasen.

SÄ, frÄgan:
— kontrollerar existensen av Ă„tminstone ett dokument
— i det önskade tillstĂ„ndet och av den specifika typen
— dĂ€r författaren eller utföraren Ă€r den anstĂ€llda vi behöver

JOIN + LIMIT 1

Det Àr ganska ofta lÀttare för utvecklaren att skriva en frÄga dÀr en stor mÀngd tabeller först kopplas ihop, och sedan fÄr man bara kvar en enstaka post. Men det som Àr enklare för utvecklaren betyder inte nödvÀndigtvis att det Àr mer effektivt för databasen.
I vĂ„rt fall var det bara 3 tabeller — och vilket resultat


LÄt oss börja med att ta bort anslutningen till tabellen "TypDokument", och dessutom lÄta databasen veta att posttypen Àr unik (det vet vi, men planen har inte kommit pÄ det Àn):

WITH T AS (
  SELECT
    "@TypDokument"
  FROM
    "TypDokument"
  WHERE
    "TypDokument" = 'Arbetsplan'
  LIMIT 1
)
...
WHERE
  d."TypDokument" = (TABLE T)
...

Ja, om tabellen/CTE bestÄr av ett enda fÀlt med en enda post, kan vi i PG skriva Àven sÄ hÀr, istÀllet för

d."TypDokument" = (SELECT "@TypDokument" FROM T LIMIT 1)

«Lata» berÀkningar i frÄgor i PostgreSQL

BitmapOr vs UNION

I vissa fall kan Bitmap Heap Scan bli mycket kostsamt för oss — till exempel i vĂ„r situation dĂ€r ett betydande antal poster faller under det angivna villkoret. Vi fick det pĂ„ grund av OR-villkoret som har blivit en BitmapOr-operation i planen.
LĂ„t oss Ă„tergĂ„ till den ursprungliga uppgiften — vi mĂ„ste hitta en post som uppfyller vilket som helst av villkoren — det vill sĂ€ga det Ă€r onödigt att söka igenom alla 59K poster för bĂ„da villkoren. Det finns ett sĂ€tt att hantera ett villkor, och gĂ„ vidare till det andra endast om inget hittas pĂ„ det första. Vi kommer att anvĂ€nda följande konstruktion:

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

Den "yttre" LIMIT 1 garanterar att sökningen avslutas sÄ snart den första posten hittas. Och om den finns i den första blocket, kommer den andra delen inte att köras (never executed i planen).

"Döljer under CASE" komplexa villkor

I den ursprungliga frĂ„gan finns det en extremt obekvĂ€m punkt — kontrollen av tillstĂ„ndet via den relaterade tabellen "DokumentUtvidgning". Oavsett sanning av övriga villkor i uttrycket (till exempel, d."Borttagen" IS NOT TRUE), utförs denna koppling alltid och "kostar resurser". Mer eller mindre resurser kommer att anvĂ€ndas — beroende pĂ„ storleken av denna tabell.
Men vi kan modifiera frÄgan sÄ att sökningen av den relaterade posten bara sker nÀr det verkligen Àr nödvÀndigt:

SELECT
  ...
FROM
  "Dokument" d
WHERE
  ... 
/*index cond*/ AND
  CASE
    WHEN "$Utkast" IS NULL AND "Borttagen" IS NOT TRUE THEN (
      SELECT
        "TillstÄnd"[1] IS TRUE
      FROM
        "DokumentUtvidgning"
      WHERE
        "@Dokument" = d."@Dokument"
    )
  END

Eftersom vi inte behöver nĂ„got av fĂ€lten frĂ„n den relaterade tabellen , har vi möjlighet att omvandla JOIN till ett villkor i en underfrĂ„ga.Vi lĂ€mnar de indexerbara fĂ€lten "utanför" CASE, och de enklare villkoren placeras i WHEN-blocket — och nu utförs den "tunga" frĂ„gan endast vid ett övergĂ„ng till THEN.
Mitt efternamn Àr "Totalt"

Vi samlar den resulterande frÄgan med alla de ovan beskrivna mekanismerna:

Vi samlar in den resulterande förfrÄgan med alla ovan beskrivna mekanismer:

MED T SOM (
  VÄLJ
    "@Dokumenttyp"
  FRÅN
    "Dokumenttyp"
  DÄR
    "Dokumenttyp" = 'Arbetsplan'
)
  (
    VÄLJ
      SANT
    FRÅN
      "Dokument" d
    DÄR
      ("Person3", "Dokumenttyp") = (19091, (TABELL T)) OCH
      FALL
        NÄR "$Utkast" ÄR NULL OCH "Borttagen" ÄR INTE SANT DÅ (
          VÄLJ
            "Status"[1] ÄR SANT
          FRÅN
            "Dokumentutvidgning"
          DÄR
            "@Dokument" = d."@Dokument"
        )
      SLUT
    BEGRÄNSA 1
  )
UNION ALL
  (
    VÄLJ
      SANT
    FRÅN
      "Dokument" d
    DÄR
      ("Dokumenttyp", "AnstÀlld") = ((TABELL T), 19091) OCH
      FALL
        NÄR "$Utkast" ÄR NULL OCH "Borttagen" ÄR INTE SANT DÅ (
          VÄLJ
            "Status"[1] ÄR SANT
          FRÅN
            "Dokumentutvidgning"
          DÄR
            "@Dokument" = d."@Dokument"
        )
      SLUT
    BEGRÄNSA 1
  )
BEGRÄNSA 1;

Justera [för] index

Ett trĂ€nat öga har mĂ€rkt att de indexerade villkoren i UNION-delarna skiljer sig nĂ„got — detta beror pĂ„ att vi redan har lĂ€mpliga index pĂ„ tabellen. Om de inte fanns — skulle det vara bra att skapa dem: Dokument(Person3, Dokumenttyp) och Dokument(Dokumenttyp, AnstĂ€lld).
om ordningen pÄ fÀlten i ROW-villkorUr planerarens synvinkel kan man förstÄs ocksÄ skriva (A, B) = (constA, constB), och (B, A) = (constB, constA). Men vid skrivningen i ordningen pÄ fÀlten i indexet, Àr en sÄdan frÄga bara enklare att felsöka senare.
Vad i planen?
PostgreSQL Antipatterns: skadliga JOIN och OR
[titta pÄ explain.tensor.ru]

TyvĂ€rr hade vi otur, och i den första UNION-delen hittades ingenting, sĂ„ den andra blev faktiskt utförd. Men Ă€ven i detta fall — totalt 0.037ms och 11 buffertar!
Vi snabbade upp frĂ„gan och minskade "pumpningen" av data i minnet tusentals gĂ„nger, genom att anvĂ€nda ganska enkla metoder — ett bra resultat för liten kopierings- och klistring.

KĂ€lla: habr.com

Köp pĂ„litlig webbhotell med DDoS-skydd, VPS VDS-servrar đŸ”„ Köp pĂ„litlig webbhotell med DDoS-skydd, VPS VDS-servrar | ProHoster