Início / dba / Perguntas / 343487
Accepted
Björn Morén
Asked: 2024-11-08 14:43:57 +0800 CST

CTEs não utilizados nunca são materializados no PostgreSQL 16?

  • 772

Quero retornar minhas linhas de postagem em uma das três ordens de classificação, controladas por um sorting_paramparâmetro de função. A classificação é complexa, não se presta a um simples ORDER BY CASE .... Então, estou pensando em escrever cada classificação em seu próprio CTE e, em seguida, escolher um dos CTEs como a saída final. No entanto, não gostaria que o PostgreSQL perdesse tempo com todos os três, apenas com o que selecionei. Então, todos os três serão materializados ou apenas o que escolhi? Ou há uma maneira melhor de fazer isso? PostgreSQL versão 16. Código simplificado abaixo.

WITH posts_by_order1 AS
(
    SELECT p.id
    FROM post p
    ORDER BY [some complex ordering 1]
    LIMIT 10
),
posts_by_order2 AS
(
    SELECT p.id
    FROM post p
    ORDER BY [some complex ordering 2]
    LIMIT 10
),
posts_by_order3 AS
(
    SELECT p.id
    FROM post p
    ORDER BY [some complex ordering 3]
    LIMIT 10
)
SELECT * FROM posts_by_order1 WHERE sorting_param = 0
UNION ALL
SELECT * FROM posts_by_order2 WHERE sorting_param = 1
UNION ALL
SELECT * FROM posts_by_order3 WHERE sorting_param = 2;

Infelizmente não posso fazer isso:

    ORDER BY 
        CASE 
            WHEN sorting_param = 0 THEN p.date 
            WHEN sorting_param = 1 THEN p.name 
            ELSE NULL 
        END DESC, ...
postgresql

1 respostas

  1. Best Answer

    O manual:

    As instruções de modificação de dados em WITHsão executadas exatamente uma vez, e sempre até a conclusão, independentemente de a consulta primária ler toda (ou mesmo alguma) de sua saída. Observe que isso é diferente da regra para SELECTem WITH: conforme declarado na seção anterior, a execução de a SELECTé realizada somente até onde a consulta primária demanda sua saída .

    Ênfase em negrito minha.

    A palavra-chave opcional MATERIALIZEDnão tem impacto nisso. O conjunto de resultados de um CTE só pode ser "materializado" depois de ter sido executado, mas nunca chegamos lá...

    Então sua construção basicamente funciona . Teste sua consulta com EXPLAIN ANALYZEe você verá (never executed)ao lado os planos para CTEs que não foram realmente chamados. Os CTEs ainda precisam ser planejados , o que incorre em uma pequena sobrecarga de qualquer maneira.

    Além disso, a ordem de classificação em subconsultas ou CTEs é transferida para o externo, SELECTdesde que nada aconteça lá que possa disparar uma classificação diferente: UNION(sem ALL), JOIN, cláusula adicional WHERE, ... Mas este é um detalhe de implementação e não há garantias. Isso aborda a ordem dentro do conjunto de resultados de uma subconsulta. Considerações semelhantes se aplicam à ordem entre UNION ALLas pernas. Relacionado:

    Dependendo de como você pretende usar essa construção, pode haver opções melhores, incluindo:

    • Basta alternar para a declaração correta no seu código de cliente.

    • Use uma função PL/pgSQL e alterne com CASEdentro. Exemplo:

    CREATE OR REPLACE FUNCTION my_func(_sorting_param int = 0)
  RETURNS TABLE (id int)  -- matching column definition list
  LANGUAGE plpgsql STRICT AS
$func$
BEGIN
   CASE _sorting_param
   WHEN 1 THEN  -- posts_by_order1
      RETURN QUERY
      SELECT p.id
      FROM   post p
      ORDER  BY [some complex ordering 1]
      LIMIT  10
   WHEN 2 THEN  -- posts_by_order2
      ...
   ELSE  -- ?
      ...
   END CASE;
END
$func$;
    • Use SQL dinâmico em uma função PL/pgSQL. Exemplo:
    CREATE OR REPLACE FUNCTION my_func(_sorting_param int = 0)
  RETURNS TABLE (id int)  -- matching column definition list!
  LANGUAGE plpgsql STRICT AS
$func$
BEGIN
   RETURN QUERY EXECUTE format(
   $q$
   SELECT p.id
   FROM   post p
   ORDER  BY %s
   LIMIT  10
   $q$
 , CASE _sorting_param
   WHEN 1 THEN  ' ... '  -- posts_by_order1
   WHEN 2 THEN  ' ... '  -- posts_by_order2
   ELSE true
   END
   );
END
$func$;

    Ligue para:

    SELECT * FROM my_func(0);

    Tome cuidado para não introduzir SQL-injeção com SQL dinâmico. Veja:

    • 3

relate perguntas