PostgreSQL-antimönster: Skadliga JOINs och OR

Se upp för operationer som ger buffertar...
Med hjälp av en liten fråga som exempel, låt oss titta på några universella metoder för att optimera frågor i PostgreSQL. Om du använder dem eller inte är upp till dig, men det är värt att veta om dem.

I vissa efterföljande versioner av PG kan situationen förändras när schemaläggaren blir smartare, men för 9.4/9.6 ser det ungefär likadant ut som i exemplen här.

Låt oss ta en mycket verklig begäran:

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;

om tabell- och fältnamnDe "ryska" namnen på fält och tabeller kan behandlas olika, men detta är en smaksak. Eftersom den här på Tensor det finns inga utländska utvecklare, och PostgreSQL tillåter oss att ge namn även i hieroglyfer, om de omsluten av citattecken, då föredrar vi att namnge objekt entydigt och tydligt så att det inte finns några avvikelser.
Låt oss titta på den resulterande planen:

[titta på explain.tensor.ru]

144ms och nästan 53K buffertar - det vill säga mer än 400MB data! Och vi kommer att ha tur om alla är i cachen vid tidpunkten för vår begäran, annars kommer det att ta många gånger längre tid när de läses från disken.

Algoritmen är viktigast!

För att på något sätt optimera en begäran måste du först förstå vad den ska göra.
Låt oss lämna utvecklingen av själva databasstrukturen utanför ramen för denna artikel för tillfället, och håller med om att vi kan relativt "billigt" skriva om begäran och/eller rulla på basen några av de saker vi behöver Index.

Så begäran:
— kontrollerar att det finns åtminstone något dokument
- i det skick vi behöver och av en viss typ
- där författaren eller artisten är den anställd vi behöver

GÅ MED + GRÄNS 1

Ganska ofta är det lättare för en utvecklare att skriva en fråga där ett stort antal tabeller först sammanfogas, och sedan återstår bara en post från hela denna uppsättning. Men lättare för utvecklaren betyder inte effektivare för databasen.
I vårt fall fanns det bara 3 bord - och vad är effekten...

Låt oss först bli av med kopplingen till tabellen "Dokumenttyp" och samtidigt berätta för databasen att vår typpost är unik (vi vet detta, men schemaläggaren har ingen aning än):

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

Ja, om tabellen/CTE består av ett enda fält av en enda post, så kan du i PG till och med skriva så här istället för

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

Lat utvärdering i PostgreSQL-frågor

BitmapOr vs UNION

I vissa fall kommer Bitmap Heap Scan att kosta oss mycket - till exempel i vår situation, när ganska många poster uppfyller kraven. Vi fick det pga ELLER-villkor förvandlas till BitmapOr- drift i plan.
Låt oss återgå till det ursprungliga problemet - vi måste hitta en motsvarande post till någon från villkoren - det vill säga, det finns inget behov av att söka efter alla 59K-poster under båda villkoren. Det finns ett sätt att lösa ett tillstånd, och gå till den andra först när ingenting hittades i den första. Följande design kommer att hjälpa oss:

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

"Extern" LIMIT 1 säkerställer att sökningen avslutas när den första posten hittas. Och om det redan finns i det första blocket kommer det andra blocket inte att köras (aldrig avrättad med respekt för).

"Göljer svåra förhållanden under CASE"

Det finns ett extremt obekvämt ögonblick i den ursprungliga frågan - att kontrollera statusen mot den relaterade tabellen "DocumentExtension". Oavsett sanningen i andra villkor i uttrycket (t.ex. d. "Raderad" ÄR INTE SANT), denna anslutning exekveras alltid och "kostar resurser". Mer eller mindre av dem kommer att spenderas - beror på storleken på detta bord.
Men du kan ändra frågan så att sökningen efter en relaterad post endast sker när det verkligen är nödvändigt:

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

En gång från den länkade tabellen till oss inget av fälten behövs för resultatet, då har vi möjlighet att göra JOIN till ett villkor på en underfråga.
Låt oss lämna de indexerade fälten "utanför CASE-parenteserna", lägg till enkla villkor från posten till WHEN-blocket - och nu exekveras den "tunga" frågan endast när den går till THEN.

Mitt efternamn är "Totalt"

Vi samlar in den resulterande frågan med all mekanik som beskrivs ovan:

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;

Justera [till] index

Ett tränat öga märkte att de indexerade förhållandena i UNION-subblocken är något annorlunda - detta beror på att vi redan har lämpliga index på bordet. Och om de inte fanns skulle det vara värt att skapa: Dokument (Person3, DocumentType) и Dokument (DocumentType, Employee).
om ordningen på fälten i RAD-förhållandenUr planerarens synvinkel kan du förstås skriva (A, B) = (constA, constB)Och (B, A) = (constB, constA). Men vid inspelning i ordningen av fälten i indexet, en sådan begäran är helt enkelt mer bekväm att felsöka senare.
Vad står i planen?

[titta på explain.tensor.ru]

Tyvärr hade vi otur och ingenting hittades i det första UNION-blocket, så det andra avrättades fortfarande. Men ändå - bara 0.037 ms och 11 buffertar!
Vi har påskyndat begäran och minskat datapumpningen i minnet flera tusen gånger, med ganska enkla tekniker - ett bra resultat med lite copy-paste. 🙂

Källa: will.com