PostgreSQL Antipatterns: "Mora postojati samo jedan!"

U SQL-u opisujete "što" želite postići, a ne "kako" to treba izvesti. Stoga problem izrade SQL upita u stilu “kako se čuje tako se piše” zauzima svoje počasno mjesto, uz značajke izračunavanja uvjeta u SQL-u.

Danas, koristeći krajnje jednostavne primjere, pogledajmo do čega to može dovesti u kontekstu korištenja GROUP/DISTINCT и LIMIT sa njima.

E sad, ako ste napisali u zahtjevu "Prvo spojite ove znakove, a zatim izbacite sve duplikate, trebao bi ostati samo jedan kopija za svaki ključ" - upravo tako će funkcionirati, čak i ako veza uopće nije potrebna.

A ponekad imate sreće i "jednostavno radi", ponekad ima neugodan učinak na performanse, a ponekad daje efekte koji su potpuno neočekivani sa stajališta programera.

Dobro, možda ne tako spektakularno, ali...

“Slatki par”: JOIN + DISTINCT

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

Bilo bi jasno što su htjeli odaberite zapise X za koje postoje zapisi u Y koji se odnose na ispunjeni uvjet. Napisao zahtjev putem JOIN — dobili neke pk vrijednosti nekoliko puta (točno koliko se odgovarajućih unosa pojavilo u Y). Kako ukloniti? Sigurno DISTINCT!

Posebno je “zadovoljstvo” kada za svaki X-zapis postoji nekoliko stotina povezanih Y-zapisa, a onda se duplikati herojski uklanjaju...

Kako popraviti? Za početak shvatite da se problem može modificirati na “odaberi zapise X za koje u Y postoji NAJMANJE JEDAN povezan s ispunjenim uvjetom” - uostalom, ne treba nam ništa od samog Y-zapisa.

Ugniježđeni POSTOJI

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

Neke verzije PostgreSQL-a razumiju da je u EXISTS dovoljno pronaći prvi unos koji se pojavi, starije ne. Stoga radije uvijek ukazujem LIMIT 1 u EXISTS.

BOČNI SPOJ

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;

Ista opcija omogućuje, ako je potrebno, da se odmah vrate neki podaci iz pronađenog pridruženog Y-zapisa. Slična se opcija raspravlja u članku "PostgreSQL Antipatterns: rijedak zapis će doći do sredine JOIN-a".

“Zašto platiti više”: DISTINCT [ON] + LIMIT 1

Dodatna prednost takvih transformacija upita je mogućnost jednostavnog ograničavanja pretraživanja zapisa ako je potreban samo jedan ili nekoliko njih, kao u sljedećem slučaju:

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

Sada čitamo zahtjev i pokušavamo razumjeti što je DBMS predložen da radi:

• povezivanje znakova
• jedinstven po X.pk
• od preostalih unosa odaberite jedan

Dakle, što ste dobili? "Samo jedan unos" od jedinstvenih - a ako uzmemo ovaj od nejedinstvenih, hoće li se rezultat nekako promijeniti?.. “A ako nema razlike, zašto platiti više?”

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

I potpuno ista tema sa GROUP BY + LIMIT 1.

“Samo moram pitati”: implicitna GRUPA + OGRANIČENJE

Slične stvari se događaju na različitim provjere nepraznine znakovi ili CTE-ovi kako zahtjev napreduje:

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

Skupne funkcije (count/min/max/sum/...) uspješno se izvode na cijelom skupu, čak i bez eksplicitnih uputa GROUP BY. Samo sa LIMIT nisu baš prijateljski raspoloženi.

Programer može misliti "ako tamo postoje zapisi, onda mi ne treba više od LIMIT". Ali nemojte to činiti! Jer za bazu je:

• računaju što žele prema svim zapisima
• dati onoliko redaka koliko traže

Ovisno o ciljanim uvjetima, prikladno je napraviti jednu od sljedećih zamjena:

• (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))

“Koliko izvagati u gramima”: DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Naivni programer može iskreno vjerovati da će se zahtjev prestati izvršavati. čim pronađemo $1 od prve različite vrijednosti na koju naiđemo.

Nekad u budućnosti ovo bi moglo i hoće funkcionirati zahvaljujući novom čvoru Skeniranje preskakanja indeksa, čija se implementacija trenutno razrađuje, ali još ne.

Za sada prvo svi će zapisi biti dohvaćeni, jedinstveni su i samo od njih će biti vraćen traženi iznos. Posebno je tužno ako smo željeli tako nešto $ 1 = 4, a u tablici ima stotine tisuća zapisa...

Da ne budemo uzalud tužni, poslužimo se rekurzivnim upitom "DISTINCT je za siromašne" s PostgreSQL Wikija:

Izvor: www.habr.com