Antywzorce PostgreSQL: szkodliwe JOIN i OR

Bądź ostrożny z operacjami, które przynoszą bufory…
Na przykładzie małego zapytania omówimy kilka uniwersalnych podejść do optymalizacji zapytań w PostgreSQL. Czy warto z nich korzystać, to już twoja decyzja, ale warto o nich wiedzieć.

W kolejnych wersjach PG sytuacja może się zmienić dzięki "inteligencji" planisty, ale dla wersji 9.4/9.6 wygląda to mniej więcej tak samo, jak w podanych przykładach.

Wezmę całkiem realne zapytanie:

SELECT
  TRUE
FROM
  "Dokument" d
INNER JOIN
  "DokumentRozszerzenie" doc_ex
    USING("@Dokument")
INNER JOIN
  "TypDokumentu" t_doc ON
    t_doc."@TypDokumentu" = d."TypDokumentu"
WHERE
  (d."Osoba3" = 19091 or d."Pracownik" = 19091) AND
  d."$Szkic" IS NULL AND
  d."Usunięty" IS NOT TRUE AND
  doc_ex."Stan"[1] IS TRUE AND
  t_doc."TypDokumentu" = 'PlanPrac'
LIMIT 1;

o nazwach tabel i pólDo "rosyjskich" nazw pól i tabel można podchodzić różnie, ale to kwestia gustu. Ponieważ w naszym "Tenzorze" nie mamy zagranicznych deweloperów, a PostgreSQL pozwala nam nadawać nazwy nawet w hieroglifach, jeżeli są zamknięte w cudzysłowach, więc wolimy nazywać obiekty jednoznacznie, aby uniknąć nieporozumień.
Zobaczmy na powstały plan:

[zobacz na explain.tensor.ru]

144ms i prawie 53K buforów — czyli ponad 400MB danych! I będziemy mieli szczęście, jeśli wszystkie te dane będą w cache w momencie naszego zapytania, w przeciwnym razie czas wykonania znacznie się wydłuży przy odczycie z dysku.

Algorytm jest najważniejszy!

Aby jakoś zoptymalizować każde zapytanie, najpierw należy zrozumieć, co ono właściwie ma zrobić.
Na razie pomijamy tworzenie samej struktury bazy danych i ustalmy, że możemy stosunkowo "tanie" przepisać zapytanie i/lub nałożyć na bazę potrzebne nam indeksy.

Więc zapytanie:
— sprawdza istnienie jakiegokolwiek dokumentu
— w potrzebnym nam stanie i określonego typu
— gdzie autorem lub wykonawcą jest potrzebny nam pracownik

JOIN + LIMIT 1

Często deweloperowi łatwiej napisać zapytanie, w którym najpierw łączy dużą liczbę tabel, a potem z tego wszystkiego pozostaje tylko jeden rekord. Ale łatwiejsze dla programisty — nie znaczy skuteczniejsze dla bazy danych.
W naszym przypadku było tylko 3 tabele — a jaki efekt…

Zacznijmy od eliminacji połączenia z tabelą "TypDokumentu", a przy okazji zasugerujemy bazie, że typ rekordu jest u nas unikalny (my to wiemy, ale planista jeszcze tego nie rozumie):

Z T JAKO (
  WYBIERZ
    "@TypDokumentu"
  Z
    "TypDokumentu"
  GDZIE
    "TypDokumentu" = 'PlanPrac'
  LIMIT 1
)
...
GDZIE
  d."TypDokumentu" = (TABLE T)
...

Tak, jeśli tabela/CTE składa się z jednego pola i jednego rekordu, to w PG można pisać nawet tak, zamiast

d."TypDokumentu" = (WYBIERZ "@TypDokumentu" Z T LIMIT 1)

„Leniwe” obliczenia w zapytaniach PostgreSQL

BitmapOr vs UNION

W niektórych przypadkach Bitmap Heap Scan może być bardzo kosztowny — na przykład w naszej sytuacji, gdy dużo rekordów spełnia wymagany warunek. Otrzymaliśmy to przez warunki OR, które przekształciły się w BitmapOr-operację w planie.
Wróćmy do pierwotnego zadania — trzeba znaleźć rekord, który odpowiada dowolnemu z warunków — więc nie ma potrzeby przeszukiwania wszystkich 59K rekordów według obu warunków. Jest sposób, aby rozwiązać jedno warunek, a przejść do drugiego dopiero, gdy nie znaleziono nic według pierwszego. Pomoże nam taka konstrukcja:

(
  WYBIERZ
    ...
  LIMIT 1
)
UNION ALL
(
  WYBIERZ
    ...
  LIMIT 1
)
LIMIT 1

„Zewnętrzny” LIMIT 1 gwarantuje, że wyszukiwanie zostanie zakończone przy znalezieniu pierwszego rekordu. I jeśli zostanie znaleziony już w pierwszym bloku, wykonanie drugiego nie nastąpi (nigdy nie wykonane w planie).

„Ukrywamy pod CASE” złożone warunki

W pierwotnym zapytaniu jest niezwykle niewygodny moment — sprawdzanie stanu po powiązanej tabeli „DokumentRozszerzenie”. Niezależnie od prawdziwości innych warunków w wyrażeniu (na przykład, d."Usunięty" IS NOT TRUE), to połączenie zawsze się wykonuje i „kosztuje zasoby”. Więcej lub mniej ich będzie wydanych — zależy od wielkości tej tabeli.
Ale można zmodyfikować zapytanie tak, aby wyszukiwanie powiązanego rekordu odbywało się tylko wtedy, gdy jest to rzeczywiście konieczne:

WYBIERZ
  ...
Z
  "Dokument" d
GDZIE
  ... /*index cond*/ I
  CASE
    KIEDY "$WersjaRobocza" IS NULL I "Usunięty" IS NOT TRUE TO (
      WYBIERZ
        "Stan"[1] IS TRUE
      Z
        "DokumentRozszerzenie"
      GDZIE
        "@Dokument" = d."@Dokument"
    )
  KONIEC

Ponieważ z powiązanej tabeli nie potrzebujemy żadnego z pól , to mamy możliwość przekształcenia JOIN w warunek za pomocą podzapytania.Zostawimy indeksowane pola „poza nawiasem” CASE, proste warunki z rekordu wprowadzamy do bloku WHEN — i teraz „ciężkie” zapytanie wykonuje się tylko przy przejściu do THEN.
Moje nazwisko „Razem”

Zbieramy wynikowe zapytanie ze wszystkimi opisanymi powyżej mechanikami:

Собираем результирующий запрос со всеми описанными выше механиками:

Z T JAKO (
  WYBIERZ
    "@JenisDokumentu"
  Z
    "JenisDokumentu"
  GDZIE
    "JenisDokumentu" = 'PlanPrac'
)
  (
    WYBIERZ
      PRAWDA
    Z
      "Dokument" d
    GDZIE
      ("Osoba3", "JenisDokumentu") = (19091, (TABELA T)) I
      KIEDY
        KIEDY "$Szkic" JEST NULL I "Usunięty" NIE JEST PRAWDA WTEDY (
          WYBIERZ
            "Stan"[1] JEST PRAWDA
          Z
            "DokumentRozszerzenie"
          GDZIE
            "@Dokument" = d."@Dokument"
        )
      KONIEC
    OGRANICZ DO 1
  )
UNION ALL
  (
    WYBIERZ
      PRAWDA
    Z
      "Dokument" d
    GDZIE
      ("JenisDokumentu", "Pracownik") = ((TABELA T), 19091) I
      KIEDY
        KIEDY "$Szkic" JEST NULL I "Usunięty" NIE JEST PRAWDA WTEDY (
          WYBIERZ
            "Stan"[1] JEST PRAWDA
          Z
            "DokumentRozszerzenie"
          GDZIE
            "@Dokument" = d."@Dokument"
        )
      KONIEC
    OGRANICZ DO 1
  )
OGRANICZ DO 1;

Dostosowujemy [do] indeksów

Wyczulone oko zauważyło, że warunki indeksowane w podblokach UNION trochę się różnią — dzieje się tak, ponieważ już mamy odpowiednie indeksy w tabeli. A gdyby ich nie było — to warto byłoby je stworzyć: Dokument(Osoba3, JenisDokumentu) i Dokument(JenisDokumentu, Pracownik).
o kolejności pól w warunkach ROWZ perspektywy planisty, oczywiście, można napisać i (A, B) = (constA, constB), i (B, A) = (constB, constA). Ale przy zapisie w kolejności pól w indeksie, takie zapytanie jest po prostu łatwiejsze do debugowania później.
Co w planie?

[zobacz na explain.tensor.ru]

Niestety, nie mieliśmy szczęścia i w pierwszym bloku UNION nic nie znaleziono, więc drugi został wykonany. Ale nawet przy tym — tylko 0.037ms i 11 buforów!
Przyspieszyliśmy zapytanie i zmniejszyliśmy „przerzucanie” danych w pamięci kilkudziesięciokrotnie, korzystając ze stosunkowo prostych metod — niezły wynik przy małej ilości kopiuj-wklej. 🙂

Źródło: habr.com