postgresql 主表分区（数据量3千万）解决查询慢速度提升

1.创建主表

CREATE TABLE "public"."measured_data" (
  "id" varchar(40) COLLATE "pg_catalog"."default" NOT NULL,
  "datetime" varchar(255) COLLATE "pg_catalog"."default",
  "clientid" varchar(255) COLLATE "pg_catalog"."default",
  "temperature" varchar(255) COLLATE "pg_catalog"."default",
  "start" varchar(255) COLLATE "pg_catalog"."default",
  "month" varchar(255) COLLATE "pg_catalog"."default",
  "year" varchar(255) COLLATE "pg_catalog"."default",
  "type" varchar(255) COLLATE "pg_catalog"."default",
  "siteid" varchar(255) COLLATE "pg_catalog"."default",
  "screen" varchar(255) COLLATE "pg_catalog"."default",
  "pass" varchar(255) COLLATE "pg_catalog"."default",
  "startoter" varchar(255) COLLATE "pg_catalog"."default",
  "gnssSt" varchar(255) COLLATE "pg_catalog"."default"
)PARTITION BY RANGE (datetime);

2.创建分区表与主表一致，并建索引

create table measured_data_2021() inherits (measured_data);
create table measured_data_2022() inherits (measured_data);
create table measured_data_2023() inherits (measured_data);

create index measured_data_2021_key
on measured_data_2021(clientid);
create index measured_data_2022_key
on measured_data_2022(clientid);
create index measured_data_2023_key
on measured_data_2023(clientid);

3.设置约束

alter table measured_data_2021
add constraint measured_data_2021_check_dept_key
check(datetime <= '1640966460000');


alter table measured_data_2022
add constraint measured_data_2022_check_dept_key
check(datetime >='1640966461000' AND datetime <= '1672502460000');

alter table measured_data_2023
add constraint measured_data_2023_check_dept_key
check(datetime >='1672502461000');

4.创建存储过程

create or replace function  measured_data_partition_trigger()
    returns trigger as $$
begin
    if new.datetime <= '1640966460000'
    then
        insert into measured_data_2021 values (new.*);
    elseif new.datetime >='1640966461000' AND new.datetime <= '1672502460000'
    then
        insert into measured_data_2022 values (new.*);
    elseif new.datetime >='1672502461000'
    then
        insert into measured_data_2023 values (new.*);
    end if;
    return null;
end;
$$
language plpgsql;

6.为主表创建触发器

create trigger insert_measured_data_trigger
before insert on measured_data
for each row execute procedure measured_data_partition_trigger();

7其他

（1）删除触发器

drop trigger tbl_vehicle_statistics_trigger on tbl_vehicle_statistics;

（2）删除存储过程

DROP function auto_insert_tbl_vehicle_statistics();

上面替换自己表名即可，可以解决数据问题。