分区表上的高效最小值/最大值

库片段

从分区表中查找最大值

适用于 PostgreSQL

8.3 到 9.0

编写语言

PL/pgSQL

依赖

PL/pgSQL

PostgreSQL 9.0 及更早版本不支持对分区/继承表进行索引的 MIN(x) 或 MAX(x) 查询。查看 MAX Partitioning with MIN Pain，以了解展示问题的测试案例，以及从 PostgreSQL 9.1 开始如何解决此问题。但是，在早期版本中，可以使用自定义 PL/pgSQL 函数来高效地实现这一点。

此代码片段由 Shaun Thomas 在 pgsql-performance 邮件列表中 贡献，并在其他人的反馈下进行了调整。该函数的原始版本适用于 PostgreSQL 8.2，并且可能适用于使用该版本的用户。由于使用了该版本中引入的 regclass 功能，因此此处简化的版本仅与 PostgreSQL 8.3 及更高版本兼容。

此函数适用于 bigint（以及整数/小整数）列并使用 MAX() 聚合。将其移植到其他数据类型或使其与 min() 一起使用并不困难。

函数定义

/**
* Return the Maximum bigint Value for a Partitioned Table Column
*
* @param string  Name of table, may be schema-qualified.
* @param string  Name of column to search.
*/
CREATE OR REPLACE FUNCTION spc_max_part_int(rParent regclass, sColName text)
RETURNS bigint AS
$$
DECLARE
  rChild regclass;
  nMax bigint;
  nTemp bigint;
BEGIN

  EXECUTE '
   SELECT max(' || quote_ident(sColName) ||')
     FROM ONLY ' || rParent
  INTO nMax;

  FOR rChild IN
    SELECT inhrelid
      FROM pg_inherits
      WHERE inhparent=rParent
  LOOP
    nTemp := spc_max_part_int(rChild, sColName);
    nMax := greatest(nTemp, nMax);
  END LOOP;

  RETURN nMax;

END;
$$ LANGUAGE plpgsql STABLE;

示例用法

db=> explain select max(foo_id) from foo;
                                      QUERY PLAN                                      
--------------------------------------------------------------------------------------
 Aggregate  (cost=177032.00..177032.01 rows=1 width=4)
   ->  Append  (cost=0.00..150477.20 rows=10621920 width=4)
         ->  Seq Scan on foo  (cost=0.00..34.00 rows=2400 width=4)
         ->  Seq Scan on foo_child1 foo  (cost=0.00..150443.20 rows=10619520 width=4)

db=> select max(foo_id) from foo;
   max    
----------
 10000000

Time: 2636.540 ms

db=> select spc_max_part_int('foo', 'foo_id');
 spc_max_part_int 
------------------
         10000000

Time: 0.556 ms