🤷🏻 🔈 🧜🏿 Antipatrones PostgreSQL: "¡Debería quedar sólo uno!" ☣️ 👩🏻‍🎤 👩‍💼

En SQL, usted describe "qué" desea obtener, no "cómo" debe ejecutarse. Por lo tanto, el problema de desarrollar consultas SQL en el estilo "como lo escucha mientras escribe" toma su lugar de honor, junto con las peculiaridades de calcular condiciones en SQL .

Hoy, utilizando ejemplos extremadamente simples, veamos a qué puede conducir esto en el contexto de uso GROUP/DISTINCTy LIMITjunto con ellos.

Ahora, si escribió en la solicitud "primero conecte estas placas y luego descarte todos los duplicados, debe haber solo una copia para cada clave" , así es como funcionará, incluso si la conexión no fue necesaria en absoluto.

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

Bueno, quizás no tan espectacular, pero ...

"Dulce pareja": ÚNETE + DISTINCT

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

¿Cómo quedaría claro que quisiéramos seleccionar tales registros X, para los cuales Y tiene los asociados con una condición cumplida ? Escribimos una solicitud JOIN: obtuvimos algunos valores pk varias veces (exactamente cuántos registros coincidentes en Y resultaron ser). ¿Como remover? ¡Por supuesto DISTINCT!

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

¿Cómo solucionarlo? Para empezar, tenga en cuenta que la tarea se puede modificar para "seleccionar los registros X para los que Y tiene AL MENOS UNO asociado con una condición de ejecución" ; 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 es suficiente encontrar el primer registro disponible en EXISTS, las más antiguas no. Por eso, prefiero especificar siempre el LIMIT 1interior 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, al mismo tiempo devolver inmediatamente algunos datos del registro Y vinculado encontrado. Una opción similar se analiza en el artículo "Antipatrones de PostgreSQL: una entrada rara volará a la mitad de un JOIN" .

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

Una ventaja adicional de tales transformaciones de consultas es la capacidad de restringir fácilmente la iteración sobre los registros si solo se necesitan uno o varios 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 e intentamos entender qué propone hacer el DBMS:

conectamos las placas
Uniqueizable por X.pk
seleccione uno de los registros restantes

Es decir, ¿tienes qué? "Algún registro" de los únicos, y si tomamos este de los no exclusivos, ¿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 pregunto": GRUPO implícito + LÍMITE

Se encuentran cosas similares durante varias verificaciones de no vacío de la placa o CTE durante la ejecución de la consulta:

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

Las funciones agregadas ( count/min/max/sum/...) se ejecutan correctamente en todo el conjunto, incluso sin una indicación explícita GROUP BY. Solo LIMITque no son muy amigables con ellos.

El desarrollador puede pensar "si hay registros allí, entonces no necesito más LIMIT" . ¡Pero no lo hagas! Porque para la base es:

cuente lo que quiera en todos los registros
da tantas líneas como pidas

Dependiendo de las condiciones de destino, es apropiado hacer una de las sustituciones aquí:

(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": DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Un desarrollador ingenuo puede creer honestamente que la consulta se detendrá tan pronto como encontremos los primeros valores diferentes de $ 1 que se encuentren .

En algún momento en el futuro, esto puede funcionar y funcionará gracias al nuevo nodo Index Skip Scan , cuya implementación se está trabajando actualmente, pero aún no.

Hasta ahora, al principio, todos los registros se recuperarán , se unificarán y solo de ellos se devolverán cuántos solicitados. Es especialmente triste si quisiéramos algo como $ 1 = 4 , y hay cientos de miles de registros en la tabla ...

Para no estar tristes en vano, usaremos la consulta recursiva "DISTINCT for the poor" de PostgreSQL Wiki :

Antipatrones PostgreSQL: "¡Debería quedar sólo uno!"