Antywzorce PostgreSQL: szkodliwe JOIN i OR

Uważaj na operacje, które powodują bufory...
Na przykładzie małego zapytania przyjrzyjmy się uniwersalnym podejściu do optymalizacji zapytań w PostgreSQL. Od Ciebie zależy, czy z nich skorzystasz, czy nie, ale warto o nich wiedzieć.

W niektórych kolejnych wersjach PG sytuacja może się zmienić, gdy harmonogram stanie się mądrzejszy, ale dla wersji 9.4/9.6 wygląda to mniej więcej tak samo, jak w przykładach tutaj.

Weźmy bardzo realną prośbę:

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;

o nazwach tabel i pól„Rosyjskie” nazwy pól i tabel można traktować inaczej, ale to kwestia gustu. Ponieważ tutaj, w Tensorze nie ma zagranicznych programistów, a PostgreSQL pozwala nam nadawać nazwy nawet hieroglifami, jeśli takowe są ujęte w cudzysłów, wówczas wolimy nazywać obiekty jednoznacznie i wyraźnie, aby nie było żadnych rozbieżności.
Spójrzmy na wynikowy plan:

[patrz na explain.tensor.ru]

144 ms i prawie 53 tys. buforów - czyli ponad 400MB danych! I będziemy mieli szczęście, jeśli do czasu naszego żądania wszystkie znajdą się w pamięci podręcznej, w przeciwnym razie odczyt z dysku zajmie wiele razy dłużej.

Algorytm jest najważniejszy!

Aby w jakiś sposób zoptymalizować dowolne żądanie, musisz najpierw zrozumieć, co powinno ono robić.
Zostawmy na razie rozwój samej struktury bazy danych poza zakresem tego artykułu i zgódźmy się, że możemy relatywnie „tanio” przepisz prośbę i/lub zwiń na bazę kilka potrzebnych nam rzeczy indeksami.

Zatem prośba:
— sprawdza istnienie przynajmniej jakiegoś dokumentu
- w takim stanie, jakiego potrzebujemy i określonego typu
- gdzie autorem lub wykonawcą jest pracownik, którego potrzebujemy

DOŁĄCZ + LIMIT 1

Dość często programiście łatwiej jest napisać zapytanie, w którym najpierw łączy się dużą liczbę tabel, a następnie z całego zestawu pozostaje tylko jeden rekord. Ale łatwiejsze dla programisty nie oznacza wydajniejsze dla bazy danych.
W naszym przypadku były tylko 3 stoły - i jaki efekt...

Pozbądźmy się najpierw połączenia z tabelą „Typ dokumentu”, a jednocześnie powiedzmy bazie danych, że nasz rekord typu jest wyjątkowy (wiemy o tym, ale planista nie ma jeszcze pojęcia):

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

Tak, jeśli tabela/CTE składa się z pojedynczego pola pojedynczego rekordu, to w PG można nawet pisać w ten sposób, zamiast

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

Leniwa ocena w zapytaniach PostgreSQL

BitmapOr vs UNION

W niektórych przypadkach skanowanie sterty bitmap będzie nas sporo kosztować - np. w naszej sytuacji, gdy dość dużo rekordów spełnia wymagany warunek. Mamy to, ponieważ Warunek LUB został zmieniony na BitmapOr- działanie w planie.
Wróćmy do pierwotnego problemu - musimy znaleźć odpowiadający mu rekord każdy z warunków - to znaczy, że nie ma potrzeby wyszukiwania wszystkich rekordów 59K w obu warunkach. Istnieje sposób na rozwiązanie jednego warunku i przejdź do drugiego tylko wtedy, gdy w pierwszym nic nie znaleziono. Pomoże nam następujący projekt:

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

„Zewnętrzny” LIMIT 1 gwarantuje, że wyszukiwanie zakończy się w momencie znalezienia pierwszego rekordu. A jeśli zostanie już znaleziony w pierwszym bloku, drugi blok nie zostanie wykonany (nigdy nie stracony w szacunku dla).

„Ukrywanie trudnych warunków pod CASE”

W pierwotnym zapytaniu występuje wyjątkowo niewygodny moment - sprawdzenie statusu w powiązanej tabeli „DocumentExtension”. Niezależnie od prawdziwości innych warunków w wyrażeniu (np. d. „Usunięte” NIE JEST PRAWDĄ), to połączenie jest zawsze wykonywane i „kosztuje zasoby”. Mniej więcej z nich zostanie wydanych - zależy od wielkości tego stołu.
Możesz jednak zmodyfikować zapytanie tak, aby wyszukiwanie powiązanego rekordu następowało tylko wtedy, gdy jest to naprawdę konieczne:

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

Raz z połączonej tabeli do nas żadne z pól nie jest potrzebne do uzyskania wyniku, wtedy mamy możliwość zamiany JOIN na warunek podzapytania.
Zostawmy indeksowane pola „poza nawiasami CASE”, dodajmy proste warunki z rekordu do bloku WHEN – i teraz „ciężkie” zapytanie zostanie wykonane tylko przy przejściu do THEN.

Moje nazwisko to „Total”

Wynikowe zapytanie zbieramy ze wszystkimi mechanikami opisanymi powyżej:

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;

Dostosowanie [do] indeksów

Wprawne oko zauważyło, że indeksowane warunki w podblokach UNION są nieco inne - dzieje się tak dlatego, że mamy już na stole odpowiednie indeksy. A gdyby ich nie było, warto byłoby stworzyć: Dokument (Osoba3, Typ dokumentu) и Dokument (typ dokumentu, pracownik).
o kolejności pól w warunkach ROWZ punktu widzenia planisty oczywiście można pisać (A, B) = (stałaA, stałaB)i (B, A) = (stałaB, stałaA). Ale podczas nagrywania w kolejności pól w indeksie, takie żądanie jest po prostu wygodniejsze do późniejszego debugowania.
Co jest w planie?

[patrz na explain.tensor.ru]

Niestety nie mieliśmy szczęścia i w pierwszym bloku UNION nic nie znaleziono, więc drugi blok został jeszcze wykonany. Ale mimo to - tylko 0.037 ms i 11 buforów!
Przyspieszyliśmy żądanie i ograniczyliśmy pompowanie danych do pamięci kilka tysięcy razy, stosując dość proste techniki - dobry wynik przy odrobinie kopiuj-wklej. 🙂

Źródło: www.habr.com