自学内容网 自学内容网
自学内容网 > 正文

postgresql表分区及测试

🕗 发布于 2025-01-17 07:03 postgresql  windows  数据库  

本文主要采用list类型实现表分区，并对表分区数据进行查询对比，数据量2000万条以上，速度相差10倍以上。

一、创建表，以substationcode字段为ist类型表分区

CREATE TABLE "public"."d_population_partition" (
  "sfzh" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,
  "hjdz" varchar(200) COLLATE "pg_catalog"."default",
  "hsno" varchar(50) COLLATE "pg_catalog"."default",
  "ldno" varchar(50) COLLATE "pg_catalog"."default",
  "cno" varchar(50) COLLATE "pg_catalog"."default",
  "birthday" timestamp(6),
  "sex" varchar(10) COLLATE "pg_catalog"."default",
  "name" varchar(100) COLLATE "pg_catalog"."default",
  "type" varchar(10) COLLATE "pg_catalog"."default",
  "keypersontype" varchar(10) COLLATE "pg_catalog"."default",
  "gridcode" varchar(32) COLLATE "pg_catalog"."default",
  "gridname" varchar(200) COLLATE "pg_catalog"."default",
  "policestationcode" varchar(32) COLLATE "pg_catalog"."default",
  "policestationname" varchar(200) COLLATE "pg_catalog"."default",
  "substationcode" varchar(32) COLLATE "pg_catalog"."default",
  "substationname" varchar(200) COLLATE "pg_catalog"."default",
  "xjzdz" varchar(255) COLLATE "pg_catalog"."default",
  "mz" varchar(250) COLLATE "pg_catalog"."default",
  "lxdh" varchar(50) COLLATE "pg_catalog"."default",
  "whcd" varchar(50) COLLATE "pg_catalog"."default",
  "zzmm" varchar(50) COLLATE "pg_catalog"."default",
  "isoutflow" varchar(10) COLLATE "pg_catalog"."default",
  "lbsqkqlrrq" varchar(10) COLLATE "pg_catalog"."default",
  "isgeom" int2,
  "id" int8 NOT NULL DEFAULT nextval('seq_increment'::regclass)
) partition by list(substationcode);

二、创建表索引

CREATE INDEX "d_population_partition_sfzh" ON "public"."d_population_partition" USING btree (
  "sfzh" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

三、建立分区子表

/*
3.1获取进行表分区的字段值
range分区、list分区、hash分区、混合分区表
(1)range分区表以范围进行分区，分区边界为[t1,t2)
(2)list分区以指定的分区值将数据存放到对应的分区上
(3)hash分区将数据散列存储在各个分区上，以打散热点数据存放到对应的分区上，然后把满足条件的行存放在该分区中，最常见的是平均的把数据放在不同的分区
(4)混合分区,分区下面也可以建立分区构成级联模式，子分区可以有不同的分区方式，这样的分区成为混合分区。
150402000000,150403000000,150404000000,150421000000,150422000000,150423000000,150424000000,150425000000,150426000000,150428000000,150429000000,150430000000
*/

SELECT array_to_string(array_agg(substationcode),',') from (SELECT substationcode, count(substationcode) FROM d_population GROUP BY substationcode HAVING count(substationcode) > 0) as ll

/*
3.2建立表分区子表
*/
CREATE TABLE dpp_150402000000 PARTITION OF d_population_partition FOR VALUES IN ('150402000000');
CREATE TABLE dpp_150403000000 PARTITION OF d_population_partition FOR VALUES IN ('150403000000');
CREATE TABLE dpp_150404000000 PARTITION OF d_population_partition FOR VALUES IN ('150404000000');
CREATE TABLE dpp_150421000000 PARTITION OF d_population_partition FOR VALUES IN ('150421000000');
CREATE TABLE dpp_150422000000 PARTITION OF d_population_partition FOR VALUES IN ('150422000000');
CREATE TABLE dpp_150423000000 PARTITION OF d_population_partition FOR VALUES IN ('150423000000');
CREATE TABLE dpp_150424000000 PARTITION OF d_population_partition FOR VALUES IN ('15042400000');
CREATE TABLE dpp_150425000000 PARTITION OF d_population_partition FOR VALUES IN ('150425000000');
CREATE TABLE dpp_150426000000 PARTITION OF d_population_partition FOR VALUES IN ('150426000000');
CREATE TABLE dpp_150428000000 PARTITION OF d_population_partition FOR VALUES IN ('150428000000');
CREATE TABLE dpp_150429000000 PARTITION OF d_population_partition FOR VALUES IN ('150429000000');
CREATE TABLE dpp_150430000000 PARTITION OF d_population_partition FOR VALUES IN ('150430000000');
CREATE TABLE dpp_others PARTITION OF d_population_partition DEFAULT;


四、导入数据，分区存储

/*
4.1获取表所有字段名称，返回列名
*/
SELECT array_to_string(array_agg(column_name),',') from information_schema.columns where table_name = 'd_population_partition'


/*
4.2向分区表中写入数据
数据量：20134148条
*/
Insert into d_population_partition(birthday,isgeom,ldno,cno,sex,name,type,keypersontype,gridcode,gridname,sfzh,policestationname,substationcode,substationname,xjzdz,mz,lxdh,whcd,zzmm,isoutflow,lbsqkqlrrq,policestationcode,hjdz,hsno) select birthday,isgeom,ldno,cno,sex,name,type,keypersontype,gridcode,gridname,sfzh,policestationname,substationcode,substationname,xjzdz,mz,lxdh,whcd,zzmm,isoutflow,lbsqkqlrrq,policestationcode,hjdz,hsno from d_population


五、查询耗时对比

/*
分区表查询
时间: 0.205s
*/
SELECT * from d_population_partition where substationcode='150428000000' and name = '关羽';


/*
未分区表查询
时间: 2.608s
*/
-- 时间: 2.608s
SELECT * from d_population where name = '关羽' and substationcode='150428000000';


原文地址：https://blog.csdn.net/thor027/article/details/145186060

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

相关文章

自学内容网
Copyright © 2015-2025