PostgreSQL antiobrasci: "Mora postojati samo jedan!"

U SQL-u opisujete "šta" želite da postignete, a ne "kako" to treba da se izvrši. Stoga problem razvoja SQL upita u stilu „kako se čuje, tako se i piše“ zauzima svoje počasno mjesto, uz karakteristike izračunavanja uslova u SQL-u.

Danas, koristeći krajnje jednostavne primjere, da vidimo do čega to može dovesti u kontekstu upotrebe GROUP/DISTINCT и LIMIT sa njima.

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

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

Pa, možda i nije tako spektakularno, ali...

“Slatki par”: PRIDRUŽI SE + DISTINCT

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

Bilo bi jasno šta žele izaberite zapise X za koje postoje zapisi u Y koji se odnose na ispunjeni uslov. Napisao zahtjev putem JOIN — dobio neke pk vrijednosti nekoliko puta (koliko se tačno odgovarajućih unosa pojavilo u Y). Kako ukloniti? Svakako DISTINCT!

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

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

Ugniježđeno EXISTS

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 naznačavam LIMIT 1 unutar 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;

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

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

Dodatna prednost ovakvih transformacija upita je mogućnost lakog 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 šta se DBMS-u predlaže da uradi:

• povezivanje znakova
• jedinstveno od strane X.pk
• od preostalih unosa izaberite jedan

Pa šta si dobio? "Samo jedan unos" od unikatnih - a ako uzmemo ovaj od nejedinstvenih, hoće li se rezultat nekako promijeniti?.. “A ako nema razlike, zašto plaćati 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.

“Moram samo da pitam”: implicitna GROUP + LIMIT

Slične stvari se dešavaju u različitim situacijama provjere nepraznine znakove ili CTE kako zahtjev napreduje:

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

Agregatne funkcije (count/min/max/sum/...) se uspješno izvršavaju na cijelom skupu, čak i bez eksplicitnih instrukcija GROUP BY. Samo sa LIMIT nisu baš druželjubivi.

Programer može razmišljati “ako tamo ima zapisa, onda mi ne treba više od LIMIT-a”. Ali nemojte to raditi! Jer za bazu je:

• računaju šta žele prema svim zapisima
• dajte onoliko redova koliko traže

U zavisnosti od ciljanih uslova, 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 visiti u gramima”: DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Naivni programer može iskreno vjerovati da će zahtjev prestati da se izvršava. čim pronađemo $1 od prvih različitih vrijednosti koje naiđu.

Negdje u budućnosti ovo može i funkcionirat će zahvaljujući novom čvoru Index Skip Scan, čija se implementacija trenutno radi, ali još nije.

Za sada prvo svi zapisi će biti vraćeni, su jedinstveni i samo od njih će biti vraćen traženi iznos. Posebno je tužno ako želimo nešto slično $ 1 = 4, a u tabeli postoje stotine hiljada zapisa...

Da ne bismo uzalud bili tužni, upotrijebimo rekurzivni upit "DISTINCT je za siromašne" sa PostgreSQL Wiki:

izvor: www.habr.com