Antipatróns de PostgreSQL: "Só debe haber un!"

En SQL, describe "o que" quere lograr, non "como" debe executarse. Polo tanto, o problema de desenvolver consultas SQL ao estilo de "tal como se escoita é como se escribe" ocupa o seu lugar de honra, xunto con características de cálculo de condicións en SQL.

Hoxe, usando exemplos moi sinxelos, vexamos a que pode levar isto no contexto do uso GROUP/DISTINCT и LIMIT con eles.

Agora, se escribiu na solicitude "primeiro conecta estes sinais e despois tira todos os duplicados, só debería quedar un copia para cada chave" - así funcionará, aínda que non fose necesaria a conexión.

E ás veces tes sorte e "simplemente funciona", ás veces ten un efecto desagradable no rendemento e ás veces dá efectos completamente inesperados desde o punto de vista do programador.

Pois quizais non sexa tan espectacular, pero...

“Doce parella”: ÚNETE + DISTINTO

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

Estaría claro o que querían seleccione rexistros X para os que existan rexistros en Y relacionados coa condición cumprida. Escribiu unha solicitude a través de JOIN — obtivo algúns valores pk varias veces (exactamente cantas entradas adecuadas apareceron en Y). Como eliminar? Certamente DISTINCT!

É especialmente "gratificante" cando para cada rexistro X hai varios centos de rexistros Y relacionados, e despois os duplicados son eliminados heroicamente...

Como arranxar? Para comezar, dáse conta de que o problema pódese modificar a "seleccione os rexistros X para os que en Y hai polo menos un asociado coa condición cumprida" - Despois de todo, non necesitamos nada do propio rexistro Y.

Anidado EXISTE

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

Algunhas versións de PostgreSQL entenden que en EXISTES abonda con atopar a primeira entrada que aparece, as máis antigas non. Por iso prefiro indicar sempre LIMIT 1 dentro EXISTS.

UNIÓN LATERAL

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;

A mesma opción permite, se é necesario, devolver inmediatamente algúns datos do rexistro Y asociado atopado. Unha opción semellante é discutida no artigo "Antipatróns PostgreSQL: un rexistro raro chegará á metade dun JOIN".

"Por que pagar máis": DISTINTO [ACTIVADO] + LÍMITE 1

Unha vantaxe adicional destas transformacións de consulta é a posibilidade de limitar facilmente a busca de rexistros se só se precisa un ou algúns deles, como no seguinte caso:

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

Agora lemos a solicitude e intentamos comprender o que se propón facer o DBMS:

• conectando os sinais
• único por X.pk
• entre as entradas restantes, seleccione unha

Entón, que conseguiches? "Só unha entrada" dos únicos -e se tomamos este dos non únicos, cambiará o resultado dalgún xeito?... "E se non hai diferenza, para que pagar máis?"

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

E exactamente o mesmo tema con GROUP BY + LIMIT 1.

“Só teño que preguntar”: implícito GRUPO + LÍMITE

Cousas semellantes ocorren en diferentes comprobacións de non baleiro sinais ou CTE a medida que avanza a solicitude:

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

funcións agregadas (count/min/max/sum/...) execútanse con éxito en todo o conxunto, mesmo sen instrucións explícitas GROUP BY. Só con LIMIT non son moi amigas.

O desenvolvedor pode pensar "Se hai rexistros alí, non necesito máis que LIMIT". Pero non o fagas! Porque para a base é:

• conta o que queren segundo todos os rexistros
• dá tantas liñas como pidan

Segundo as condicións de destino, é conveniente realizar unha das seguintes substitucións:

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

“Canto pesar en gramos”: DISTINTO + LÍMITE

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Un programador inxenuo pode crer sinceramente que a solicitude deixará de executarse. en canto atopemos $1 dos primeiros valores diferentes que se atopan.

Nalgunha ocasión no futuro isto pode funcionar e funcionará grazas a un novo nodo Escaneo de salto de índice, cuxa implantación se está a traballar actualmente, pero aínda non.

Polo de agora primeiro todos os rexistros serán recuperados, son únicos, e só deles se devolverá a cantidade solicitada. É especialmente triste se queriamos algo así $ 1 = 4, e hai centos de miles de rexistros na táboa...

Para non estar tristes en balde, usemos unha consulta recursiva "DISTINCT é para os pobres" de PostgreSQL Wiki:

Fonte: www.habr.com