ProHoster > Blog > administracja > PostgreSQL Antywzorce: „Musi być tylko jeden!”

PostgreSQL Antywzorce: „Musi być tylko jeden!”

W SQL opisujesz „co” chcesz osiągnąć, a nie „jak” powinno to zostać wykonane. Dlatego honorowe miejsce zajmuje problem tworzenia zapytań SQL w stylu „jak się słyszy, tak się pisze” wraz z cechy obliczania warunków w SQL.

Dziś na niezwykle prostych przykładach zobaczmy do czego może to prowadzić w kontekście użytkowania GROUP/DISTINCT и LIMIT z nimi.

Teraz, jeśli napisałeś w żądaniu „najpierw połącz te znaki, a potem wyrzuć wszystkie duplikaty, powinien zostać tylko jeden skopiuj dla każdego klucza” - dokładnie tak to będzie działać, nawet jeśli połączenie w ogóle nie było potrzebne.

Czasem ma się szczęście i „po prostu działa”, czasem ma to nieprzyjemny wpływ na wydajność, a czasem daje efekty zupełnie nieoczekiwane z punktu widzenia dewelopera.

PostgreSQL Antywzorce: „Musi być tylko jeden!”
No może nie aż tak spektakularnie, ale...

„Słodka para”: DOŁĄCZ + WYRÓŻNIJ

SELECT DISTINCT
  X.*
FROM
  X
JOIN
  Y
    ON Y.fk = X.pk
WHERE
  Y.bool_condition;

Byłoby jasne, czego chcieli wybierz rekordy X, dla których w Y znajdują się rekordy powiązane ze spełnionym warunkiem. Napisałem prośbę za pośrednictwem JOIN — otrzymał kilka wartości pk kilka razy (dokładnie ile odpowiednich wpisów pojawiło się w Y). Jak usunąć? Z pewnością DISTINCT!

Jest to szczególnie „satysfakcjonujące”, gdy na każdy rekord X przypada kilkaset powiązanych rekordów Y, a następnie bohatersko usuwane są duplikaty…

PostgreSQL Antywzorce: „Musi być tylko jeden!”

Jak naprawić? Na początek zdaj sobie sprawę, że problem można zmodyfikować „wybierz rekordy X, dla których w Y jest CO NAJMNIEJ JEDEN powiązany ze spełnionym warunkiem” - w końcu nie potrzebujemy niczego z samej płyty Y.

Zagnieżdżone ISTNIEJE

SELECT
  *
FROM
  X
WHERE
  EXISTS(
    SELECT
      NULL
    FROM
      Y
    WHERE
      fk = X.pk AND
      bool_condition
    LIMIT 1
  );

Niektóre wersje PostgreSQL rozumieją, że w EXISTS wystarczy znaleźć pierwszy wpis, który się pojawi, starsze nie. Dlatego wolę zawsze wskazywać LIMIT 1 wewnątrz EXISTS.

POŁĄCZENIE BOCZNE

SELECT
  X.*
FROM
  X
, LATERAL (
    SELECT
      Y.*
    FROM
      Y
    WHERE
      fk = X.pk AND
      bool_condition
    LIMIT 1
  ) Y
WHERE
  Y IS DISTINCT FROM NULL;

Ta sama opcja pozwala w razie potrzeby natychmiastowo zwrócić część danych ze znalezionego powiązanego rekordu Y. Podobną opcję omówiono w artykule „Antywzorce PostgreSQL: rzadki rekord osiągnie środek JOIN”.

„Po co płacić więcej”: DISTINCT [ON] + LIMIT 1

Dodatkową zaletą takich przekształceń zapytań jest możliwość łatwego ograniczenia wyszukiwania rekordów w przypadku, gdy potrzebny jest tylko jeden lub kilka z nich, jak w poniższym przypadku:

SELECT DISTINCT ON(X.pk)
  *
FROM
  X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

Teraz czytamy żądanie i staramy się zrozumieć, co proponuje DBMS:

  • łączenie znaków
  • unikalny przez X.pk
  • z pozostałych wpisów wybierz jeden

Więc co dostałeś? „Tylko jeden wpis” od unikalnych - a jeśli weźmiemy ten z nieunikalnych, czy wynik jakoś się zmieni?.. „A jeśli nie ma różnicy, po co płacić więcej?”

SELECT
  *
FROM
  (
    SELECT
      *
    FROM
      X
    -- сюда можно подсунуть подходящих условий
    LIMIT 1 -- +1 Limit
  ) X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

I dokładnie ten sam temat z GROUP BY + LIMIT 1.

„Muszę tylko zapytać”: ukryta GRUPA + LIMIT

Podobne rzeczy zdarzają się w różnych kontrole braku pustki znaki lub CTE w miarę postępu żądania:

...
CASE
  WHEN (
    SELECT
      count(*)
    FROM
      X
    LIMIT 1
  ) = 0 THEN ...

Funkcje agregujące (count/min/max/sum/...) są pomyślnie wykonywane na całym zestawie, nawet bez wyraźnych instrukcji GROUP BY. Tylko z LIMIT nie są zbyt przyjaźni.

Deweloper może pomyśleć „jeśli są tam zapisy, to potrzebuję nie więcej niż LIMIT”. Ale nie rób tego! Ponieważ dla bazy jest to:

  • policz, czego chcą według wszelkich zapisów
  • podaj tyle linijek, ile proszą

W zależności od warunków docelowych właściwe jest dokonanie jednego z następujących podstawień:

  • (count + LIMIT 1) = 0 na NOT EXISTS(LIMIT 1)
  • (count + LIMIT 1) > 0 na EXISTS(LIMIT 1)
  • count >= N na (SELECT count(*) FROM (... LIMIT N))

„Ile zawiesić w gramach”: DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Naiwny programista może szczerze wierzyć, że żądanie przestanie być wykonywane. gdy tylko znajdziemy 1 $ z pierwszych różnych wartości, które się pojawią.

Kiedyś w przyszłości może to zadziałać i będzie działać dzięki nowemu węzłowi Skanowanie pomijania indeksu, którego wdrożenie jest obecnie opracowywane, ale jeszcze nie teraz.

Na razie najpierw wszystkie rekordy zostaną pobrane, są unikalne i tylko z nich zostanie zwrócona żądana kwota. Jest to szczególnie smutne, jeśli chcieliśmy czegoś takiego $ 1 = 4, a w tabeli znajdują się setki tysięcy rekordów...

Aby nie smucić się na próżno, zastosujmy zapytanie rekurencyjne „DISTINCT jest dla biednych” z Wiki PostgreSQL:

PostgreSQL Antywzorce: „Musi być tylko jeden!”

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

Dodaj komentarz