PostgreSQL Antipatterns: schadelijke JOIN's en OR's

Pas op voor operaties die buffers met zich meebrengen...
Laten we, met een kleine query als voorbeeld, eens kijken naar enkele universele benaderingen voor het optimaliseren van query's in PostgreSQL. Of je ze wel of niet gebruikt, is aan jou, maar het is de moeite waard om er meer over te weten.

In sommige volgende versies van PG kan de situatie veranderen naarmate de planner slimmer wordt, maar voor 9.4/9.6 ziet het er ongeveer hetzelfde uit als in de voorbeelden hier.

Laten we een heel reëel verzoek nemen:

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;

over tabel- en veldnamenDe “Russische” namen van velden en tabellen kunnen anders worden behandeld, maar dit is een kwestie van smaak. Omdat de hier bij Tensor er zijn geen buitenlandse ontwikkelaars, en PostgreSQL stelt ons in staat namen te geven, zelfs in hiërogliefen, als ze dat wel zijn tussen aanhalingstekens, dan geven we er de voorkeur aan om objecten ondubbelzinnig en duidelijk te benoemen, zodat er geen discrepanties ontstaan.
Laten we eens kijken naar het resulterende plan:

[kijk naar explain.tensor.ru]

144 ms en bijna 53K buffers - dat wil zeggen, meer dan 400 MB aan gegevens! En we zullen geluk hebben als ze allemaal in de cache staan ​​op het moment van ons verzoek, anders zal het vele malen langer duren als ze vanaf schijf worden gelezen.

Het algoritme is het belangrijkst!

Om elk verzoek op de een of andere manier te optimaliseren, moet u eerst begrijpen wat het moet doen.
Laten we de ontwikkeling van de databasestructuur zelf voorlopig buiten de reikwijdte van dit artikel laten, en het erover eens zijn dat we relatief ‘goedkoop’ kunnen herschrijf het verzoek en/of rol enkele dingen die we nodig hebben op de basis Indexen.

Dus het verzoek:
— controleert het bestaan ​​van ten minste een document
- in de staat die we nodig hebben en van een bepaald type
- waarbij de auteur of uitvoerder de medewerker is die we nodig hebben

DOE MEE + LIMIET 1

Vaak is het gemakkelijker voor een ontwikkelaar om een ​​query te schrijven waarbij eerst een groot aantal tabellen wordt samengevoegd, en dan blijft er slechts één record over van deze hele set. Maar makkelijker voor de ontwikkelaar betekent niet efficiënter voor de database.
In ons geval waren er slechts 3 tafels - en wat is het effect...

Laten we eerst de verbinding met de tabel "Documenttype" verwijderen en tegelijkertijd de database vertellen dat ons typerecord is uniek (dit weten we, maar de planner heeft nog geen idee):

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

Ja, als de tabel/CTE uit een enkel veld van een enkel record bestaat, dan kun je in PG zelfs zo schrijven, in plaats van

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

Luie evaluatie in PostgreSQL-query's

BitmapOr versus UNION

In sommige gevallen zal Bitmap Heap Scan ons veel kosten, bijvoorbeeld in onze situatie waarin behoorlijk wat records aan de vereiste voorwaarde voldoen. We hebben het omdat OR-voorwaarde omgezet in BitmapOr- werking volgens plan.
Laten we terugkeren naar het oorspronkelijke probleem: we moeten een corresponderend record vinden aan iedereen van de voorwaarden - dat wil zeggen dat het niet nodig is om onder beide voorwaarden naar alle 59K-records te zoeken. Er is een manier om één voorwaarde uit te werken, en ga alleen naar de tweede als er niets is gevonden in de eerste. Het volgende ontwerp zal ons helpen:

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

“Extern” LIMIT 1 zorgt ervoor dat de zoekopdracht eindigt wanneer het eerste record wordt gevonden. En als het al in het eerste blok wordt gevonden, wordt het tweede blok niet uitgevoerd (nooit geëxecuteerd met betrekking tot).

“Moeilijke omstandigheden verbergen onder CASE”

Er is een uiterst ongemakkelijk moment in de oorspronkelijke query: het controleren van de status aan de hand van de gerelateerde tabel "DocumentExtension". Ongeacht de waarheid van andere voorwaarden in de uitdrukking (bijvoorbeeld d.“Verwijderd” IS NIET WAAR), wordt deze verbinding altijd uitgevoerd en “kost middelen”. Er zal min of meer van worden uitgegeven - afhankelijk van de grootte van deze tafel.
Maar u kunt de zoekopdracht zo aanpassen dat de zoekopdracht naar een gerelateerd record alleen plaatsvindt als dit echt nodig is:

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

Eenmaal van de gekoppelde tabel naar ons geen van de velden is nodig voor het resultaat, dan hebben we de mogelijkheid om JOIN om te zetten in een voorwaarde voor een subquery.
Laten we de geïndexeerde velden “buiten de CASE-haakjes” laten, eenvoudige voorwaarden uit het record toevoegen aan het WHEN-blok - en nu wordt de “zware” query alleen uitgevoerd wanneer deze wordt doorgegeven aan THEN.

Mijn achternaam is "Totaal"

We verzamelen de resulterende vraag met alle hierboven beschreven mechanismen:

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;

Aanpassen van indexen

Een geoefend oog merkte op dat de geïndexeerde voorwaarden in de UNION-subblokken enigszins verschillen - dit komt omdat we al geschikte indexen op tafel hebben. En als ze niet zouden bestaan, zou het de moeite waard zijn om te creëren: Document(Persoon3, Documenttype) и Document(DocumentType, Werknemer).
over de volgorde van velden in ROW-voorwaardenVanuit het oogpunt van de planner kun je natuurlijk schrijven (A, B) = (constA, constB)En (B, A) = (constB, constA). Maar bij het opnemen in de volgorde van de velden in de index, is een dergelijk verzoek eenvoudigweg handiger om later fouten op te sporen.
Wat staat er in het plan?

[kijk naar explain.tensor.ru]

Helaas hadden we pech en werd er niets gevonden in het eerste UNION-blok, dus het tweede werd alsnog uitgevoerd. Maar toch - alleen 0.037 ms en 11 buffers!
We hebben het verzoek versneld en het pompen van gegevens in het geheugen verminderd enkele duizenden keren, met vrij eenvoudige technieken - een goed resultaat met een beetje kopiëren en plakken. 🙂

Bron: www.habr.com