主页 / dba / 问题 / 119575
Accepted
Parker
Asked: 2015-10-30 07:55:55 +0800 CST

使用 GROUP BY 和 HAVING 时如何避免两次调用函数？

  • 772

我有一个带有父子关系表的 PostgreSQL 数据库（9.2）。我有一个查询来查找具有多个父节点的节点。

以下查询有效并返回正确的结果：

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;

结果集：

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

表定义为：

            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)

我重写了查询以不使用子选择：

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

新查询有效，但我想知道 COUNT 函数被多次调用。

更新：这是查询计划：

                                                 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)

我更喜欢使用parents别名，但以下不起作用：

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 > ...
                                                            ^

PostgreSQL 会优化 ? 的多次调用COUNT？

如果没有，是否有这种查询的替代形式会更有效？

postgresql performance

1 个回答

  1. Best Answer

    您的第二个查询（您使用HAVING子句实现它的那个）可能更快。在您的第一个查询（使用子选择）中，postgres 必须计算整个表的计数值。在您的第二个查询中，一旦计数值超过 1，它就可以开始忽略要计数的行（尽管我不 100% 知道 postgres 是否足够聪明地做到这一点 - 不过我很确定它是）。

    由于COUNT()是一个聚合函数，因此无论返回的行数如何，它都会运行它运行的次数。如果您有一个不是聚合函数的函数，那么在子选择中运行您的组和 where/have 子句可能会更快。

    我所指的示例：

    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;

    HAVING要具体回答您的问题 - 是的，postgres 将跟踪它计算的聚合值并在子句中重新使用它们（而不是重新计算它们） 。我相信它也会在SELECT子句中重新使用它们（如果出于某种奇怪的原因，您在 中多次运行完全相同的聚合SELECT）

    引用Postgres 的优秀文档（我的粗体）

    了解聚合与 SQL 的 WHERE 和 HAVING 子句之间的交互非常重要。WHERE 和 HAVING 之间的根本区别在于：WHERE 在计算组和聚合之前选择输入行（因此，它控制哪些行进入聚合计算），而 HAVING 在计算组和聚合之后选择组行。因此，WHERE 子句不能包含聚合函数；尝试使用聚合来确定哪些行将作为聚合的输入是没有意义的。另一方面，HAVING 子句总是包含聚合函数。（严格来说，您可以编写不使用聚合的 HAVING 子句，但它很少有用。在 WHERE 阶段可以更有效地使用相同的条件。）

    这并没有具体说它重新使用计算值.. 但它暗示它说该HAVING子句是在计算聚合之后使用的。

    • 5

相关问题