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作为排序键,并且此种场景最好保证分布键和排序键一致。

相关文档

排序加速计算