Antipatrones de PostgreSQL: "¡Debe haber solo uno!"

En SQL, describe "qué" quiere obtener, no "cómo" debe hacerse. Por lo tanto, el problema de desarrollar consultas SQL al estilo "como se escucha es como se escribe" toma su lugar de honor, junto con peculiaridades de la evaluación de condiciones en SQL.

Hoy, usando ejemplos extremadamente simples, veamos a qué puede conducir esto en el contexto de usar GROUP/DISTINCT и LIMIT con ellos.

Eso es si escribiste en la solicitud. “Primero conecte estas tabletas y luego deseche todos los duplicados, debe haber solo uno instancia para cada clave" - así es exactamente como funcionará, incluso si la conexión no fuera necesaria en absoluto.

Y a veces tienes suerte y "simplemente funciona", a veces tiene un efecto desagradable en el rendimiento y a veces da efectos que son absolutamente inesperados desde el punto de vista del desarrollador.

Bueno, tal vez no tan espectacular, pero…

"Dulce pareja": ÚNETE + DISTINTO

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

¿Cómo sería claro lo que querían seleccione dichos registros X, para los cuales en Y están asociados con la condición cumplida. Envió una solicitud a través de JOIN - recibió algunos valores de pk varias veces (exactamente cuántos registros adecuados resultaron estar en Y). ¿Como remover? Ciertamente DISTINCT!

Es especialmente "agradable" cuando para cada registro X hay varios cientos de registros Y relacionados, y luego los duplicados se eliminan heroicamente ...

¿Como arreglar? Para empezar, tenga en cuenta que la tarea se puede modificar para "seleccione aquellos registros X para los cuales hay AL MENOS UNO en Y asociado con la condición que se está cumpliendo" - después de todo, no necesitamos nada del registro Y en sí.

EXISTE anidado

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

Algunas versiones de PostgreSQL entienden que en EXISTS basta con encontrar el primer registro que se encuentra, las más antiguas no. Por eso, prefiero indicar siempre 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;

La misma opción permite, si es necesario, devolver inmediatamente algunos datos del registro Y asociado encontrado al mismo tiempo. Una opción similar se discute en el artículo. "Antipatrones de PostgreSQL: el registro raro llegará a la mitad de JOIN".

"Por qué pagar más": DISTINCT [ON] + LIMIT 1

Una ventaja adicional de tales transformaciones de consulta es la capacidad de limitar fácilmente la enumeración de registros si solo se necesita uno o algunos de ellos, como en el siguiente caso:

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

Ahora leemos la solicitud y tratamos de entender qué se supone que debe hacer el DBMS:

• conectamos las placas
• único por X.pk
• elige uno de los registros restantes

Entonces, ¿qué obtuviste? "Algún registro" de los únicos, y si tomas este de los no únicos, ¿cambiará el resultado de alguna manera? .. "Y si no hay diferencia, ¿por qué pagar más?"

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

Y exactamente el mismo tema con GROUP BY + LIMIT 1.

"Solo tengo que preguntar": GRUPO implícito + LÍMITE

Cosas similares ocurren en diferentes cheques no vacíos etiquetas o CTE a medida que avanza la solicitud:

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

Funciones agregadas (count/min/max/sum/...) se ejecutan con éxito en todo el conjunto, incluso sin especificar explícitamente GROUP BY. solo aqui con LIMIT no son muy amigables.

El desarrollador puede pensar “Ahora, si hay registros allí, entonces no necesito más que LIMIT”. ¡Pero no tienes que hacerlo! Porque para la base es:

• cuentan lo que quieren en todos los registros
• da tantas lineas como te pidan

Dependiendo de las condiciones del objetivo, es apropiado hacer una de las siguientes sustituciones:

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

"Cuánto colgar en gramos": DISTINTO + LÍMITE

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Un desarrollador ingenuo puede creer sinceramente que la ejecución de una solicitud se detendrá, tan pronto como encontremos los primeros $1 valores diferentes que se encuentran.

En algún momento en el futuro, esto puede funcionar y funcionará gracias a un nuevo nodo Índice Saltar Escaneo, cuya implementación se está resolviendo actualmente, pero aún no.

hasta ahora primero todos los registros serán recuperados, son únicos y solo se devolverán tantos como se soliciten. Es especialmente triste si quisiéramos algo como $ 1 = 4, y hay cientos de miles de registros en la tabla...

Para no entristecernos en vano utilizaremos una consulta recursiva "DISTINTO para los pobres" de PostgreSQL Wiki:

Fuente: habr.com