Início / dba / Perguntas / 333106
Accepted
elkronequis
Asked: 2023-11-15 06:11:43 +0800 CST

Qual é a ordem das operações no PostgreSQL?

  • 772

Sou estudante de banco de dados e executei a seguinte consulta para aprender algumas coisas ao mesmo tempo (LEFT/RIGHT JOIN, UNION, WHERE + RegEx). O que me preocupa é a ordem de execução. Eu tenho duas tabelas, como tal:

create table practicaleft(
    id smallint primary key,
    nombre varchar,
    cumple date
);

create table practicaright(
    id smallint primary key,
    apellido varchar,
    cumpleanios date
);

Então, insiro alguns dados aleatórios:

INSERT INTO practicaleft VALUES
(1, 'John', CURRENT_DATE - 1),
(5, 'Alice', CURRENT_DATE - 5),
(3, 'Bob', CURRENT_DATE - 3),
(7, 'Eva', CURRENT_DATE - 7);

INSERT INTO practicaright VALUES
(5, 'Doe', CURRENT_DATE - 5),
(6, 'Smith', CURRENT_DATE - 6),
(3, 'Johnson', CURRENT_DATE - 3),
(4, 'Brown', CURRENT_DATE - 4);

Depois, executo esta consulta:

select id, nombre
from practicaleft
where nombre similar to 'A%'
union
select pr.id, pr.apellido
from practicaright pr
where pr.id = 4 or pr.apellido ilike '_o%'
union all
select id, apellido
from practicaright 
where cumpleanios > current_date - 5;

Os resultados? Aqui você vai:

4   "Brown"
5   "Alice"
5   "Doe"
3   "Johnson"
3   "Johnson"
4   "Brown"

TL;DR: esta consulta é dividida em três partes e os resultados são mesclados com o operador UNION ALL.

Agora vem a questão. Pode-se acreditar que isso é executado instrução por instrução e, portanto, a ordem deveria ser:

5   "Alice"
5   "Doe"
3   "Johnson"
4   "Brown"
3   "Johnson"
4   "Brown"

Mas isso não está acontecendo. A única maneira de corrigir isso é adicionar alguma string aleatória como um campo, assim:

select id, nombre, 'part1' as query_part
from practicaleft
where nombre similar to 'A%'
union
select pr.id, pr.apellido, 'part2' as query_part
from practicaright pr
where pr.id = 4 or pr.apellido ilike '_o%'
union all
select id, apellido, 'part3' as query_part
from practicaright 
where cumpleanios > current_date - 25;

O que está acontecendo? Eu pulei alguma mecânica SQL realmente importante?

postgresql

3 respostas

  1. Best Answer

    A resposta de Jasen está correta - o PostgreSQL é livre para retornar as linhas na ordem que desejar, a menos que você adicione uma ORDER BYcláusula como esta:

    (SELECT ... UNION SELECT ... UNION ALL SELECT ...) ORDER BY ...;

    Você precisa dos parênteses para garantir que a ordem seja aplicada a todo o resultado e não apenas à última ramificação do arquivo UNION.

    Mas deixe-me explicar por que o PostgreSQL não retorna as linhas na ordem esperada. A razão é que o primeiro UNIONnão é UNION ALL. Se você tivesse usado UNION ALLem todos os lugares, o PostgreSQL executaria a consulta assim:

    EXPLAIN (COSTS OFF)
select id, nombre
from practicaleft
where nombre similar to 'A%'
union all
select pr.id, pr.apellido
from practicaright pr
where pr.id = 4 or pr.apellido ilike '_o%'
union all
select id, apellido
from practicaright  
where cumpleanios > current_date - 5;

                            QUERY PLAN                            
══════════════════════════════════════════════════════════════════
 Append
   ->  Seq Scan on practicaleft
         Filter: ((nombre)::text ~ '^(?:A.*)$'::text)
   ->  Seq Scan on practicaright pr
         Filter: ((id = 4) OR ((apellido)::text ~~* '_o%'::text))
   ->  Seq Scan on practicaright
         Filter: (cumpleanios > (CURRENT_DATE - 5))
(7 rows)

    Ou seja, o PostgreSQL executaria as três consultas e simplesmente anexaria os resultados, e você terminaria com a ordem esperada.

    Mas você usou unionpela primeira vez e unionelimina duplicatas. Isso é executado da seguinte maneira:

    EXPLAIN (COSTS OFF)
select id, nombre
from practicaleft
where nombre similar to 'A%'
union 
select pr.id, pr.apellido
from practicaright pr
where pr.id = 4 or pr.apellido ilike '_o%'
union all
select id, apellido
from practicaright
where cumpleanios > current_date - 5;

                                  QUERY PLAN                                  
══════════════════════════════════════════════════════════════════════════════
 Append
   ->  HashAggregate
         Group Key: practicaleft.id, practicaleft.nombre
         ->  Append
               ->  Seq Scan on practicaleft
                     Filter: ((nombre)::text ~ '^(?:A.*)$'::text)
               ->  Seq Scan on practicaright pr
                     Filter: ((id = 4) OR ((apellido)::text ~~* '_o%'::text))
   ->  Seq Scan on practicaright
         Filter: (cumpleanios > (CURRENT_DATE - 5))
(10 rows)

    O PostgreSQL usa um agregado de hash para remover duplicatas das duas primeiras ramificações. As linhas de resultados são retornadas na ordem em que estão na tabela hash, o que é bastante aleatório (boas funções hash se comportam assim).

    • 20

  2. SQL não garante a ordenação dos resultados, a menos que você tenha uma order bycláusula em sua consulta.

    Se você não disser "ordenar por", seus resultados virão na ordem que o planejador de consultas e o mecanismo de banco de dados decidirem ser mais eficiente (ou suficientemente eficiente).

    Varreduras de tabela paralelas são aquelas em que várias consultas examinam a mesma tabela ao mesmo tempo. mas suas tabelas de exemplo provavelmente são curtas demais para isso.

    Quando tenho uma sequência de union alls que desejo em ordem, adiciono uma coluna de ordenação com valores constantes à consulta. 1 as sort 2 as sort 3 as sort order by sort

    • 19

  3. Sinto muito, não sou um especialista em Postgres, mas a questão era sobre a ordem das operações, mas não sobre a ordem das linhas no conjunto de resultados. Existem união e união entre 3 conjuntos de dados. Digamos que temos o conjunto de dados 1,2,3 união 3,4,5 e união todos 5,6,7. Se a união for aplicada primeiro, o resultado deverá ser 1,2,3,4,5,5,6,7. se union all for executado primeiro, o resultado deverá ser 1,2,3,4,5,6,7 porque o agrupamento implícito da união é aplicado na última etapa. Por favor corrija-me se eu estiver errado.

    • 2

relate perguntas