Antypatterny PostgreSQL: „Musi pozostać tylko jeden!”

W SQL opisujesz 'co' chcesz otrzymać, a nie 'jak' to ma być wykonane. Dlatego problem programowania zapytań SQL w stylu 'jak się słyszy, tak się pisze' zajmuje honorowe miejsce obok cech obliczania warunków w SQL.

Dziś na bardzo prostych przykładach zobaczymy, do czego to może prowadzić w kontekście użycia GROUP/DISTINCT i LIMIT razem z nimi.

Jeśli napisałeś w zapytaniu 'najpierw połącz te tabelki, a potem usuń wszystkie duplikaty, musi pozostać tylko jeden egzemplarz według każdego klucza' — dokładnie tak to będzie działać, nawet jeśli połączenie wcale nie było potrzebne.

Czasami szczęście sprzyja i to 'po prostu działa', czasami ma to nieprzyjemny wpływ na wydajność, a czasami daje absolutnie nieoczekiwane efekty z punktu widzenia programisty.

Cóż, może nie tak spektakularne, ale…

'Słodka parka': JOIN + DISTINCT

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

Jak by to było oczywiste, że chcieliśmy wybrać takie rekordy X, dla których w Y są związane z realizowanym warunkiem. Napisano zapytanie przez JOIN — otrzymano jakieś wartości pk wiele razy (dokładnie tyle ile odpowiednich rekordów w Y się znalazło). Jak to usunąć? Oczywiście UNIKALNE!

Szczególnie 'cieszy', gdy dla każdego rekordu X znajduje się po kilka setek związanych rekordów Y, a potem heroicznie usuwa się duplikaty…

Jak naprawić? Na początek zrozumieć, że zadanie można zmodyfikować do 'wybrać takie rekordy X, dla których w Y jest CHOĆBY JEDEN związany z realizowanym warunkiem' — przecież z samego rekordu Y nic nam nie potrzeba.

Zagnieżdżone EXISTS

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źć pierwszą napotkaną rekord, starsze – nie. Dlatego zawsze preferuję podawać LIMIT 1 wciąż EXISTS.

LATERAL JOIN

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 umożliwia jednoczesny zwrot danych z znalezionej powiązanej rekordu Y. Podobna opcja została omówiona w artykule 'PostgreSQL Antipatterns: rzadka rekord dotrze do środka JOIN'.

'Dlaczego płacić więcej': DISTINCT [ON] + LIMIT 1

Dodatkową zaletą takich transformacji zapytań jest możliwość łatwego ograniczenia przeszukiwania rekordów, jeśli potrzebny jest tylko jeden / kilka z nich, jak w następującym przypadku:

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

Teraz czytamy zapytanie i próbujemy zrozumieć, co proponuje zrobić system zarządzania bazą danych:

• łączymy tabelki
• unikalizujemy według X.pk
• z pozostałych zapisów wybieramy jakiś jeden

Co więc otrzymaliśmy? „Jakąś jedną rekord” z unikalizowanych — a jeśli weźmiemy tę jedną z nieunikalizowanych, czy wynik jakoś się zmieni?.. „A jeśli nie ma różnicy, po co płacić więcej?”

SELECT
  *
FROM
  (
    SELECT
      *
    FROM
      X
    -- tutaj można wprowadzić odpowiednie warunki
    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.

„Mogłem tylko zapytać”: niejawny GROUP + LIMIT

Podobne sytuacje występują przy różnych sprawdzaniach niepustości tabeli lub CTE w trakcie wykonywania zapytania:

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

Funkcje agregacyjne (count/min/max/sum/...) są skutecznie wykonywane na całym zbiorze, nawet bez wyraźnego wskazania COUNT_BIG(*). Tylko z LIMIT nie zbyt się dogadują.

Programista może myśleć „jeżeli tam są rekordy, to nie potrzebuję więcej niż LIMIT”. Ale tak nie rób! Ponieważ dla bazy danych to:

• policz, co chcą oddaj tyle wierszy, ile proszą
• W zależności od warunków docelowych, tutaj można wprowadzić jedno z zamienników:

(count + LIMIT 1) = 0

• NOT EXISTS(LIMIT 1) na (count + LIMIT 1) > 0
• EXISTS(LIMIT 1) na count >= N
• (SELECT count(*) FROM (... LIMIT N)) na „Ile waży w gramach”: DISTINCT + LIMIT

SELECT DISTINCT pk FROM X LIMIT $1

Naivny programista może szczerze wierzyć, że wykonanie zapytania zatrzyma się,

gdy tylko znajdziemy $1 pierwszych napotkanych różnych wartości Kiedyś w przyszłości może tak to zadziała dzięki nowemu węzłowi.

Index Skip Scan , którego realizacja jest teraz opracowywana, ale na razie — nie.Na razie najpierw

zostaną wyciągnięte wszystkie rekordy , unikalizowane, i dopiero z nich zostanie wydane tyle, ile zapytano. Szczególnie przykra sytuacja, jeśli chcieliśmy coś w rodzaju, a rekordów w tabeli — setki tysięcy… $1 = 4Aby nie smucić się na próżno, skorzystaj z rekurencyjnego zapytania

„DISTINCT dla biednych” z PostgreSQL Wiki W SQL opisujesz „co” chcesz otrzymać, a nie „jak” to powinno być realizowane.:

Źródło: habr.com