AnalyticDB PostgreSQL版提供了自动增量排序功能，本文为您介绍如何使用自动增量排序功能。

功能简介

自动增量排序（AutoMerge）是AnalyticDB PostgreSQL版在后台运行的数据自动排序进程。自动增量排序会定期巡检表的数据状态，对新增无序数据进行排序，并与已有有序数据进行增量归并。

自动增量排序功能默认全局开启，无需使用自动增量排序的表可以关闭该功能，具体操作方法，请参见开启或关闭表级别自动增量排序。

如果您需要全局关闭自动增量排序功能，请提交工单联系技术支持进行关闭。

说明 自动增量排序有助于查询的性能提升，但是增量排序生成新的有序文件需要占用一定资源，尤其是I/O资源。

使用限制

  • 自动增量排序功能仅支持设置了排序键的AO表（AO行存表和AOCS列存表）。您可以通过如下命令查看是否为AO表：
    SELECT reloptions FROM pg_class WHERE relname = 'table_name';

    返回示例如下，其中appendonly=true表示该表为AO表： 

                                  reloptions
----------------------------------------------------------------------
 {appendonly=true,orientation=column,compresstype=lz4,compresslevel=9}
(1 row)
  • 自动增量排序功能依赖Autovacuum功能，如需使用自动增量排序请确保Autovacuum功能已开启。关于Autovacuum功能，请参见维护定期回收空间任务。
  • 自动增量排序功能需要AnalyticDB PostgreSQL版为V6.3.5.0及以上版本，如何查看和升级内核版本，请参见查看内核小版本和版本升级。

定义排序键

如果您希望表具备自动排序的能力，则需要在DDL语句中定义排序键。

  • 新建表时添加排序键，示例如下：
    CREATE TABLE table_name(
col_name type,
...
)
WITH(appendonly = true, orientation = row/column)
DISTRIBUTED BY(distributed_keys)
ORDER BY(sort_keys)
;
  • 对已创建的表使用ALTER命令添加或修改排序键，示例如下：
    ALTER TABLE table_name SET ORDER BY(sort_keys);

开启或关闭表级别自动增量排序

AnalyticDB PostgreSQL版提供了表级别的参数设置，可以灵活开启或关闭自动增量排序功能。

  • 开启表级别自动增量排序功能：
    ALTER TABLE table_name SET (automerge = on);
  • 关闭表级别自动增量排序功能：
    ALTER TABLE table_name SET (automerge = off);
  • 查询表的自动增量排序功能是否开启，示例SQL如下：
    SELECT relname, reloptions FROM pg_class WHERE relname = 'table_name';

    返回示例如下：

      relname   |                     reloptions
------------+----------------------------------------------------
 table_name | {appendonly=true,orientation=column,automerge=on}

    automerge=on表示已开启自动增量排序功能，如果没有返回该内容，则表示表级别自动增量排序功能与系统保持一致，此时您可以通过以下SQL进行确认： 

    SHOW automerge;

自动增量排序的性能收益测试

以下内容以过滤计算为例，展示自动增量排序对于查询性能带来的收益。

  1. 确认AutoVacuum和AutoMerge功能已开启，命令如下：
    SHOW autovacuum;
SHOW automerge;

    返回值为on则表示已开启。

  2. 关闭Laser引擎功能，命令如下：
    SET laser.enable = off;
    说明 关闭Laser引擎功能仅用于对比自动增量排序前后的加速效果，实际使用时建议开启。更多关于Laser引擎功能介绍，请参见Laser计算引擎的使用。
  3. 新建一张表，并导入随机数据。
    CREATE TABLE test_automerge(a bigint, b bigint)
WITH(appendonly = true, orientation = column, compresstype = lz4, compresslevel = 9)
DISTRIBUTED BY(a)
ORDER BY(b);
  4. 调整合入排序（Merge）数据的阈值，用于体现排序对性能的影响。
    ALTER TABLE test_automerge set (automerge_unsorted_row_num_threshold = 10000000);

INSERT INTO test_automerge SELECT i, round(random() * 100) FROM generate_series(1, 9000000) as i;
  5. 执行过滤查询。
    SELECT count(*) FROM test_automerge WHERE b = 5;

    返回结果如下：

     count
-------
 89713
(1 row)

Time: 204.918 ms
  6. 继续导入数据，触发自动增量排序。
    INSERT INTO test_automerge SELECT i, round(random() * 100) FROM generate_series(1, 1000000) as i;
  7. 等待增量数据合入排序完成后，再次进行查询。
    SELECT count(*) FROM test_automerge WHERE b = 5;

    返回结果如下：

     count
-------
 99683
(1 row)

Time: 15.289 ms
    说明 自动增量排序的任务进程可以在pg_stat_activity视图中查询。

通过以上示例可以看出，排序对过滤场景的加速较为显著。排序对于计算性能的提升不仅仅局限于过滤查询场景，在Aggregate、Join、OrderBy等场景中均有非常出色的表现，因此合理的选择排序键非常关键。如何选择排序键可以参考以下建议：

  • 优先考虑过滤场景，选择选择率低的过滤列作为排序键。
  • 若表的Aggregate、Join、OrderBy计算场景占比很高，则选择GroupKey、JoinKey、OrderKey作为排序键，并且此种场景最好保证分布键和排序键一致。

相关文档

排序加速计算