POSTGRESQL分区表模式下查询效率测试

测试环境： POSTGRESQL 13 CENTOS 7.6

一、创建测试表

CREATE TABLE sheet_sale_main_test(company_id integer NOT NULL,sheet_id serial,sheet_no text NOT NULL, order_sheet_id integer,order_sheet_date date,sheet_type text,money_inout_flag smallint, branch_id integer, red_flag smallint,red_sheet_id integer, red_sheet_date date,supcust_id integer,shop_id integer,total_amount float8 NOT NULL, is_retail boolean,tax_amount float4,discount_info text,
payway1_id integer,payway1_amount float8,payway2_id integer,payway2_amount float8, now_pay_amount float8 NOT NULL,
now_disc_amount float4 DEFAULT 0 NOT NULL,paid_amount float8 NOT NULL,disc_amount float4 DEFAULT 0 NOT NULL,
maker_id integer, make_time timestamp, happen_time timestamp, approver_id integer,approve_time timestamp, seller_id integer,make_brief text,approve_brief text, submit_time timestamp) partition by range(happen_time);

create table sheet_sale_main_test_20072102 partition of sheet_sale_main_test for values from (‘2020-07-01’) to (‘2021-03-01’);
create table sheet_sale_main_test_21032106 partition of sheet_sale_main_test for values from (‘2021-03-01’) to (‘2021-06-01’);
create table sheet_sale_main_test_21062201 partition of sheet_sale_main_test for values from (‘2021-06-01’) to (‘2022-01-01’);
create table sheet_sale_main_test_22012301 partition of sheet_sale_main_test for values from (‘2022-01-01’) to (‘2023-01-01’);
create table sheet_sale_main_test_23012401 partition of sheet_sale_main_test for values from (‘2023-01-01’) to (‘2024-01-01’);
create table sheet_sale_main_test_24012501 partition of sheet_sale_main_test for values from (‘2024-01-01’) to (‘2025-01-01’);
create table sheet_sale_main_test_25012601 partition of sheet_sale_main_test for values from (‘2025-01-01’) to (‘2026-01-01’);
create table sheet_sale_main_test_26012701 partition of sheet_sale_main_test for values from (‘2026-01-01’) to (‘2027-01-01’);
create table sheet_sale_main_test_27013001 partition of sheet_sale_main_test for values from (‘2027-01-01’) to (‘2030-01-01’);
create table sheet_sale_main_test_30013301 partition of sheet_sale_main_test for values from (‘2030-01-01’) to (‘2033-01-01’);

二、插入测试数据

insert into sheet_sale_main_test
(company_id,sheet_no,sheet_type,money_inout_flag,branch_id, supcust_id ,shop_id, total_amount, now_pay_amount, paid_amount, happen_time, seller_id)
SELECT 10,‘X40505050666’,‘X’, 1, (random()*100)::INT, (random()*3000)::INT, (random()*3000)::INT,(random()*1000)::INT, 0, 0, date(generate_series(‘2021-01-11’::timestamp, ‘2021-01-11’::timestamp + ‘360 days’, ‘1 second’)),(random()*1000)::INT

执行该语句一次插入从2021-1-11开始，每一秒产生一条记录，360天产生3000多万条记录，大约90秒可以完成插入。这样连续插入3年数据，大约1亿多条。

三、查询测试

select * from sheet_sale_main where sheet_id=145667
查询时间需要40秒。

select * from sheet_sale_main where happen_time>=‘2021-1-11’ and happen_time<=‘2021-1-12’ limit 20
查询需要10秒

对sheet_id,happen_time分别增加索引后，以上查询都提升到0.030秒左右。

验证猜想1：分区表查询条件必须指定分区键，否则会很慢（证明是错的）

我之前有个猜想，就是分区表的查询条件必须指定分区键，否则会扫描所有分区，会很慢。分区键是happen_time,.所以要根据sheet_id查询一条记录，为了速度也得指定happen_time。为了证明猜想，就做了以下测试：
select * from sheet_sale_main where sheet_id=145667
select * from sheet_sale_main where sheet_id=145667 and happen_time>=‘2021-1-11’ and happen_time<=‘2021-1-12’
结果发现两者耗费时间相同，也就是说，对于有索引的自增列字段，不指定分区键查询也一样快。

验证猜想2:分区表按某日期列查询最早或最后的记录会扫描所有分区，所以会很慢

对1亿条记录的表执行
select max(happen_time) from sheet_sale_main_test where supcust_id=90
select max(happen_time) from sheet_sale_main_test
执行时间都差不多，大约0.019秒

查找某客户最后一次购买记录

select * from sheet_sale_main_test where supcust_id=1000 order by happen_time desc limit 1
supcust_id并没有加索引 查询在0.037秒内完成
查询某客户
select * from sheet_sale_main_test where supcust_id=900 and total_amount-paid>900 order by happen_time limit 1
查询时间0.038秒

结论：POSTGRESQL不愧是性能怪兽，对大表查询效率非常高