Intagg

From PostgreSQL 中文维基, PostgreSQL 中文站, PostgreSQL 中国社区, PostgreSQL Chinese community

[编辑] intagg

intagg 模块提供一个整数聚集器和一个枚举器。

[编辑] 函数

aggregator 是一个聚集函数 int_array_aggregate(integer)，它生成一个整数数组，包含所有喂给它的所有整数。下面是一个不那么有用的例子：

 test=# select int_array_aggregate(i) from
 test-#   generate_series(1,10,2) i;
  int_array_aggregate
 ---------------------
  {1,3,5,7,9}
 (1 row)

枚举器是一个函数 int_array_enum(integer[])，它返回 setof integer。它实际上是聚集器的逆操作：给出一个整数数组，把它展开成一个行集合。比如，

 test=# select * from int_array_enum(array[1,3,5,7,9]);
  int_array_enum
 ----------------
               1
               3
               5
               7
               9
 (5 rows)

[编辑] 用例

许多数据库系统有一对多表的概念。这样的表通常在两个有索引的表中间，比如：

 CREATE TABLE left (id INT PRIMARY KEY, ...);
 CREATE TABLE right (id INT PRIMARY KEY, ...);
 CREATE TABLE one_to_many(left INT REFERENCES left, right INT REFERENCES right);

通常的用法是这样：

 SELECT right.* from right JOIN one_to_many ON (right.id = one_to_many.right)
   WHERE one_to_many.left = item;

这样会为一个左手边表的一条记录返回所有右手边表的项。这是一个SQL里面非常常见的构造。

那么，这种方法在 one_to_many 表里的记录数极大的时候可能会非常笨拙。通常，像这样的连接会导致为特定的左手边的行，在 one_to_many 表里的每条右手边行做一次索引扫描和抓取。如果你有一个非常动态的系统，那我们没啥可做的。但是，如果你有一些数据相当静态，你可以用聚集器创建一个摘要表。

 CREATE TABLE summary as
   SELECT left, int_array_aggregate(right) AS right
   FROM one_to_many
   GROUP BY left;

这样会创建一个每个左手边行生成一行的表。而如果我们没有啥特定方法使用这个数组，那就几乎没用；这就是要用到数组枚举器的地方。你可以用

 SELECT left, int_array_enum(right) FROM summary WHERE left = item;

上面使用 int_array_enum 的查询生成和下面

 SELECT left, right FROM one_to_many WHERE left = item;

一样的结果。区别是对 summary 表的查询只用从表中获取一行，而直接在 one_to_many 上的查询必须为每行都进行索引扫描和数据抓取。

在某个系统上，一个 EXPLAIN 显示一个开销为 8488 的查询的开销缩减为 329。第一个查询是和 one_to_many 表连接的，然后被下面这个代替：

 SELECT right, count(right) FROM
   ( SELECT left, int_array_enum(right) AS right
     FROM summary JOIN (SELECT left FROM left_table WHERE left = item) AS lefts
          ON (summary.left = lefts.left)
   ) AS list
   GROUP BY right
   ORDER BY count DESC;