postgresql 表分区的测试用例_分区数据对比查询测试用例-优快云博客

本文链接：https://blog.youkuaiyun.com/xto/article/details/5403738

这篇博客介绍了如何在 PostgreSQL 中实现数据表分区，通过创建多个子表并定义插入规则，实现根据cpy_id进行分区。同时展示了设置约束排除、数据迁移以及查询性能优化的相关操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

----大数据数据表分区做法
CREATE TABLE bcompany_01(CHECK(cpy_id<=500000) ) INHERITS (bcompany);

CREATE TABLE bcompany_02(CHECK(cpy_id<=1000000 and cpy_id>500000) ) INHERITS (bcompany);

CREATE TABLE bcompany_03(CHECK(cpy_id<=1500000 and cpy_id>1000000) ) INHERITS (bcompany);

CREATE TABLE bcompany_04(CHECK(cpy_id<=2000000 and cpy_id>1500000) ) INHERITS (bcompany);

CREATE OR REPLACE RULE bcompany_01_partition AS
ON INSERT TO bcompany WHERE (cpy_id<=500000)
DO INSTEAD
INSERT INTO bcompany_01 VALUES(NEW.CPY_ID,NEW.CPY_CODE,NEW.CPY_NAME,NEW.CPY_INDUSTRY,NEW.CPY_KIND,
NEW.CPY_MEMO,NEW.CPY_ADDRESS,NEW.CPY_ZIP,NEW.CPY_LINK,NEW.CPY_WEB,NEW.CPY_FAX,NEW.CPY_PHONE,NEW.CPY_EMAIL);

CREATE OR REPLACE RULE bcompany_02_partition AS
ON INSERT TO bcompany WHERE (cpy_id<=1000000 and cpy_id>500000)
DO INSTEAD
INSERT INTO bcompany_02 VALUES(NEW.CPY_ID,NEW.CPY_CODE,NEW.CPY_NAME,NEW.CPY_INDUSTRY,NEW.CPY_KIND,
NEW.CPY_MEMO,NEW.CPY_ADDRESS,NEW.CPY_ZIP,NEW.CPY_LINK,NEW.CPY_WEB,NEW.CPY_FAX,NEW.CPY_PHONE,NEW.CPY_EMAIL);

CREATE OR REPLACE RULE bcompany_03_partition AS
ON INSERT TO bcompany WHERE (cpy_id<=1500000 and cpy_id>1000000)
DO INSTEAD
INSERT INTO bcompany_03 VALUES(NEW.CPY_ID,NEW.CPY_CODE,NEW.CPY_NAME,NEW.CPY_INDUSTRY,NEW.CPY_KIND,
NEW.CPY_MEMO,NEW.CPY_ADDRESS,NEW.CPY_ZIP,NEW.CPY_LINK,NEW.CPY_WEB,NEW.CPY_FAX,NEW.CPY_PHONE,NEW.CPY_EMAIL);

CREATE OR REPLACE RULE bcompany_04_partition AS
ON INSERT TO bcompany WHERE (cpy_id<=2000000 and cpy_id>1500000)
DO INSTEAD
  INSERT INTO bcompany_04 VALUES(NEW.CPY_ID,NEW.CPY_CODE,NEW.CPY_NAME,NEW.CPY_INDUSTRY,NEW.CPY_KIND,
  NEW.CPY_MEMO,NEW.CPY_ADDRESS,NEW.CPY_ZIP,NEW.CPY_LINK,NEW.CPY_WEB,NEW.CPY_FAX,NEW.CPY_PHONE,NEW.CPY_EMAIL);

SET constraint_exclusion = on;

select * from bcompany_01

select * from bcompany_tmp

--copy (select * from bcompany) to bcompany_bak

select * from bcompany_tmp where cpy_id<=500000

insert into bcompany (select * from bcompany_tmp where cpy_id<=1000000 and cpy_id>500000);
insert into bcompany (select * from bcompany_tmp where cpy_id<=1500000 and cpy_id>1000000);
insert into bcompany (select * from bcompany_tmp where cpy_id<=2000000 and cpy_id>1500000);
select * from bcompany_01

SELECT * FROM BCOMPANY WHERE

SELECT COUNT(1) FROM BCOMPANY

SELECT COUNT(1) FROM BCOMPANY_TMP WHERE CPY_ID<=2000000

SELECT * FROM BCOMPANY WHERE CPY_NAME='同维科技公司'

SELECT * FROM BCOMPANY_TMP WHERE CPY_NAME ='同维科技公司'

explain SELECT * FROM BCOMPANY WHERE CPY_ID<40000

SELECT * FROM BCOMPANY_TMP WHERE CPY_ID=779046

SELECT * FROM BCOMPANY LIMIT 10