Início / dba / Perguntas / 119575
Accepted
Parker
Asked: 2015-10-30 07:55:55 +0800 CST

Como evitar invocar funções duas vezes ao usar GROUP BY e HAVING?

  • 772

Tenho um banco de dados PostgreSQL (9.2) com uma tabela de relações pai-filho. Eu tenho uma consulta que procura nós com vários pais.

A consulta a seguir funciona e retorna os resultados corretos:

SELECT node,parents FROM
(
  SELECT nr.child AS node, COUNT(nr.parent) AS parents 
  FROM node_relation nr 
  GROUP BY nr.child
) AS count WHERE parents > 1;

O conjunto de resultados:

 node   | parents
--------+---------
 n21174 |       2
 n8635  |       2
(2 rows)

A definição da tabela é:

            Table "public.node_relation"
   Column    |         Type          |   Modifiers
-------------+-----------------------+---------------
 child       | character varying(50) | not null
 parent      | character varying(50) | not null
Indexes:
    "node_relation_pkey" PRIMARY KEY, btree (child, parent)

Eu reescrevi a consulta para não usar uma sub-seleção:

SELECT child AS node, COUNT(parent) AS parents 
FROM node_relation 
GROUP BY child 
HAVING COUNT(parent) > 1;

A nova consulta funciona, mas me pergunto se a função COUNT está sendo invocada várias vezes.

Atualização: Aqui está o plano de consulta:

                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=0.00..1658.81 rows=19970 width=16)
   Filter: (count(parent) > 1)
   ->  Index Only Scan using node_relation_pkey on node_relation  (cost=0.00..1259.40 rows=19971 width=16)

Eu preferiria usar o parentsalias, mas o seguinte não funciona:

SELECT child AS node, COUNT(parent) AS parents 
FROM node_relation 
GROUP BY child 
HAVING parents > 1;

ERROR:  column "parents" does not exist
LINE 1: ...parents FROM node_relation GROUP BY child HAVING parents > ...
                                                            ^

O PostgreSQL otimizará as múltiplas invocações de COUNT?

Se não, existe uma forma alternativa dessa consulta que seria mais eficiente?

postgresql performance

1 respostas

  1. Best Answer

    Sua segunda consulta (aquela em que você a implementa com a HAVINGcláusula) provavelmente é mais rápida. Em sua primeira consulta (com a subseleção), o postgres precisa calcular os valores de contagem para toda a tabela. Em sua segunda consulta, ele pode começar a ignorar as linhas a serem contadas assim que atingir um valor de contagem acima de 1 (embora eu não saiba 100% se o postgres é inteligente o suficiente para fazer isso - tenho quase certeza de que sim).

    Como COUNT()é uma função agregada, ela será executada o número de vezes que for executada, independentemente do número de linhas retornadas. Se você tivesse uma função que NÃO fosse uma função agregada, executar seu grupo e cláusula where/haver em uma subseleção provavelmente seria mais rápido.

    Exemplo do que estou me referindo:

    SELECT
  some_non_agg_function(a.id, a.child)
FROM join_tab1 a
GROUP BY a.id, a.child
HAVING COUNT(a.id) > 1;
-- probably not as fast as
WITH rows_to_process AS (
  SELECT DISTINCT
    id, child
  FROM join_tab1 a
  GROUP BY a.id, a.child
  HAVING COUNT(a.id) > 1
) SELECT
  some_non_agg_function(id, child)
FROM rows_to_process;

    Para responder especificamente à sua pergunta - sim, o postgres acompanhará quais valores agregados calculou e os reutilizará (em vez de recalculá-los) dentro da HAVINGcláusula. Acredito que também os reutilizará na SELECTcláusula (se, por algum motivo estranho, você executar exatamente o mesmo agregado mais de uma vez no SELECT)

    Para citar a excelente documentação do Postgres (meu em negrito)

    É importante entender a interação entre agregações e as cláusulas WHERE e HAVING do SQL. A diferença fundamental entre WHERE e HAVING é esta: WHERE seleciona linhas de entrada antes que grupos e agregados sejam calculados (assim, ele controla quais linhas vão para o cálculo agregado), enquanto HAVING seleciona linhas de grupo após grupos e agregados serem computados. Assim, a cláusula WHERE não deve conter funções agregadas; não faz sentido tentar usar um agregado para determinar quais linhas serão entradas para os agregados. Por outro lado, a cláusula HAVING sempre contém funções agregadas. (Estritamente falando, você tem permissão para escrever uma cláusula HAVING que não use agregações, mas raramente é útil. A mesma condição pode ser usada com mais eficiência no estágio WHERE.)

    Isso não diz especificamente que reutiliza os valores calculados .. mas implica dizer que a HAVINGcláusula é usada depois que os agregados são calculados.

    • 5

relate perguntas