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

No SQL, você descreve “o que” deseja obter, não “como” deve ser feito. Portanto, o problema de desenvolver consultas SQL no estilo “conforme você ouve enquanto escreve” ocupa o seu lugar de destaque, junto com as peculiaridades das condições de cálculo em SQL .



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



Agora, se você escreveu na solicitação "primeiro conecte essas placas e depois descarte todas as duplicatas, deve haver apenas uma 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 produz efeitos absolutamente inesperados do ponto de vista do desenvolvedor.





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



"Doce casal": PARTICIPAR + DISTINTO



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


Como ficaria claro que desejamos selecionar tais registros X, para os quais Y tem aqueles associados a uma condição cumprida . Escrevemos uma solicitação através de JOIN- obtivemos alguns valores pk várias vezes (exatamente quantos registros correspondentes em Y acabaram sendo). Como remover? Claro DISTINCT!



É especialmente "feliz" quando para cada registro X há várias centenas de registros Y vinculados e, em seguida, as duplicatas são heroicamente removidas ...







Como consertar? Para começar, perceba que a tarefa pode ser modificada para “selecionar tais registros X para os quais Y tem PELO MENOS UM associado a uma condição de execução” - afinal, não precisamos de nada do próprio registro Y.



EXISTS 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 é suficiente encontrar o primeiro registro disponível em EXISTS, os mais antigos não. Portanto, prefiro sempre especificar por LIMIT 1dentro 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;


A mesma opção permite, se necessário, ao mesmo tempo retornar imediatamente alguns dados do registro Y vinculado encontrado. Uma opção semelhante é discutida no artigo "Antipadrões do PostgreSQL: uma entrada rara irá voar para o meio de um JOIN" .


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



Uma vantagem adicional dessas transformações de consulta é a capacidade de restringir facilmente a iteração sobre os registros se apenas um / vários deles forem necessários, como no seguinte caso:



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 DBMS está se propondo a fazer:



  • nós conectamos as placas
  • Uniqueizable por X.pk
  • selecione um dos registros restantes


Ou seja, você conseguiu o quê? "Um registro" dos únicos - e se você pegar este dos não únicos, o resultado mudará de alguma forma? .. "E se não houver 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.



"Acabei de perguntar": GRUPO + LIMITE implícito



Coisas semelhantes são encontradas durante várias verificações para o não vazio do prato ou CTE durante a execução do pedido:



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


As funções de agregação ( count/min/max/sum/...) são executadas com sucesso em todo o conjunto, mesmo sem indicação explícita GROUP BY. Só que LIMITeles não são muito amigáveis com eles.



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



  • conte o que quiser em todos os registros
  • dê quantas linhas você pedir


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



  • (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 honestamente que a consulta será interrompida assim que encontrarmos $ 1 dos primeiros valores diferentes encontrados .



Em algum momento no futuro, isso pode funcionar e funcionará graças ao novo nó Index Skip Scan , cuja implementação está sendo elaborada, mas ainda não.



Até o momento, em um primeiro momento, todos os registros serão recuperados , exclusivos, e somente deles será retornado quantos solicitados. É especialmente triste se quisermos algo como $ 1 = 4 , e há centenas de milhares de registros na tabela ...



Para não ficar tristes em vão, usaremos a consulta recursiva "DISTINTO para os pobres" do PostgreSQL Wiki :






All Articles