postgresql分区表

#postgresql分区表重要参数配置
constraint_exclusion = partition
enable_partitionwise_aggregate = on #(分区级别的聚合：PostgreSQL 11附带了一个名为enable_partitionwise_aggregate的新选项，可以打开该选项以使查询计划程序将聚合推送到分区级别。 默认情况下，此选项已关闭）

#创建分区表
CREATE TABLE repayments(
“id” int8 NOT NULL DEFAULT nextval(‘repayments_id_seq’::regclass),
“merchant_id” int4,
“batch_no” varchar COLLATE “pg_catalog”.“default” NOT NULL,
“batch_type” varchar COLLATE “pg_catalog”.“default” DEFAULT ‘’::character varying,
“batch_date” varchar COLLATE “pg_catalog”.“default” DEFAULT ‘’::character varying,
“out_card_no” varchar COLLATE “pg_catalog”.“default” DEFAULT ‘’::character varying,
“amount” numeric DEFAULT 0,
“interest_amount” numeric DEFAULT 0,
“in_card_no” varchar COLLATE “pg_catalog”.“default” DEFAULT ‘’::character varying,
“currency” varchar COLLATE “pg_catalog”.“default” DEFAULT ‘’::character varying,
“out_fee_mode” varchar COLLATE “pg_catalog”.“default” DEFAULT ‘’::character varying,
“out_fee_amount” numeric DEFAULT 0,
“in_fee_mode” varchar COLLATE “pg_catalog”.“default” DEFAULT ‘’::character varying,
“in_fee_amount” numeric DEFAULT 0,
“auth_code” varchar COLLATE “pg_catalog”.“default” DEFAULT ‘’::character varying,
“end_flag” varchar COLLATE “pg_catalog”.“default” DEFAULT ‘’::character varying,
“assets_no” varchar COLLATE “pg_catalog”.“default” DEFAULT ‘’::character varying,
“serial_no” varchar COLLATE “pg_catalog”.“default” DEFAULT ‘’::character varying,
“real_amount” numeric DEFAULT 0,
“real_out_fee_amount” numeric DEFAULT 0,
“real_in_fee_amount” numeric DEFAULT 0,
“result” varchar COLLATE “pg_catalog”.“default” DEFAULT ‘’::character varying,
“third_reserved” varchar COLLATE “pg_catalog”.“default” DEFAULT ‘’::character varying,
“reserved” varchar COLLATE “pg_catalog”.“default” DEFAULT ‘’::character varying,
“sequence_id” varchar COLLATE “pg_catalog”.“default” DEFAULT ‘’::character varying,
“status” varchar COLLATE “pg_catalog”.“default” DEFAULT ‘’::character varying,
“notify_status” varchar COLLATE “pg_catalog”.“default” DEFAULT ‘’::character varying,
“applied_at” timestamp(6),
“submitted_at” timestamp(6),
“replied_at” timestamp(6),
“notified_at” timestamp(6),
“created_at” timestamp(6) NOT NULL,
“updated_at” timestamp(6) NOT NULL,
“batch_count” varchar COLLATE “pg_catalog”.“default” DEFAULT ‘’::character varying,
“canceled_at” timestamp(6),
“organization_id” int8,
“risk_amount” numeric(20,6) NOT NULL DEFAULT 0.0,
“freeze_flag” int4 NOT NULL DEFAULT 0,
“frozen_no” varchar(64) COLLATE “pg_catalog”.“default” NOT NULL DEFAULT ‘’::character varying,
“frozen_serial_no” varchar(255) COLLATE “pg_catalog”.“default” NOT NULL DEFAULT ‘’::character varying,
“callback_num” varchar(5) COLLATE “pg_catalog”.“default” NOT NULL DEFAULT ‘0’::character varying,
“penalty_interest_amount” numeric(20,2) DEFAULT 0,
“is_compay” int2 NOT NULL DEFAULT 2,
CONSTRAINT “repayments_pkey” PRIMARY KEY (“id”)
) partition by range (created_at);

COMMENT ON COLUMN “public”.“repayments”.“risk_amount” IS ‘风险准备金金额 默认 0’;
COMMENT ON COLUMN “public”.“repayments”.“freeze_flag” IS ‘1:转出方回款后冻结 默认0’;
COMMENT ON COLUMN “public”.“repayments”.“frozen_no” IS ‘冻结编号’;
COMMENT ON COLUMN “public”.“repayments”.“frozen_serial_no” IS ‘冻结流水号’;
COMMENT ON COLUMN “public”.“repayments”.“callback_num” IS ‘回调次数’;
COMMENT ON COLUMN “public”.“repayments”.“penalty_interest_amount” IS ‘罚息’;
COMMENT ON COLUMN “public”.“repayments”.“is_compay” IS ‘是否为代偿:1.是,2.否’;

create table repayments_2017 partition of repayments (“id” int8 NOT NULL DEFAULT nextval(‘repayments_id_seq’::regclass) primary key,
“merchant_id”,
“batch_no”,
“batch_type”,
“batch_date”,
“out_card_no”,
“amount”,
“interest_amount”,
“in_card_no”,
“currency”,
“out_fee_mode”,
“out_fee_amount”,
“in_fee_mode”,
“in_fee_amount”,
“auth_code”,
“end_flag”,
“assets_no”,
“serial_no”,
“real_amount”,
“real_out_fee_amount”,
“real_in_fee_amount”,
“result”,
“third_reserved”,
“reserved”,
“sequence_id”,
“status”,
“notify_status”,
“applied_at”,
“submitted_at”,
“replied_at”,
“notified_at”,
“created_at”,
“updated_at”,
“batch_count”,
“canceled_at”,
“organization_id”,
“risk_amount”,
“freeze_flag”,
“frozen_no”,
“frozen_serial_no”,
“callback_num”,
“penalty_interest_amount”,
“is_compay”,
CONSTRAINT “repayments_pkey” PRIMARY KEY (“id”)
) for values from (‘2018-01-01 00:00:00’);

create table repayments_chun partition of repayments (“id” int8 NOT NULL DEFAULT nextval(‘repayments_id_seq’::regclass) primary key,
name varchar(50) not null,
CONSTRAINT “repayments_fen2_pkey” PRIMARY KEY (“id”)) for values from (‘2018-01-01 00:00:00’) to (‘2018-04-01 00:00:00’);

#为每一个分区表，在主要的列上创建索引。该索引并不是严格必须创建的，但在大部分场景下，它都非常有用。
CREATE INDEX almart_date_key_2015_12_10 ON almart_2015_12_10 (date_key);

CREATE INDEX almart_date_key_2015_12_11 ON almart_2015_12_11 (date_key);

CREATE INDEX almart_date_key_2015_12_12 ON almart_2015_12_12 (date_key);

CREATE INDEX almart_date_key_2015_12_13 ON almart_2015_12_13 (date_key);