Antipadrões do PostgreSQL: "Deve haver apenas um!"

Em SQL, você descreve “o que” deseja alcançar, não “como” deve ser executado. Portanto, o problema de desenvolver consultas SQL no estilo “como se ouve é como se escreve” ocupa o seu lugar de honra, junto com recursos de cálculo de condições em SQL.

Hoje, usando exemplos extremamente simples, vamos ver o que isso pode levar no contexto de uso GROUP/DISTINCT и LIMIT com eles.

Agora, se você escreveu no pedido “primeiro conecte esses sinais e depois jogue fora todas as duplicatas, só deveria sobrar um cópia para cada chave" - é exatamente assim que funcionará, mesmo que a conexão não seja necessária.

E às vezes você tem sorte e “simplesmente funciona”, às vezes tem um efeito desagradável no desempenho e às vezes dá efeitos completamente inesperados do ponto de vista do desenvolvedor.

Bem, talvez não tão espetacular, mas...

“Doce casal”: JOIN + DISTINCT

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

Ficaria claro o que eles queriam selecione os registros X para os quais existem registros em Y que estão relacionados à condição atendida. Escreveu uma solicitação via JOIN — obteve alguns valores de pk várias vezes (exatamente quantas entradas adequadas apareceram em Y). Como remover? Certamente DISTINCT!

É especialmente “gratificante” quando para cada registro X existem várias centenas de registros Y relacionados, e então as duplicatas são heroicamente removidas...

Como consertar? Para começar, perceba que o problema pode ser modificado para “selecione os registros X para os quais em Y exista PELO MENOS UM associado à condição atendida” - afinal, não precisamos de nada do próprio registro Y.

EXISTENTES aninhados

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

Algumas versões do PostgreSQL entendem que no EXISTS basta encontrar a primeira entrada que aparece, as mais antigas não. Por isso prefiro indicar sempre LIMIT 1 dentro EXISTS.

JUNÇÃO 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 opção permite, se necessário, retornar imediatamente alguns dados do registro Y associado encontrado. Uma opção semelhante é discutida no artigo "Antipadrões PostgreSQL: um registro raro atingirá o meio de um JOIN".

“Por que pagar mais”: DISTINCT [ON] + LIMIT 1

Um benefício adicional de tais transformações de consulta é a capacidade de limitar facilmente a pesquisa de registros se apenas um ou alguns deles forem necessários, como no caso a seguir:

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

Agora lemos a solicitação e tentamos entender o que o SGBD se propõe a fazer:

• conectando os sinais
• exclusivo por X.pk
• das entradas restantes, selecione uma

Então, o que você conseguiu? "Apenas uma entrada" dos únicos - e se pegarmos este dos não únicos, o resultado mudará de alguma forma?.. “E se não há diferença, por que pagar mais?”

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

E exatamente o mesmo tópico com GROUP BY + LIMIT 1.

“Só preciso perguntar”: GRUPO + LIMITE implícito

Coisas semelhantes ocorrem em diferentes verificações de não-vazio sinais ou CTEs à medida que a solicitação avança:

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

Funções agregadas (count/min/max/sum/...) são executados com sucesso em todo o conjunto, mesmo sem instruções explícitas GROUP BY. Somente com LIMIT eles não são muito amigáveis.

O desenvolvedor pode pensar “se houver registros lá, então não preciso de mais do que LIMIT”. Mas não faça isso! Porque para a base é:

• conte o que eles querem de acordo com todos os registros
• dê quantas linhas eles pedirem

Dependendo das condições alvo, é apropriado fazer uma das seguintes substituições:

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

“Quanto pendurar em gramas”: DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

Um desenvolvedor ingênuo pode acreditar sinceramente que a solicitação deixará de ser executada. assim que encontrarmos $1 dos primeiros valores diferentes que encontrarmos.

Em algum momento no futuro isso pode e funcionará graças a um novo nó Índice Ignorar digitalização, cuja implementação está atualmente em fase de elaboração, mas ainda não.

Por enquanto primeiro todos os registros serão recuperados, são únicos, e somente a partir deles será devolvido o valor solicitado. É especialmente triste se quiséssemos algo como $ 1 = 4, e há centenas de milhares de registros na tabela...

Para não ficarmos tristes em vão, vamos usar uma consulta recursiva "DISTINCT é para os pobres" do PostgreSQL Wiki:

Fonte: habr.com