版本历史
寻求帮助

关于 YMatrix

立即体验

部署数据库

使用数据库

管理集群

安全配置

最佳实践

技术原理

高级功能

图数据库

管理手册

SQL 参考

常见问题

mxnumeric 使用手册

概况

PostgreSQL 的 numeric 类型（以下简称 pg numeric）虽然精度远高于其它数据库中的同名类型，但其实现更为复杂，性能也相对较差。pg numeric 难以实现向量化，导致在向量化执行器中不仅没有性能提升，反而因采用兼容机制运行，性能甚至低于非向量化执行器。与经过向量化加速的 int/float 等类型相比，pg numeric 的性能慢约 1-2 个数量级。 为了提高 numeric 类型的性能，实现了一个有限精度的 numeric 类型（以下简称 mxnumeric），最大支持 38 位精度，并通过 mxnumeric 扩展提供。

安装与升级

mxnumeric 作为默认 numeric 类型

mxnumeric 类型会作为默认的 numeric 类型（6.5 版本及以上），不需要额外安装和配置，数据库初始化之后使用的 numeric 则默认是 mxnumeric。 mxnumeric 类型最终使用的是 public.numeric 类型，同时数据库中仍然保留原有的 pg_catalog.numeric 类型。

手动安装

直接使用扩展命令安装。

CREATE EXTENSION mxnumeric;

扩展安装完成之后，会在当前 Database 中创建 public.numeric 类型，同时不会对原有的 pg_catalog.numeric 类型进行变更。

扩展升级

当 mxnumeric 扩展中进行了 bugfix 或优化时, 一般只需要安装新的安装包即可, 不需要额外操作。 但是当 mxnumeric 扩展提供的函数或运算符等数据库对象发生变更, 或是增加了新的此类内容时, 在一个已经部署好的集群中, 就需要升级 mxnumeric 扩展的版本, 这需要管理员手工进行操作。 视需求的不同, 操作方法也有差异, 以下分别列出, 并且假设我们是从 0.1 版本升级至 0.2 版本。

对当前库进行升级

对于已经创建好的库, 我们采用如下两种方法进行升级:

-- 连接到目标库
\c db1

-- 检查当前的安装状态与版本
\dx mxnumeric

-- 方法 1: 升级至指定版本
alter extension mxnumeric update to '0.2';

-- 方法 2: 升级至最新版本
alter extension mxnumeric update;

重新安装扩展当然也可以实现扩展版本的更新, 但是这样会由于依赖关系而删除所有已经创建的 mxnumeric 字段, 因此非必要情况不要采用此方法.

对模板库进行升级

如果我们希望新创建的库也能够自动使用新版本的 mxnumeric 扩展, 就需要在模板库中更新扩展的版本.

-- 更新 template1 库中的 mxnumeric 版本
\c template1
alter extension mxnumeric update;

-- 更新 template0 库中的 mxnumeric 版本, 注意此库默认不允许连接, 因此需要额外操作
\c postgres
alter database template0 allow_connections on;

\c template0
alter extension mxnumeric update;

\c postgres
alter database template0 allow_connections off;

经过上述操作后, 再创建的新库中的 mxnumeric 扩展的版本就会直接为最新版本.

识别 mxnumeric 类型

因为同时存在两套 numeric 类型，所以需要注意区分使用的 numeric 类型。

目前常用的判断方式有两种，

  • 一个是判断 GUC mx_force_system_types 是否是 false，如果是 false 则使用的是 mxnumeric；
show mx_force_system_types;
 mx_force_system_types
-----------------------
 off
(1 row)
  • 另一个是直接查看 numeric 类型的 oid，如果不是 1700，则使用的是 mxnumeric。
select 'numeric'::regtype::oid;
  oid
-------
 13862
(1 row)

判断表中 numeric 列是否 mxnumeric 类型，可以通过 mxnumeric.list_numeric_columns(table_name) 和 mxnumeric.list_numeric_columns(schema_name, table_name) 两个函数列出其所有的 numeric 列，以及其使用的真实 numeric 类型。

select attnum, attname, type from mxnumeric.list_numeric_columns('t1');
 attnum | attname |        type
--------+---------+--------------------
      2 | col1    | pg_catalog.numeric
      3 | col2    | public.numeric
(2 rows)

select attnum, attname, type from mxnumeric.list_numeric_columns('public', 't1');
 attnum | attname |        type
--------+---------+--------------------
      2 | col1    | pg_catalog.numeric
      3 | col2    | public.numeric
(2 rows)

使用

配置 GUC 参数 mx_force_system_types

设置 GUC 参数 mx_force_system_types=false 之后，使用 numeric 类型会使用 public.numeric。如果有限精度 numeric 已经作为默认 numeric 类型，则 GUC mx_force_system_types 默认为 false。

  • 设置为 true：使用系统自带的 pg_catalog.numeric 类型
  • 设置为 false：使用 mxnumeric 扩展提供的 public.numeric 类型

精度范围

有限精度 numeric 类型支持的精度范围为 1-38，创建表时超过 38 位会提示错误。

create table t2(a int, b numeric(39, 0));
ERROR:  NUMERIC precision 39 must be between 1 and 38

mxnumeric 类型的计算结果超过 38 位精度，会提示 overflow 错误。

-- 期望乘法结果为 numeric(40,0)，mxnumeric 精度超过 38 导致 overflow 错误
select 12345678901234567890::public.numeric * 12345678901234567890::public.numeric;
ERROR:  value overflows numeric format (Error.cc:26)

-- pg_catalog.numeric 不会报错
select 12345678901234567890::pg_catalog.numeric * 12345678901234567890::pg_catalog.numeric;
                ?column?
-----------------------------------------
 152415787532388367501905199875019052100

select pg_typeof(12345678901234567890::pg_catalog.numeric * 12345678901234567890::pg_catalog.numeric);
 pg_typeof
-----------
 numeric

小数位数越界

当精度指定为 38 时，如果计算结果会超过指定的小数位数时，则也会提示 overflow 错误。 比如下面计算，pg_catalog.numeric 因为支持更高的精度，所以能够支持 9 位小数，而 public.numeric 则会提示 overflow 错误。

select 123456789012345678901234567890.12345678::pg_catalog.numeric(38,8) * 0.9;
                 ?column?
------------------------------------------
 111111110111111111011111111101.111111102
(1 row)


select 123456789012345678901234567890.12345678::public.numeric(38,8) * 0.9;
ERROR:  value overflows numeric format (Error.cc:26)

除法返回值类型

mxnumeric 的除法返回值类型为 double precision。pg numeric 的除法返回值类型为 numeric，

因为 SQL 标准并未定义 numeric 除法的精度要求，另外 PostgreSQL 中则要求除法运算的精度不低于 float8 的精度，所以认为 mxnumeric 的除法结果保持 float8 即可，这样可以让 numeric 除法最大化性能。

因为除法返回值类型变为 float8，所以除法计算存在一定精度上的损失，比如：

select 1234.5678901234567890::pg_catalog.numeric(20,16) / 666;
      ?column?
--------------------
 1.8537055407259111
(1 row)

-- mxnumeric 除法有一定的精度损失
select 1234.5678901234567890::public.numeric(20,16) / 666;
      ?column?
--------------------
 1.8537055407259109

函数返回值类型

下面函数的返回值类型也有优化，从 numeric 类型修改为返回 float8 类型：

  • exp()
  • ln()
  • log()
  • pow/power()
  • sqrt()

功能定制

有一些额外的功能无法在扩展创建的流程中完成，需要手动配置。

提供了 mxnumeric.set_config(text,bool) 函数，用于设置 mxnumeric 的配置，以及一个 mxnumeric.configs 视图，用于显示当前支持的额外配置项以及其状态。

select * from mxnumeric.configs;
       config       | enabled |                                          description
--------------------+---------+------------------------------------------------------------------------------------------------
 linear_interpolate | f       | replace original linear_interpolate functions with mxnumeric version to support mxnumeric type
 mars3              | f       | create operator classes to support mars3 btree & brin index for mxnumeric type

linear_interpolate 函数配置

内核的 linear_interpolate() 函数已经写死了支持的数据类型，所以无法直接使其支持 mxnumeric 类型，并且该函数的使用频次比较低，所以暂时不默认支持。

如果遇到需要使用 linear_interpolate() 函数的场景，可以使用 mxnumeric.set_config('linear_interpolate', true) 来支持 mxnumeric 类型。其逻辑是将 pg_catalog.linear_interpolate() 函数重命名进行备份操作，然后创建 mxnumeric 版本的 public.linear_interpolate() 函数替换。 可以使用 mxnumeric.set_config('linear_interpolate', false) 来恢复到默认的 pg_catalog.linear_interpolate() 函数，并删除 mxnumeric 版本的 public.linear_interpolate() 函数。

select mxnumeric.set_config('linear_interpolate', true);

mars3 配置

因为 mars3 和 mxnumeric 都是扩展，不能保证 mars3 的安装顺序一定在 mxnumeric 之前，所以默认 mxnumeric 类型是不支持 mars3_btree 和 mars3_brin 索引类型的。

如果需要使用 mars3_btree 和 mars3_brin 索引类型，可以使用 mxnumeric.set_config('mars3', true) 来手动创建 operator class，以支持 mxnumeric 类型使用 mars3_btree 和 mars3_brin 索引类型。不过必须保证 matrixts 插件已经创建，否则会提示错误。

select mxnumeric.set_config('mars3', true);

并且可以使用 mxnumeric.set_config('mars3', false) 来删除 operator class，恢复到默认状态。

附录

mxnumeric 函数清单

普通函数

普通函数指的是上下文和场景无关的普通的函数, 与之对比的是聚集函数, 只能使用于聚集运算中.

math 函数
oid UDF 函数名 target schema 有损运算 原始结果类型 优化结果类型 参数类型 结果精度 函数类型 用途 alias of
1705 abs public N numeric numeric = plain math
1711 ceil public N numeric numeric (P,0) plain math
2167 ceiling public N numeric numeric (P,0) plain math ceil
1973 div public Y numeric double numeric, numeric plain math
1732 exp public Y numeric double numeric plain math
1376 factorial public N numeric bigint (MAX,0) plain math
1712 floor public N numeric numeric (P,0) plain math
1734 ln public Y numeric double numeric plain math
1736 log public Y numeric double numeric, numeric plain math
1741 log public Y numeric double numeric plain math
1481 log10 ? Y numeric double numeric plain math
1728 mod public N numeric numeric, numeric = plain math
1704 numeric_abs mxnumeric N numeric numeric = plain math abs
1724 numeric_add mxnumeric N numeric numeric, numeric + plain math
6997 numeric_dec mxnumeric N numeric numeric = plain math
1727 numeric_div mxnumeric Y numeric double numeric, numeric plain math
1980 numeric_div_trunc mxnumeric Y numeric double numeric, numeric plain math
1733 numeric_exp mxnumeric Y numeric double numeric plain math exp
111 numeric_fac mxnumeric N numeric bigint (MAX,0) plain math factorial
1764 numeric_inc mxnumeric N numeric numeric = plain math
1767 numeric_larger mxnumeric N numeric numeric, numeric = plain math
1735 numeric_ln mxnumeric Y numeric double numeric plain math ln
1737 numeric_log mxnumeric Y numeric double numeric plain math log
1729 numeric_mod mxnumeric N numeric numeric, numeric = plain math mod
1726 numeric_mul mxnumeric N numeric numeric, numeric * plain math
1739 numeric_power mxnumeric Y numeric double numeric, numeric plain math power
1766 numeric_smaller mxnumeric N numeric numeric, numeric = plain math
1731 numeric_sqrt mxnumeric Y numeric double numeric plain math sqrt
1725 numeric_sub mxnumeric N numeric numeric, numeric + plain math
1771 numeric_uminus mxnumeric N numeric numeric = plain math
1915 numeric_uplus mxnumeric N numeric numeric = plain math
1738 pow public Y numeric double numeric plain math power
2169 power public Y numeric double numeric plain math
1707 round public N numeric numeric, integer (P,arg) plain math
1708 round public N numeric numeric (P,0) plain math
1730 sqrt public Y numeric double numeric plain math
1709 trunc public N numeric numeric, integer (P,arg) plain math
1710 trunc public N numeric numeric (P,0) plain math
  • numeric_larger() & numeric_smaller() 是用于 numeric 的 min() & max() 聚集的, 因此可以要求两个参数精度相同, 因此结果精度也相同.
  • numeric_uminus() & numeric_uplus() 是用于 numeric 单目运算符 +x, -x 的.
  • 表中部分函数是采用 language sql 实现的其它函数的参数绑定, 比如 log10(x) 就是 log(10, x), 由于 numericX 预期不支持 C 以外的 language, 因此我们需要单独为它们提供 C 版本的实现.
operator 函数
oid UDF 函数名 target schema 有损运算 原始结果类型 优化结果类型 参数类型 结果精度 函数类型 用途
1769 numeric_cmp mxnumeric integer numeric, numeric plain oper
1718 numeric_eq mxnumeric bool numeric, numeric plain oper
1721 numeric_ge mxnumeric bool numeric, numeric plain oper
1720 numeric_gt mxnumeric bool numeric, numeric plain oper
1723 numeric_le mxnumeric bool numeric, numeric plain oper
1722 numeric_lt mxnumeric bool numeric, numeric plain oper
1719 numeric_ne mxnumeric bool numeric, numeric plain oper
index 函数
oid UDF 函数名 target schema 有损运算 原始结果类型 优化结果类型 参数类型 结果精度 函数类型 用途
6145 cdblegacyhash_numeric public integer numeric plain index
432 hash_numeric public integer numeric plain index
780 hash_numeric_extended public bigint numeric plain index
4141 in_range public bool numeric, numeric, numeric, boolean, boolean plain index
cast 函数
oid UDF 函数名 target schema 有损运算 原始结果类型 优化结果类型 参数类型 结果精度 函数类型 用途
1745 float4 public real numeric plain cast
1746 float8 public double numeric plain cast
1783 int2 public smallint numeric plain cast
1744 int4 public integer numeric plain cast
1779 int8 public bigint numeric plain cast
3824 money public money numeric plain cast
1703 numeric public numeric numeric, int plain cast
1740 numeric public numeric integer plain cast
1742 numeric public numeric real plain cast
1743 numeric public numeric double plain cast
1781 numeric public numeric bigint plain cast
1782 numeric public numeric smallint plain cast
3449 numeric public numeric jsonb plain cast
3823 numeric public numeric money plain cast
7597 numeric2complex public complex numeric plain cast
3844 numrange public numrange numeric, numeric plain cast
3845 numrange public numrange numeric, numeric, text plain cast
3924 numrange_subdiff public double numeric, numeric plain cast
1772 to_char public text numeric, text plain cast
1777 to_number public numeric text, text plain cast
util 函数
oid UDF 函数名 target schema 有损运算 原始结果类型 优化结果类型 参数类型 结果精度 函数类型 用途
3259 generate_series public N numeric numeric, numeric, numeric = plain util
3260 generate_series public N numeric numeric, numeric = plain util
3281 scale public integer numeric plain util
1706 sign public N numeric numeric = plain util
2170 width_bucket public integer numeric, numeric, numeric, integer plain util
internal 函数
oid UDF 函数名 target schema 有损运算 原始结果类型 优化结果类型 参数类型 结果精度 函数类型 用途
7082 interval_bound public N numeric numeric, numeric = plain internal
7083 interval_bound public N numeric numeric, numeric, integer = plain internal
7084 interval_bound public N numeric numeric, numeric, integer, numeric = plain internal
6082 linear_interpolate public numeric anyelement, anyelement, numeric, anyelement, numeric plain internal
1701 numeric_in mxnumeric numeric cstring, oid, integer (P,S) plain internal
1702 numeric_out mxnumeric cstring numeric plain internal
2460 numeric_recv mxnumeric numeric internal, oid, integer (P,S) plain internal
2461 numeric_send mxnumeric bytea numeric plain internal
3237 pg_lsn_mi ? numeric pg_lsn, pg_lsn plain internal
3166 pg_size_pretty ? text numeric plain internal
2022 pg_stat_get_activity ? record integer plain internal
1136 pg_stat_get_wal ? record plain internal
3165 pg_wal_lsn_diff ? numeric pg_lsn, pg_lsn plain internal

聚集函数

壳函数
oid UDF 函数名 schema 有损运算 原始结果类型 优化结果类型 参数类型 结果精度 函数类型 用途
2100 avg public numeric bigint (MAX, S) agg
2101 avg public numeric integer (MAX, S) agg
2102 avg public numeric smallint (MAX, S) agg
2103 avg public numeric numeric (MAX, S) agg
2130 max public numeric numeric = agg
2146 min public numeric numeric = agg
2154 stddev public numeric bigint agg
2155 stddev public numeric integer agg
2156 stddev public numeric smallint agg
2159 stddev public numeric numeric agg
2724 stddev_pop public numeric bigint agg
2725 stddev_pop public numeric integer agg
2726 stddev_pop public numeric smallint agg
2729 stddev_pop public numeric numeric agg
2712 stddev_samp public numeric bigint agg
2713 stddev_samp public numeric integer agg
2714 stddev_samp public numeric smallint agg
2717 stddev_samp public numeric numeric agg
2107 sum public numeric bigint (MAX, S) agg
2114 sum public numeric numeric (MAX, S) agg
2718 var_pop public numeric bigint agg
2719 var_pop public numeric integer agg
2720 var_pop public numeric smallint agg
2723 var_pop public numeric numeric agg
2641 var_samp public numeric bigint agg
2642 var_samp public numeric integer agg
2643 var_samp public numeric smallint agg
2646 var_samp public numeric numeric agg
2148 variance public numeric bigint agg
2149 variance public numeric integer agg
2150 variance public numeric smallint agg
2153 variance public numeric numeric agg
实现函数
oid UDF 函数名 schema 有损运算 原始结果类型 优化结果类型 参数类型 结果精度 函数类型 用途
1964 int8_avg public numeric bigint[] agg helper legacy
1842 int8_sum public numeric numeric, bigint agg helper legacy
1833 numeric_accum mxnumeric internal internal, numeric agg helper
3548 numeric_accum_inv mxnumeric internal internal, numeric agg helper
1837 numeric_avg mxnumeric numeric internal agg helper
2858 numeric_avg_accum mxnumeric internal internal agg helper
3389 numeric_poly_avg mxnumeric numeric internal agg helper
3392 numeric_poly_stddev_pop mxnumeric numeric internal agg helper
3393 numeric_poly_stddev_samp mxnumeric numeric internal agg helper
3388 numeric_poly_sum mxnumeric numeric internal agg helper
3390 numeric_poly_var_pop mxnumeric numeric internal agg helper
3391 numeric_poly_var_samp mxnumeric numeric internal agg helper
2596 numeric_stddev_pop mxnumeric numeric internal agg helper
1839 numeric_stddev_samp mxnumeric numeric internal agg helper
3178 numeric_sum mxnumeric numeric internal agg helper
2514 numeric_var_pop mxnumeric numeric internal agg helper
1838 numeric_var_samp mxnumeric numeric internal agg helper
← 上一篇
JSON/JSONB、MXKV/MXKV2
下一篇 →
空间数据类型