列存索引（IMCI）实战：商品品类停留时长分析

业务需求

统计最近一年内，每个商品品类所有用户的停留总时长。

在传统 DynamoDB 中，服务端无法完成聚合运算，需要通过过滤表达式获取数据后在业务代码中进行聚合；数据量大时延迟和读容量消耗显著上升。

PolarDB PostgreSQL版 DynamoDB兼容模式结合，可以在 PostgreSQL 引擎层面直接完成高性能分析查询，无需将数据拉取到业务侧。

查看底层表结构

所有 DynamoDB 表存储在 polardb_internal_dynamodb 数据库中，Schema 名称与 DynamoDB 账号名一致。以账号名 dynamodb 为例，连接 polardb_internal_dynamodb 数据库后执行以下 SQL 查看表数据：

SELECT * FROM dynamodb.usertable LIMIT 5;

执行结果：

polardb_internal_dynamodb=# select * from dynamodb.usertable limit 5;
 userid |                                                              polardb_document
--------+------------------------------------------------------------------------------------------------------------------------------
 user1  | {"userid": {"S": "user1"}, "duration": {"N": "10"}, "event_time": {"S": "2026-01-01T10:00:00Z"}, "product_category": {"S": "Clothing"}}
 user2  | {"userid": {"S": "user2"}, "duration": {"N": "5"}, "event_time": {"S": "2026-01-02T11:30:00Z"}, "product_category": {"S": "Electronics"}}
 user3  | {"userid": {"S": "user3"}, "duration": {"N": "20"}, "event_time": {"S": "2026-01-03T09:15:00Z"}, "product_category": {"S": "Clothing"}}
 user4  | {"userid": {"S": "user4"}, "duration": {"N": "8"}, "event_time": {"S": "2026-01-04T20:00:00Z"}, "product_category": {"S": "Pet Products"}}
 user5  | {"userid": {"S": "user5"}, "duration": {"N": "12"}, "event_time": {"S": "2026-01-05T08:45:00Z"}, "product_category": {"S": "Electronics"}}
(5 rows)

其中 polardb_document 列是一个 JSONB 类型字段，存储了每条 DynamoDB 记录的完整业务数据。

创建生成列

为了在列存索引中加速按时间过滤和品类分组的聚合查询，需要从 polardb_document 这个 JSONB 列中提取查询所需的属性，创建为持久化生成列（Generated Column）：

ALTER TABLE dynamodb.usertable
    ADD COLUMN col_event_time TEXT GENERATED ALWAYS AS
        (polardb_document -> 'event_time' ->> 'S') STORED,
    ADD COLUMN col_product_category TEXT GENERATED ALWAYS AS
        (polardb_document -> 'product_category' ->> 'S') STORED,
    ADD COLUMN col_duration BIGINT GENERATED ALWAYS AS
        ((polardb_document -> 'duration' ->> 'N')::bigint) STORED;

创建完成后再次查看表信息：

polardb_internal_dynamodb=# select * from dynamodb.usertable limit 5;
 userid |                                                                          polardb_document                                                                           |    col_event_time     | col_product_category | col_duration
--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+----------------------+--------------
 user1  | {"userid": {"S": "user1"}, "duration": {"N": "10"}, "event_time": {"S": "2026-01-01T10:00:00Z"}, "product_category": {"S": "Clothing"}}      | 2026-01-01T10:00:00Z | Clothing             |           10
 user2  | {"userid": {"S": "user2"}, "duration": {"N": "5"}, "event_time": {"S": "2026-01-02T11:30:00Z"}, "product_category": {"S": "Electronics"}}   | 2026-01-02T11:30:00Z | Electronics          |            5
 user3  | {"userid": {"S": "user3"}, "duration": {"N": "20"}, "event_time": {"S": "2026-01-03T09:15:00Z"}, "product_category": {"S": "Clothing"}}     | 2026-01-03T09:15:00Z | Clothing             |           20
 user4  | {"userid": {"S": "user4"}, "duration": {"N": "8"}, "event_time": {"S": "2026-01-04T20:00:00Z"}, "product_category": {"S": "Pet Products"}}  | 2026-01-04T20:00:00Z | Pet Products         |            8
 user5  | {"userid": {"S": "user5"}, "duration": {"N": "12"}, "event_time": {"S": "2026-01-05T08:45:00Z"}, "product_category": {"S": "Electronics"}}  | 2026-01-05T08:45:00Z | Electronics          |           12
(5 rows)

生成列会在数据写入时自动从 JSONB 中提取对应值并持久化存储，后续查询可直接使用这些列。

创建列存索引

基于分区键和新增的生成列创建 CSI（Column Store Index）：

-- 也可以使用 CREATE INDEX CONCURRENTLY 并发创建索引，避免锁表
CREATE INDEX csi_usertable
ON dynamodb.usertable
USING CSI (
    userid,
    col_event_time,
    col_product_category,
    col_duration
);

可通过 pg_indexes 视图确认索引已创建：

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'usertable' AND schemaname = 'dynamodb';

执行结果：

 indexname      |                                              indexdef
----------------+-----------------------------------------------------------------------------------------------
 usertable_pkey | CREATE UNIQUE INDEX usertable_pkey ON dynamodb.usertable USING btree (userid)
 csi_usertable  | CREATE INDEX csi_usertable ON dynamodb.usertable USING csi (userid, col_event_time, col_product_category, col_duration)
(2 rows)

开启列存索引参数

执行分析查询前，确保列存索引相关参数已开启：

-- 允许查询使用列存索引
SET polar_csi.enable_query = on;
SHOW polar_csi.enable_query;
-- 实验和分析场景下，可将代价阈值设为 0，更容易触发 CSI
SET polar_csi.cost_threshold = 0;
SHOW polar_csi.cost_threshold;

执行统计查询

统计最近一年内各商品品类的总停留时长（分钟）。先通过 EXPLAIN 查看执行计划，确认查询是否已由 IMCI 加速：

EXPLAIN
SELECT
    col_product_category AS product_category,
    SUM(col_duration)    AS total_duration_minutes
FROM dynamodb.usertable
WHERE col_event_time::timestamptz >= now() - interval '1 year'
GROUP BY product_category
ORDER BY product_category;

            QUERY PLAN
──────────────────────────────────
CSI Executor:
┌─────────────────────────────────┐
│         PROJECTION              │
│   ──────────────────            │
│ __internal_decompress_strin     │
│          g(#0)                  │
│           #1                    │
│                                 │
│         ~0 Rows                 │
└─────────────┬───────────────────┘
              │
┌─────────────────────────────────┐
│         ORDER_BY                │
│   ──────────────────            │
│        usertable                │
│  .col_product_category ASC      │
└─────────────┬───────────────────┘
              │
┌─────────────────────────────────┐
│         PROJECTION              │
│   ──────────────────            │
│ __internal_compress_string_     │
│        uhugeint(#0)             │
└─────────────┬───────────────────┘
              │
              ...

在 EXPLAIN 输出中，如果出现 CSI Executor，表示查询已由列存索引加速。去掉 EXPLAIN 后执行，获得实际统计结果：

SELECT
    col_product_category AS product_category,
    SUM(col_duration)    AS total_duration_minutes
FROM dynamodb.usertable
WHERE col_event_time::timestamptz >= now() - interval '1 year'
GROUP BY product_category
ORDER BY product_category;

以本文的测试数据为例，预期统计结果如下：