PolarDB：分區表使用列存索引

步驟一：環境準備

1. 請確認您的叢集版本與配置是否滿足以下條件：

   • 叢集版本：

     • PostgreSQL 16（核心小版本為2.0.16.8.3.0及以上）

     • PostgreSQL 14（核心小版本為2.0.14.10.20.0及以上）

     說明

     您可在控制台查看核心小版本號碼，也可以通過SHOW polardb_version;語句查看。如未滿足核心小版本要求，請升級核心小版本。

   • 原表必須包含主鍵，且在建立列存索引時需要將主鍵列加入列存索引中。

   • wal_level參數的值需設定為logical，即在預寫式日誌WAL（Write-Ahead Logging）中增加支援邏輯編碼所需的資訊。

     說明

     您可以通過控制台設定wal_level參數。修改該參數後叢集將會重啟，請在修改參數前做好業務安排，謹慎操作。

2. 開啟列存索引功能。

   對於不同的PolarDB PostgreSQL版核心版本，開啟列存索引的方式不同：

   PostgreSQL 16（2.0.16.9.8.0及以上）或PostgreSQL 14（2.0.14.17.35.0及以上）

   目前的版本下的PolarDB PostgreSQL版叢集，支援兩種開啟方式，具體差異如下，請按需選擇：

   對比項	【推薦】添加列存索引唯讀節點	直接使用預先安裝的列存索引外掛程式
   操作方式	通過控制台實現可視化操作，手動添加列存索引節點。	無需任何操作，即可直接使用。
   資源分派	列存引擎獨佔所有資源，能夠充分利用所有記憶體。	列存引擎只能使用25%的記憶體，其餘記憶體則分配給行存引擎使用。
   業務影響	TP（事務）與AP（分析）業務在不同節點上相互隔離，互不影響。	TP（事務）與AP（分析）業務在同一節點，會互相影響。
   費用	需額外收取列存索引唯讀節點的費用，按照普通計算節點收費。	無費用。

   添加列存索引唯讀節點

   您可選擇以下兩種方式中任意一種方式添加列存索引唯讀節點：

   說明

   叢集中應包含一個唯讀節點，即單節點叢集不支援添加列存索引唯讀節點。

   控制台添加

   1. 登入PolarDB控制台，選擇叢集所在地區。您可以按照如下兩種方式中的任意一種進入增删节点向导頁面：

      • 在集群列表頁面，單擊操作欄的增删节点。

        

      • 在目的地組群的基本信息頁面，数据库节点地區，單擊增删节点。

        

   2. 選擇增加列存索引只读节点選項，並單擊确定。

   3. 在叢集變更配置頁面，添加列存索引唯讀節點並支付。

      1. 單擊增加一个列存索引只读节点，選擇節點規格。

      2. 選擇切換時間。

      3. （可選）查看產品服務合約、服務等級協議。

      4. 單擊立即购买。

      

   4. 支付完成後，返回叢集詳情頁等待列存索引唯讀節點添加成功，即節點狀態為运行中。

   購買時添加

   在PolarDB購買頁的节点个数配置項中自行選擇列存索引唯讀節點數量。

   

   PostgreSQL 16（2.0.16.8.3.0～2.0.16.9.8.0）或PostgreSQL 14（2.0.14.10.20.0～2.0.14.17.35.0）

   目前的版本下的PolarDB PostgreSQL版叢集，列存索引作為外掛程式polar_csi部署在資料庫叢集中，在使用之前需要在指定的資料庫中建立外掛程式。

   說明

   • polar_csi外掛程式的範圍是Database層級，如果需要在叢集的多個Database中使用列存索引，需要為每個Database分別建立polar_csi外掛程式。

   • 安裝外掛程式使用的資料庫帳號必須為高許可權帳號。

   您可以選擇以下兩種方式中的任意一種安裝polar_csi外掛程式。

   控制台安裝

   1. 登入PolarDB控制台，在左側導覽列單擊集群列表，選擇叢集所在地區，並單擊目的地組群ID進入叢集詳情頁。

   2. 在左側導覽列選擇配置与管理 > 插件管理，在管理插件頁簽，選中未安装插件。

   3. 在頁面右上方選擇目標資料庫，單擊polar_csi外掛程式操作列安装，在彈出的安装插件對話方塊，選擇目標数据库账号，單擊確定，即將外掛程式安裝到目標資料庫中。

      

   命令列安裝

   串連資料庫叢集，並在具有相應許可權的目標資料庫中執行以下語句，建立polar_csi外掛程式。

   CREATE EXTENSION polar_csi;

步驟二：資料準備

本案例中，會建立一個多級分區表，並類比插入3.2億條資料（約16 GB），之後根據分區條件進行統計分析。

測試用分區表結構如下：

• sales：主表。

• sales_2023：按照年份分區。

  • sales_2023_a：按照月份分區，1~6月定義為分區a。

  • sales_2023_b：按照月份分區，7~12月定義為分區b。

• sales_2024：按照年份分區。

  • sales_2024_a：按照月份分區，1~6月定義為分區a。

  • sales_2024_b：按照月份分區，7~12月定義為分區b。

1. 建立名為sales的多級分區表，以時間列sale_date作為分區鍵，定義如下。

   CREATE TABLE sales (
       sale_id serial,
       product_id int NOT NULL,
       sale_date date NOT NULL,
       amount numeric(10,2) NOT NULL,
       primary key(sale_id, sale_date)
   ) PARTITION BY RANGE (sale_date);
   
   CREATE TABLE sales_2023 PARTITION OF sales
       FOR VALUES FROM ('2023-1-1') TO ('2024-1-1')
       PARTITION BY RANGE (sale_date);
   CREATE TABLE sales_2023_a PARTITION OF sales_2023
       FOR VALUES FROM ('2023-1-1') TO ('2023-7-1');
   CREATE TABLE sales_2023_b PARTITION OF sales_2023
       FOR VALUES FROM ('2023-7-1') TO ('2024-1-1');
   
   CREATE TABLE sales_2024 PARTITION OF sales
       FOR VALUES FROM ('2024-1-1') TO ('2025-1-1')
       PARTITION BY RANGE (sale_date);
   CREATE TABLE sales_2024_a PARTITION OF sales_2024
       FOR VALUES FROM ('2024-1-1') TO ('2024-7-1');
   CREATE TABLE sales_2024_b PARTITION OF sales_2024
       FOR VALUES FROM ('2024-7-1') TO ('2025-1-1');

2. 產生資料並寫入到分區表，約16 GB。

   INSERT INTO sales (product_id, sale_date, amount)
   SELECT
     (random()*100)::int AS product_id,
     '2023-01-1'::date + i/3200000*7 AS sale_date,
     (random()*1000)::numeric(10,2) AS amount
   FROM
     generate_series(1, 320000000) i;

3. 為表建立列存索引，將sale_id、product_id、sale_date和amount欄位加入到列存索引。

   CREATE INDEX ON sales USING CSI(sale_id, product_id, sale_date, amount);

步驟三：執行查詢

使用不同執行引擎進行查詢。根據不同的分區條件產生三條查詢語句Q1，Q2和Q3。

• 使用列存索引

  --- 開啟列存索引，設定查詢並行度為4
  SET polar_csi.enable_query to on;
  SET polar_csi.exec_parallel to 4;
  
  --- Q1
  EXPLAIN ANALYZE SELECT sale_date, COUNT(*) FROM sales WHERE sale_date BETWEEN '2023-1-1' and '2023-3-1' AND amount > 100 GROUP BY sale_date;
  --- Q2
  EXPLAIN ANALYZE SELECT sale_date, COUNT(*) FROM sales WHERE sale_date BETWEEN '2023-1-1' and '2023-9-1' AND amount > 100 GROUP BY sale_date;
  --- Q3
  EXPLAIN ANALYZE SELECT sale_date, COUNT(*) FROM sales WHERE sale_date BETWEEN '2023-1-1' and '2024-3-1' AND amount > 100 GROUP BY sale_date;

• 關閉列存索引，使用行存引擎

  --- 關閉列存索引，使用行存引擎，並設定查詢並行度為4
  SET polar_csi.enable_query to off;
  SET max_parallel_workers_per_gather to 4;
  
  --- Q1
  EXPLAIN ANALYZE SELECT sale_date, COUNT(*) FROM sales WHERE sale_date BETWEEN '2023-1-1' and '2023-3-1' AND amount > 100 GROUP BY sale_date;
  --- Q2
  EXPLAIN ANALYZE SELECT sale_date, COUNT(*) FROM sales WHERE sale_date BETWEEN '2023-1-1' and '2023-9-1' AND amount > 100 GROUP BY sale_date;
  --- Q3
  EXPLAIN ANALYZE SELECT sale_date, COUNT(*) FROM sales WHERE sale_date BETWEEN '2023-1-1' and '2024-3-1' AND amount > 100 GROUP BY sale_date;