Início / dba / Perguntas / 316818
Accepted
Erwin Brandstetter
Asked: 2022-09-13 15:50:54 +0800 CST

Os resultados das cláusulas UNION ALL são sempre anexados em ordem?

  • 772

De acordo com o SQL padrão UNION/ UNION ALLnão garante nenhuma ordem de classificação específica sem uma ORDER BYcláusula externa - como quase não há lugar no SQL onde a ordem de classificação é garantida sem ORDER BY.

No entanto, o Postgres usa uma etapa "Append" para casos simples de UNION ALL, portanto, os resultados da primeira perna (mesmo que não classificados entre suas partições) sempre vêm antes da próxima perna, etc. O Postgres simplesmente anexa os resultados de cada perna na ordem dada. Isso é particularmente relevante com uma LIMITcláusula:

SELECT 1 FROM tbl  -- or any complex query
UNION ALL
SELECT 2
LIMIT  1

Obviamente, isso não vale para UNION(sem ALL). Mas fora isso, nunca vi o Postgres retornar fora de ordem, ou seja, '2' da consulta acima, enquanto o primeiro SELECTtambém retornaria linha(s). Nem mesmo se a primeira etapa for extremamente cara.

Eu baseei consultas sobre esse comportamento no passado. Agora , encontrei uma alegação de que o Postgres pode retornar linhas fora de ordem aqui, mas sem comprovação real.

O atual manual do Postgres tem a dizer sobre o assunto:

UNIONefetivamente acrescenta o resultado de query2ao resultado de query1(embora não haja garantia de que esta seja a ordem em que as linhas são realmente retornadas). Além disso, elimina linhas duplicadas de seu resultado, da mesma forma que DISTINCT, a menos que UNION ALLseja usado.

Isso é bastante obscuro. A ordem citada se aplica à lista de SELECTcláusulas, ou linhas dentro de cada cláusula, ou apenas ao conjunto retornado? Além disso, UNION ALLé mencionado apenas na segunda frase, então não está claro se a primeira frase tão importante deve se aplicar a UNION ALL...

Alguém pode mostrar um exemplo, onde as linhas são retornadas fora de ordem, quebrando a sequência de UNION ALLcláusulas? Em qualquer versão do Postgres. (Mesmo que a versão mais recente seja a mais interessante.)

Se não for isso, há motivos para acreditar que isso pode mudar em uma versão futura?

ORDER BYnão é a questão imediata aqui. A questão é se várias UNION ALLcláusulas retornam linhas na sequência determinada (antes LIMITpodem ser acionadas e impedir que outras ramificações sejam executadas).

postgresql order-by

2 respostas

  1. Best Answer

    Houve uma pergunta semelhante recentemente na lista de discussão pgsql-docs,
    Esclareça as garantias de ordenação na combinação de consultas (ou falta delas) :

    Eu estava tentando entender quais - se houver - são as garantias em relação ao pedido para combinar consultas (UNION/UNION ALL/...). A partir desta mensagem 1 , parece que UNION ALL preserva a ordem das consultas de operandos, enquanto UNION não (presumivelmente nem INTERSECT, INTERSECT ALL, EXCEPT e EXCEPT ALL).

    A documentação[2] não menciona isso, eu sugiro adicionar uma nota esclarecendo isso.

    Tom Lane (e outros) respondeu:

    Como a documentação não garante, não há nenhuma. Se você deseja saída ordenada, use ORDER BY.

    Não, não há garantia. É que UNION ALL funciona assim hoje (preservando a ordem das subseleções) - e nem tenho certeza disso, pode não preservar a ordem em todos os casos, com índices ou particionamentos diferentes ou um plano paralelo, etc. De qualquer forma, não há garantia de que o comportamento não mudará no futuro devido a melhorias do planejador.

    Sim, isso. Você pode obter um plano paralelizado hoje para UNION ALL:

    =# explain analyze select * from foo union all select * from foo;
                                                                 QUERY PLAN                                                            

--------------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=0.00..208552.05 rows=5120008 width=244) (actual time=0.652..390.135 rows=5120000 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Append  (cost=0.00..208552.05 rows=2133336 width=244) (actual time=0.021..228.848 rows=1706667 loops=3)
         ->  Parallel Seq Scan on foo  (cost=0.00..98942.68 rows=1066668 width=244) (actual time=0.453..78.084 rows=853333 loops=3)
         ->  Parallel Seq Scan on foo foo_1  (cost=0.00..98942.68 rows=1066668 width=244) (actual time=0.024..125.299 rows=1280000 loops=2)
 Planning Time: 0.094 ms
 Execution Time: 488.352 ms

    É verdade que em casos simples não paralelizados faremos a primeira consulta e depois a segunda, mas o SQL não promete que isso seja verdade e o Postgres também não.

    • 8

  2. Foi estabelecido, não há garantia de que as linhas do primeiro UNION ALLtermo sejam retornadas antes das linhas do próximo UNION ALLtermo etc. Nem no SQL padrão, nem no Postgres.

    A solução limpa é não confiar na ordem das cláusulas.

    Alternativas

    Para consultas simples como mostrado acima (sem exterior ORDER BYou JOIN) a sequência foi observada de qualquer maneira até a adição de Parallel Appendplanos paralelos com o Postgres 11 . Simplesmente porque Appendera a única opção de plano - que é (foi) implementada dessa maneira.

    Ainda funciona no Postgres 15 atual , desde que Parallel Appendnão esteja envolvido - o que só acontece para grandes conjuntos. Você pode ter certeza disso desativando essa opção. O manual:

    enable_parallel_appendpode ser usado para desativar esse recurso.

    Você pode definir essa opção localmente, mesmo apenas para a transação atual corrigir o código antigo rapidamente com:

    SET LOCAL enable_parallel_append = off;

    Não se prenda a essa solução provisória. Melhor corrigir seu código SQL corretamente.

    Alternativas adequadas

    vladic comentou:

    [...] A questão aqui é que se você estiver usando ORDER BY, você perde a funcionalidade conveniente de "executar em uma ordem específica até que um determinado número de linhas seja recebido". Não vejo outra maneira de fazer isso além de executar várias consultas sequencialmente (o que pode ser menos eficiente e também requer SQL dinâmico em geral).

    A próxima melhor coisa que posso pensar é uma função PL/pgSQL construindo o conjunto. Você pode verificar o número de linhas de resultados após cada consulta com GET DIAGNOSTICS, modificar (reduzir) o LIMITpara a próxima consulta e RETURNassim que encontrar o suficiente. Pode ser feito sem SQL dinâmico (portanto, não é EXECUTEnecessário), pois LIMITaceita um parâmetro. Ver:

    Consideravelmente mais código. Mais despesas de planejamento. Mas pelo lado positivo: o adaptado dinamicamente LIMITpode até produzir melhores planos de consulta para consultas subsequentes.

    Outro exemplo, repetindo a mesma consulta em um loop, também sem SQL dinâmico:

    Exemplo de código para uma dinâmica LIMITenquanto também usa SQL dinâmico EXECUTEem um loop:

    • 3

relate perguntas