PostgreSQL Antipatterns: "Tsy maintsy misy iray ihany!"

Ao amin'ny SQL, mamaritra ny "inona" tianao hotratrarina ianao, fa tsy ny "ahoana" tokony hotanterahina. Noho izany, ny olana amin'ny famolavolana fanontaniana SQL amin'ny fomba "araka ny fandrenesana dia ny fomba nanoratana azy" dia maka ny toeran'ny voninahitra, miaraka amin'ny endri-javatra amin'ny kajy fepetra ao amin'ny SQL.

Amin'izao fotoana izao, amin'ny fampiasana ohatra tena tsotra, andeha hojerentsika hoe inona no mety hitarika izany amin'ny sehatry ny fampiasana GROUP/DISTINCT и LIMIT miaraka amin'izy ireo.

Ankehitriny, raha nanoratra tao amin'ny fangatahana ianao “Ampifandraiso aloha ireo famantarana ireo, ary ario ny dika mitovy rehetra, tokony ho iray ihany kopia isaky ny fanalahidy" - izany no fomba fiasa, na dia tsy nilaina mihitsy aza ny fifandraisana.

Ary indraindray dia tsara vintana ianao ary "miasa fotsiny", indraindray misy fiantraikany tsy mahafinaritra amin'ny fampisehoana, ary indraindray dia manome vokatra tsy ampoizina tanteraka amin'ny fomba fijerin'ny mpamorona.

Eny, mety tsy dia mahavariana, fa ...

“Mifankatiava mamy”: MIARAKA + MIDITRA

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

Ho hita mazava izay tadiavin’izy ireo safidio ny rakitra X izay misy firaketana ao amin'ny Y mifandraika amin'ny fepetra tanteraka. Nanoratra fangatahana tamin'ny JOIN - nahazo sanda pk imbetsaka (firy marina ny fidirana mety niseho tao amin'ny Y). Ahoana ny fanesorana? Azo antoka DISTINCT!

"Mahafinaritra" indrindra raha misy rakitsoratra Y mifandraika amin'ny X tsirairay, ary avy eo dia nesorina tamim-pahasahiana ireo dika mitovy ...

Ahoana ny fanamboarana? Voalohany, fantaro fa ny olana dia azo ovaina ho "Misafidiana rakitsoratra X izay ao amin'ny Y dia misy FAHAFATESANA IRAY mifandray amin'ny fepetra tanteraka" - na izany aza, tsy mila na inona na inona avy amin'ny Y-record mihitsy isika.

Nested EXISTS

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

Ny dikan-teny sasany amin'ny PostgreSQL dia mahatakatra fa ao amin'ny EXISTS dia ampy ny mahita ny fidirana voalohany mipoitra, ny zokiny dia tsy. Noho izany dia aleoko manondro foana LIMIT 1 ao anatin'ny 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;

Ny safidy mitovy dia mamela, raha ilaina, ny mamerina avy hatrany ny angona sasany avy amin'ny rakitsoratra Y mifandray. Safidy mitovy amin'izany no resahina ao amin'ny lahatsoratra "PostgreSQL Antipatterns: misy rakitsoratra tsy fahita firy ho tonga eo afovoan'ny JOIN".

“Nahoana no mandoa bebe kokoa”: DISTINCT [ON] + LIMIT 1

Ny tombony fanampiny amin'ny fanovana fanontaniana toy izany dia ny fahafahana mametra mora foana ny fikarohana ireo rakitra raha toa ka iray na vitsivitsy amin'izy ireo ihany no ilaina, toy ny amin'ity tranga manaraka ity:

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

Ankehitriny dia mamaky ny fangatahana izahay ary miezaka ny hahatakatra izay atolotra ny DBMS:

• mampifandray ireo famantarana
• tokana nataon'i X.pk
• avy amin'ireo lahatsoratra sisa, mifidiana iray

Inona àry no azonao? "Fidirana iray ihany" avy amin'ireo tsy manam-paharoa - ary raha raisina ity iray amin'ireo tsy miavaka ity dia hiova ve ny vokatra?.. "Ary raha tsy misy fahasamihafana, nahoana no mandoa bebe kokoa?"

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

Ary tena mitovy lohahevitra amin'ny GROUP BY + LIMIT 1.

“Tsy maintsy manontany fotsiny aho”: GROUP + LIMIT implicit

Miseho amin'ny sehatra samihafa ny zavatra mitovy fisavana tsy fahabangana famantarana na CTE rehefa mandroso ny fangatahana:

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

Asa mitambatra (count/min/max/sum/...) dia vita soa aman-tsara amin'ny andiany iray manontolo, na dia tsy misy toromarika mazava aza GROUP BY. miaraka amin'ny LIMIT tsy dia sariaka loatra izy ireo.

Afaka mieritreritra ny mpamorona "raha misy firaketana ao dia tsy mila mihoatra ny LIMIT aho". Fa aza manao izany! Satria ny fototra dia:

• isao izay tadiaviny araka ny firaketana rehetra
• omeo andalana maro araka izay angatahiny

Miankina amin'ny fepetra kendrena, dia mety ny manao ny iray amin'ireto fanoloana manaraka ireto:

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

"Firy ny lanja amin'ny grama": DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Ny mpamorona tsy misy dikany dia mety mino amin-kitsimpo fa hijanona ny fampiharana. raha vao mahita $1 amin'ireo soatoavina samihafa voalohany hita.

Amin'ny hoavy dia mety ary hiasa izany noho ny node vaovao Index Skip Scan, izay efa eo am-pamolavolana ny fampiharana azy amin’izao fotoana izao fa tsy mbola.

Amin'izao aloha horaisina ny rakitra rehetra, dia miavaka, ary avy amin'izy ireo ihany no hamerenana ny vola nangatahana. Mampalahelo indrindra raha mila zavatra toy izany isika $ 1 = 4, ary misy firaketana an'hetsiny ao anaty latabatra...

Mba tsy halahelo foana, andao hampiasa fanontaniana miverimberina "DISTINCT dia ho an'ny mahantra" avy amin'ny Wiki PostgreSQL:

Source: www.habr.com