PostgreSQL分区后查询性能未达标，请求优化方案

原表信息 #

现有表tbl_inventory_detail_old，包含1900万条记录，约500个唯一agent_id，35个唯一product_id，DDL如下：

CREATE TABLE public.tbl_inventory_detail_old (
agent_id int8 NOT NULL,
bucket_id int2 NOT NULL,
product_id int2 NOT NULL,
quantity float8 NOT NULL,
serial varchar(32) NOT NULL,
serial_block_id int8 NULL,
bundle_serial varchar(32) NOT NULL,
transaction_id int8 NULL,
is_serial bool NOT NULL,
created_by varchar(64) NOT NULL,
updated_by varchar(64) NULL,
created_date timestamp NOT NULL,
updated_date timestamp NULL,
is_reserved bool NOT NULL DEFAULT false,
serial_status int4 NULL,
bundle_product_id int2 NULL,
CONSTRAINT pk_tbl_inventory_detail_old PRIMARY KEY (agent_id, bucket_id, product_id, serial, is_reserved, bundle_serial),
CONSTRAINT fk_tbl_inventory_detail_product_old FOREIGN KEY (product_id) REFERENCES public.tbl_product(id),
CONSTRAINT fk_tbl_inventory_detail_serial_block_old FOREIGN KEY (serial_block_id) REFERENCES public.tbl_serial_block(id),
CONSTRAINT fk_tbl_inventory_detail_transaction_old FOREIGN KEY (transaction_id) REFERENCES public.tbl_transaction(id)
);
CREATE INDEX idx_inventory_detail_serial_old ON public.tbl_inventory_detail_old USING btree (serial);

慢查询语句 #

执行以下查询耗时超2秒：

explain (ANALYZE, COSTS, VERBOSE, BUFFERS)
select
    id1_0.serial
from
    tbl_inventory_detail_old id1_0
where
    id1_0.agent_id =115
    and id1_0.product_id =15
    and id1_0.bucket_id =1
    and id1_0.is_reserved =false
    and cast(id1_0.serial as numeric(38,0)) between cast('15115601000701' as numeric(38,0)) and cast('15115601000702' as numeric(38,0));

原表执行计划 #

Gather  (cost=1000.56..129510.07 rows=5736 width=13) (actual time=829.972..831.547 rows=2 loops=1)
  Output: serial
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=9488 read=34133
  I/O Timings: shared/local read=216.310
  ->  Parallel Index Only Scan using pk_tbl_inventory_detail_old on public.tbl_inventory_detail_old id1_0  (cost=0.56..127936.47 rows=2390 width=13) (actual time=552.611..826.319 rows=1 loops=3)
    Output: serial
    Index Cond: ((id1_0.agent_id = 115) AND (id1_0.bucket_id = 1) AND (id1_0.product_id = 15) AND (id1_0.is_reserved = false))
    Filter: (((id1_0.serial)::numeric(38,0) >= '15115601000701'::numeric(38,0)) AND ((id1_0.serial)::numeric(38,0) <= '15115601000702'::numeric(38,0)))
    Rows Removed by Filter: 666432
    Heap Fetches: 999307
    Buffers: shared hit=9488 read=34133
    I/O Timings: shared/local read=216.310
    Worker 0:  actual time=823.606..823.607 rows=0 loops=1
      Buffers: shared hit=2816 read=15830
      I/O Timings: shared/local read=81.756
    Worker 1:  actual time=4.753..825.878 rows=2 loops=1
      Buffers: shared hit=3300 read=10044
      I/O Timings: shared/local read=61.850
Query Identifier: -16000001
Planning:
  Buffers: shared hit=61
Planning Time: 0.256 ms
Execution Time: 831.576 ms

分区表创建及数据迁移 #

创建分区表tbl_inventory_detail并迁移数据后，查询耗时仍约2秒，创建脚本如下：

CREATE TABLE tbl_inventory_detail (
    agent_id int8 NOT NULL,
    bucket_id int2 NOT NULL,
    product_id int2 NOT NULL,
    quantity float8 NOT NULL,
    serial varchar(32) NOT NULL,
    serial_block_id int8 NULL,
    bundle_serial varchar(32) NOT NULL,
    transaction_id int8 NULL,
    is_serial bool NOT NULL,
    created_by varchar(64) NOT NULL,
    updated_by varchar(64) NULL,
    created_date timestamp NOT NULL,
    updated_date timestamp NULL,
    is_reserved bool NOT NULL DEFAULT false,
    serial_status int4 NULL,
    bundle_product_id int2 NULL,
    CONSTRAINT pk_tbl_inventory_detail PRIMARY KEY (agent_id, product_id, bucket_id, is_reserved, serial, bundle_serial),
    CONSTRAINT fk_tbl_inventory_detail_product FOREIGN KEY (product_id) REFERENCES tbl_product(id),
    CONSTRAINT fk_tbl_inventory_detail_serial_block FOREIGN KEY (serial_block_id) REFERENCES tbl_serial_block(id),
    CONSTRAINT fk_tbl_inventory_detail_transaction FOREIGN KEY (transaction_id) REFERENCES tbl_transaction(id)
) PARTITION BY RANGE (agent_id);

CREATE INDEX idx_inventory_detail_serial ON tbl_inventory_detail USING btree (serial);

CREATE TABLE tbl_inventory_detail_partition_1 PARTITION OF tbl_inventory_detail 
FOR VALUES FROM (0) TO (100) PARTITION BY RANGE (product_id);

CREATE TABLE tbl_inventory_detail_partition_2 PARTITION OF tbl_inventory_detail 
FOR VALUES FROM (100) TO (200) PARTITION BY RANGE (product_id);

CREATE TABLE tbl_inventory_detail_partition_3 PARTITION OF tbl_inventory_detail 
FOR VALUES FROM (200) TO (300) PARTITION BY RANGE (product_id);

CREATE TABLE tbl_inventory_detail_partition_4 PARTITION OF tbl_inventory_detail 
FOR VALUES FROM (300) TO (400) PARTITION BY RANGE (product_id);

CREATE TABLE tbl_inventory_detail_partition_5 PARTITION OF tbl_inventory_detail 
FOR VALUES FROM (400) TO (500) PARTITION BY RANGE (product_id);

CREATE TABLE tbl_inventory_detail_partition_6 PARTITION OF tbl_inventory_detail 
FOR VALUES FROM (500) TO (MAXVALUE) PARTITION BY RANGE (product_id);

CREATE TABLE tbl_inventory_detail_partition_1_1 PARTITION OF tbl_inventory_detail_partition_1 FOR VALUES FROM (0) TO (5);
CREATE TABLE tbl_inventory_detail_partition_1_2 PARTITION OF tbl_inventory_detail_partition_1 FOR VALUES FROM (5) TO (10);
CREATE TABLE tbl_inventory_detail_partition_1_3 PARTITION OF tbl_inventory_detail_partition_1 FOR VALUES FROM (10) TO (15);
CREATE TABLE tbl_inventory_detail_partition_1_4 PARTITION OF tbl_inventory_detail_partition_1 FOR VALUES FROM (15) TO (20);
CREATE TABLE tbl_inventory_detail_partition_1_5 PARTITION OF tbl_inventory_detail_partition_1 FOR VALUES FROM (20) TO (25);
CREATE TABLE tbl_inventory_detail_partition_1_6 PARTITION OF tbl_inventory_detail_partition_1 FOR VALUES FROM (25) TO (MAXVALUE);

CREATE TABLE tbl_inventory_detail_partition_2_1 PARTITION OF tbl_inventory_detail_partition_2 FOR VALUES FROM (0) TO (5);
CREATE TABLE tbl_inventory_detail_partition_2_2 PARTITION OF tbl_inventory_detail_partition_2 FOR VALUES FROM (5) TO (10);
CREATE TABLE tbl_inventory_detail_partition_2_3 PARTITION OF tbl_inventory_detail_partition_2 FOR VALUES FROM (10) TO (15);
CREATE TABLE tbl_inventory_detail_partition_2_4 PARTITION OF tbl_inventory_detail_partition_2 FOR VALUES FROM (15) TO (20);
CREATE TABLE tbl_inventory_detail_partition_2_5 PARTITION OF tbl_inventory_detail_partition_2 FOR VALUES FROM (20) TO (25);
CREATE TABLE tbl_inventory_detail_partition_2_6 PARTITION OF tbl_inventory_detail_partition_2 FOR VALUES FROM (25) TO (MAXVALUE);

CREATE TABLE tbl_inventory_detail_partition_3_1 PARTITION OF tbl_inventory_detail_partition_3 FOR VALUES FROM (0) TO (5);
CREATE TABLE tbl_inventory_detail_partition_3_2 PARTITION OF tbl_inventory_detail_partition_3 FOR VALUES FROM (5) TO (10);
CREATE TABLE tbl_inventory_detail_partition_3_3 PARTITION OF tbl_inventory_detail_partition_3 FOR VALUES FROM (10) TO (15);
CREATE TABLE tbl_inventory_detail_partition_3_4 PARTITION OF tbl_inventory_detail_partition_3 FOR VALUES FROM (15) TO (20);
CREATE TABLE tbl_inventory_detail_partition_3_5 PARTITION OF tbl_inventory_detail_partition_3 FOR VALUES FROM (20) TO (25);
CREATE TABLE tbl_inventory_detail_partition_3_6 PARTITION OF tbl_inventory_detail_partition_3 FOR VALUES FROM (25) TO (MAXVALUE);

CREATE TABLE tbl_inventory_detail_partition_4_1 PARTITION OF tbl_inventory_detail_partition_4 FOR VALUES FROM (0) TO (5);
CREATE TABLE tbl_inventory_detail_partition_4_2 PARTITION OF tbl_inventory_detail_partition_4 FOR VALUES FROM (5) TO (10);
CREATE TABLE tbl_inventory_detail_partition_4_3 PARTITION OF tbl_inventory_detail_partition_4 FOR VALUES FROM (10) TO (15);
CREATE TABLE tbl_inventory_detail_partition_4_4 PARTITION OF tbl_inventory_detail_partition_4 FOR VALUES FROM (15) TO (20);
CREATE TABLE tbl_inventory_detail_partition_4_5 PARTITION OF tbl_inventory_detail_partition_4 FOR VALUES FROM (20) TO (25);
CREATE TABLE tbl_inventory_detail_partition_4_6 PARTITION OF tbl_inventory_detail_partition_4 FOR VALUES FROM (25) TO (MAXVALUE);

CREATE TABLE tbl_inventory_detail_partition_5_1 PARTITION OF tbl_inventory_detail_partition_5 FOR VALUES FROM (0) TO (5);
CREATE TABLE tbl_inventory_detail_partition_5_2 PARTITION OF tbl_inventory_detail_partition_5 FOR VALUES FROM (5) TO (10);
CREATE TABLE tbl_inventory_detail_partition_5_3 PARTITION OF tbl_inventory_detail_partition_5 FOR VALUES FROM (10) TO (15);
CREATE TABLE tbl_inventory_detail_partition_5_4 PARTITION OF tbl_inventory_detail_partition_5 FOR VALUES FROM (15) TO (20);
CREATE TABLE tbl_inventory_detail_partition_5_5 PARTITION OF tbl_inventory_detail_partition_5 FOR VALUES FROM (20) TO (25);
CREATE TABLE tbl_inventory_detail_partition_5_6 PARTITION OF tbl_inventory_detail_partition_5 FOR VALUES FROM (25) TO (MAXVALUE);

CREATE TABLE tbl_inventory_detail_partition_6_1 PARTITION OF tbl_inventory_detail_partition_6 FOR VALUES FROM (0) TO (5);
CREATE TABLE tbl_inventory_detail_partition_6_2 PARTITION OF tbl_inventory_detail_partition_6 FOR VALUES FROM (5) TO (10);
CREATE TABLE tbl_inventory_detail_partition_6_3 PARTITION OF tbl_inventory_detail_partition_6 FOR VALUES FROM (10) TO (15);
CREATE TABLE tbl_inventory_detail_partition_6_4 PARTITION OF tbl_inventory_detail_partition_6 FOR VALUES FROM (15) TO (20);
CREATE TABLE tbl_inventory_detail_partition_6_5 PARTITION OF tbl_inventory_detail_partition_6 FOR VALUES FROM (20) TO (25);
CREATE TABLE tbl_inventory_detail_partition_6_6 PARTITION OF tbl_inventory_detail_partition_6 FOR VALUES FROM (25) TO (MAXVALUE);

INSERT INTO tbl_inventory_detail
(agent_id, bucket_id, product_id, quantity, serial, serial_block_id, bundle_serial, transaction_id, is_serial, created_by, updated_by, created_date, updated_date, is_reserved, serial_status, bundle_product_id)
select agent_id, bucket_id, product_id, quantity, serial, serial_block_id, bundle_serial, transaction_id, is_serial, created_by, updated_by, created_date, updated_date, is_reserved, serial_status, bundle_product_id
from tbl_inventory_detail_old;

分区表执行计划 #

Gather  (cost=1000.00..67709.65 rows=9996 width=15) (actual time=0.519..619.900 rows=2 loops=1)
  Output: id1_0.serial
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=34471 dirtied=3
  ->  Parallel Seq Scan on public.tbl_inventory_detail_partition_2_4 id1_0  (cost=0.00..65710.05 rows=4165 width=15) (actual time=409.305..615.264 rows=1 loops=3)
    Output: id1_0.serial
    Filter: ((NOT id1_0.is_reserved) AND (id1_0.agent_id = 115) AND (id1_0.product_id = 15) AND (id1_0.bucket_id = 1) AND ((id1_0.serial)::numeric(38,0) >= '15115601000701'::numeric(38,0)) AND ((id1_0.serial)::numeric(38,0) <= '15115601000702'::numeric(38,0)))
    Rows Removed by Filter: 666432
    Buffers: shared hit=34471 dirtied=3
    Worker 0:  actual time=613.239..613.240 rows=0 loops=1
      Buffers: shared hit=8759
    Worker 1:  actual time=614.567..614.568 rows=1 loops=1
      Buffers: shared hit=16900 dirtied=1
Query Identifier: -16000000
Planning Time: 0.181 ms
Execution Time: 619.924 ms

优化方案 #

1. 移除serial的类型转换 #

当前查询中对serial的类型转换（cast(serial as numeric)）导致无法使用serial字段的索引，且每条记录都需要执行转换操作，严重影响性能。如果serial字段的值都是固定长度的数字字符串（如示例中的14位），可以直接使用字符串范围查询，因为数字字符串的字典序与数值序一致：

select
    id1_0.serial
from
    tbl_inventory_detail id1_0
where
    id1_0.agent_id =115
    and id1_0.product_id =15
    and id1_0.bucket_id =1
    and id1_0.is_reserved =false
    and id1_0.serial between '15115601000701' and '15115601000702';

若存在长度不统一的情况，需先将serial补全前导零至固定长度，再执行字符串范围查询。

2. 创建针对性复合索引 #

针对查询的过滤条件，创建包含所有等值条件+范围条件的复合索引，且将选择性高的字段放在前面（agent_id > product_id > bucket_id > is_reserved > serial），同时该索引为覆盖索引（仅包含查询所需字段）：

CREATE INDEX idx_inventory_detail_query ON tbl_inventory_detail (agent_id, product_id, bucket_id, is_reserved, serial);

该索引可以让数据库直接通过索引扫描获取结果，无需回表，大幅降低I/O开销。

3. 更新分区表统计信息 #

数据迁移后，分区表的统计信息可能不准确，导致优化器选择了效率更低的顺序扫描。执行以下命令更新统计信息：

ANALYZE tbl_inventory_detail;

4. 调整分区策略（可选） #

当前分区策略按agent_id范围分区后再按product_id范围分区，虽然能缩小扫描范围，但product_id仅有35个唯一值，按范围分区的