pg数据库查看表字段是否被索引覆盖

转载

轩辕 2024-09-09 09:52:09

文章标签 pg数据库查看表字段是否被索引覆盖 PG pgsql 分区分表 文章分类 数据仓库大数据

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

PG10前分区实现

前言
一、基本步骤
二、实践

1.创建父表
2.创建子表
3.创建触发器
4.查询

总结

前言

文章介绍了PG10的分区实现，本节介绍PG10之前版本如何实现分区

一、基本步骤

创建父表，所有的分区都从它继承，这个表中没有数据，不要在这个表上定义任何检查和约束,除非你想约束所有的分区,同样在其上定义任何索引和约束也没有任何意义
创建几个子表，每个表都是从父表继承，通常这些表不会增加任何字段，我们把子表称为分区,实际上他们就是普通的pg表
给分区表增加约束，定义每个分区允许的键值
对于每个分区，在关键字段上创建索引
定义一个规则或者触发器，把对父表的数据插入重定向到合适的分区表
确保constraint_exclusion里的配置参数postgresql.conf是打开的，值为partition，打开后，如果查询中的where子句的过滤条件与分区的约束条件匹配，那么这个查询会智能的查询这个分区，而不会查询其它分区

二、实践

1.创建父表

create table test (id integer, name varchar(32));

2.创建子表

create table test_a inherits (test );  
create table test_b inherits (test );  
create table test_c inherits (test );

创建约束：

alter table test_a add constraint con_test_a check (id >=1 and id <=1000);  
 alter table test_b add constraint con_test_b check (id >=1001 and id <=2000); 
 alter table test_c add constraint con_test_c check (id >=2001);

即根据id的区间进行划分，或者在创建表时直接指定：

CREATE TABLE test_a (CHECK ( id>= 1 AND id < =1000 )) INHERITS (test);
CREATE TABLE test_b (CHECK ( id>= 1001 AND id < =2000 )) INHERITS (test);
CREATE TABLE test_c (CHECK ( id>= 2001 )) INHERITS (test);

3.创建触发器

上述子表，在PG层面即是普通的表，所以针对数据插入，并不会自动根据id进行表选择，这部分工具需要通过自定义触发器实现。

函数

CREATE OR REPLACE FUNCTION test_insert_trigger()
RETURNS TRIGGER AS 
$BODY$
BEGIN
    IF ( NEW.id >= 1 AND
         NEW.id<= 1000 ) THEN
        INSERT INTO test_a VALUES (NEW.*);
    ELSIF ( NEW.id >= 1001 AND
    	  NEW.id<= 2000 ) THEN
    	INSERT INTO test_b VALUES (NEW.*);
    ELSE 
		INSERT INTO test_c VALUES (NEW.*);
	END IF;
	 RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql;

触发器

create trigger insert_prud 
before insert on test
for each row execute procedure test_insert_trigger();

表结构如下图

pg数据库查看表字段是否被索引覆盖_分表

4.查询

PG提供参数constraint_exclusion，包含off/on/partition，含义：当PG生产执行计划时是否考虑表上的约束，这个参数有三个选项 “off,on ,partition” ，默认参数为 off, 意思不使用表上的 constraint 来生成计划，如果设置成 on ,则对所有表生效，生成 PLAN 时会考虑表上的 constraint, 建议设置成 partition,只对分区表生效，从而避免扫描分区表所有分区。

设置为partition，在查询，更新时，会根据条件优先匹配分区，从面提升查询性能。

ps.这不需要触发器

示例：

insert into test_a select generate_series(1,1000),'aaa';
insert into test_b select generate_series(1001,2000),'bbb'; 
insert into test_c select generate_series(2001,3000),'ccc'; 

--根据约束定位中test_c
explain select * from test where id>2002;

--根据约束定位中test_c
explain update test set "name"='abc'  where id=2003;

结合小节3的触发器，实现了插入数据时按分片键进入不同表，查询(修改)时根据分享键确定操作哪张表。即实现了分区的功能。