ááááááºáááºááŒááºáž ááá¯á·ááá¯áẠá¡ááá¯ááºážááœá²ááŒááºážá á áááºáá°ážááẠáááºááá¯á·ááŒá áºáá¬ááááºážá
áá¬ááºáááºážááẠá€áá±áá¬ááœáẠá áááºáááº- áá±á¬ááºážááá¯ááŸá¯á¡á¬áž á¡áá±á¬ááºážáá¯á¶ážááŒá áºá¡á±á¬ááºááŒá¯áá¯ááºááẠá¡áááºážá¡ááŒá áºá¡á¬ážáá¯á¶ážáá®ážáá«ážááẠááá¯á¡áá»áááºááœáẠáá¯ááºáá¯á¶ážááœá¬ážááŒá®ážáá±á¬áẠáá±ážááœááºážáá±á«áºáá¬ááẠ- áá±á¬ááºáá áºáá¯á áá¬áá²á á€ááá¯á·ááŒáá·áº ááá¯ááºážááŒá¬ážááẠá áááºáá°ážáá±á«áºáá¬áááºá
áá®áá»ááºážá
á¬áá¬áž ááœá²ááœá²ááŸá¯
á¡ááá¡áá» 'á¡á²áá®á¡áá»áááºááŸá¬' ááá¯áá±á¬á· á áá»á±ážáá°ážáááºáá«ááẠááŸáá·áº Habru!
áá®áá±á¬á· áá±á¬ááºáááºááœá±ááᯠáá»á±á¬áºááœáŸááºá¡á±á¬áẠáááºááá¯áá¯ááºááá¯ááºááá²á áá áºáá»áááºáááºážááŸá¬áá² ááá·áºááá¯ááºááá¯ááºá¡áááºá¡áá»ááºážááœá±ááᯠááŒáŸáá·áºáááºáá±ážááá¯ááºááá¬ážá
á¡áá¬á¡á¬ážáá¯á¶ážááᯠáááºááá¯ááºááá»áŸ ááá¯ážááŸááºážá¡á±á¬ááºáá¯ááºááá¯á·ááá¯á·áá±á¬áẠáá±áá¬áá±á·á
áºá á
áœááºážáá±á¬ááºáááºááœáẠáá
áºá
á¯á¶áá
áºáá¬ááᯠáááááá¬áᬠááŒáŸáá·áºáááºááẠáááºážáááºážááŸá
áºáá¯áᬠááŸááá«áááºá
1) áá»ááºááŒáá·áºáá±á¬áááºážááŒá±á¬ááºáž - áá»áœááºá¯ááºááá¯á·ááẠá¡áááºážá¡ááŒá
áºáá»á¬ážááᯠááá¯ážááŒáŸáá·áºáᬠááœá²á·á
ááºážááŸá¯áá¯á¶á
á¶ááᯠááŒá±á¬ááºážáá²áá«á
2) á¡ááŸáááºá¡áá¯ááºááŒááºážá
áœá¬áááºážááŒá±á¬ááºáž - áá±ážááœááºážááᯠááá¯ááá¯áá±á¬ááºážááœááºá¡á±á¬ááºááŒá¯áá¯ááºááŒááºážá
áá«áááºááŒá±á¬áááºááá¯áá±á¬á· á¡á²áá®á¡áá»áááºá á¡ááŸáááºááŒáŸáá·áºááá¯á· áá±á¬ááºážááá¯áá»ááºááŸá¬ áá¬ááŒá±á¬ááºážáááŸááºážááááá±á¬á·áá°ážá áááºážááŒá±á¬ááºážááᯠááœá±ážáá²á·áááºá á á¬ážááœá²áá®ááá¯ááºážá¡ááŒá±á¬ááºážá¡áá²á
áá®áá±á¬á· á¡ááááá±ážááœááºážá áá¬áá²á· áááºááá¯ááŒá±á¬ááºážááá²á
áááŠážá¡ááŒá±á¡áá±áá»á¬áž
ááááŠážá
áœá¬á ဠERD ááŸáááẠ(á¡ááŒá±á¡áá±á¡á ááá¯ážááŸááºážáá±á¬áááºážáááºážááŒáá·áº ááŒáá¬ážáááº)
á¡áááá¡ááºá¹áá«áááºáá»á¬áž:
- áá»á¬ážá áœá¬áá±á¬áááºáá¶áá±áž
- ááá¬ážááœáẠááŒá áºááá¯ááºáá»á±ááŸááá±á¬ partition key áá áºáá¯ááŸáááŸáá·áºááŒá®ážááŒá áºáááºá
áá°áááºážáá±á¬ááºážááá¯áá»ááº-
SELECT
p."PARAMETER_ID" as parameter_id,
pc."PC_NAME" AS pc_name,
pc."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w."LASERMARK" AS lasermark,
w."LOTID" AS lotid,
w."REPORTED_VALUE" AS reported_value,
w."LOWER_SPEC_LIMIT" AS lower_spec_limit,
w."UPPER_SPEC_LIMIT" AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SHIPMENT_NAME" AS shipment_name,
s."SHIPMENT_DATE" AS shipment_date,
extract(year from s."SHIPMENT_DATE") AS year,
extract(month from s."SHIPMENT_DATE") as month,
s."REPORT_NAME" AS report_name,
p."SPARAM_NAME" AS SPARAM_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
FROM data w INNER JOIN shipment s ON s."SHIPMENT_ID" = w."SHIPMENT_ID"
INNER JOIN parameters p ON p."PARAMETER_ID" = w."PARAMETER_ID"
INNER JOIN shipment_pc sp ON s."SHIPMENT_ID" = sp."SHIPMENT_ID"
INNER JOIN pc pc ON pc."PC_ID" = sp."PC_ID"
INNER JOIN ( SELECT w2."LASERMARK" , MAX(s2."SHIPMENT_DATE") AS "SHIPMENT_DATE"
FROM shipment s2 INNER JOIN data w2 ON s2."SHIPMENT_ID" = w2."SHIPMENT_ID"
GROUP BY w2."LASERMARK"
) md ON md."SHIPMENT_DATE" = s."SHIPMENT_DATE" AND md."LASERMARK" = w."LASERMARK"
WHERE
s."SHIPMENT_DATE" >= '2018-07-01' AND s."SHIPMENT_DATE" <= '2018-09-30' ;
á
ááºážáááºáá±áá¬áá±á·á
áºááœáẠáá¯ááºáá±á¬ááºááŸá¯ááááºáá»á¬áž-
áá±ážá : 502 997.55
ááœááºáá»ááºáá»áááº: áá
á
áá¹ááá·áº
áá«ááá¯á· áá¬ááá¯ááŒááºáá²á á¡áá»áááºááá¯ááºážá¡ááá¯áẠáá¯á¶ááŸááºáá±á¬ááºážááá¯ááŸá¯áá
áºáá¯á
á¡ááá¯ážááŸááºážáá¯á¶áž áá¯áá¹áááá°ááá»ááºááᯠáá¯ááºááŒáá·áºáá¡á±á¬ááº- á¡áá»áááºá¡ááá¯ááºážá¡á
áá
áºáá¯áááá°áá¬áá
áºáá¯ááŸááá»áŸáẠáááºážááẠáá»áœááºá¯ááºááá¯á·ááᯠáá°áá®áá±ážáá«áááºáá¬ážá ááŸááºááẠ- partitioningá
áááºá¡ááá¯ááºážáá¯ááºáááá²á
ááááá áºáá»ááºááœááºá ááœá±ážáá»ááºááŸá¯ááŸá¬ áááá¬áááºááŸá¬ážááẠ- "SHIPMENT_DATE" áá±á¬á·ááᯠá¡áá¯á¶ážááŒá¯á "ááá¯á·áá±á¬ááºááŒááºáž" ááá¬ážááᯠááá¯ááºážááŒá¬ážááŒááºáž (ááŸá±á·ááᯠáá¯ááºáááºááœá¬ážááẠ- áá±á¬ááºáá¯á¶ážááœáẠáá¯ááºáá¯ááºááŸá¯ááœáẠá¡áááºážááẠááŸá¬ážááœááºážááœá¬ážáá«áááºá).
áááºááᯠpartition áá¯ááºááá²á
áá®áá±ážááœááºážá ááááºááááºáá«áá°ážá áá¶áá±á¬ááºážáá±á¬ááºáá
áœá¬á PostgreSQL 10 ááœááºá ááᯠáá°áá¬áž ááá¯ááºážááŒá¬ážááŒááºáž ááá¹ááá¬ážáá
áºáᯠááŸááá±ááŒá®ááŒá
áºáááºá
áá®áá±á¬á·:
- á¡áááºážá¡ááŒá áºááá¬ážáá¡ááŸáá¯ááºáá¯á¶áá áºáá¯á¶ááá¯ááááºážáááºážáá« - pg_dump source_table
- áá°áááºážááá¬ážááá¯áá»ááºáá« - ááá¬áž source_table ááá¯áá»ááá¯ááºáá«á
- á¡ááœá¬á¡áá±ážááᯠá¡ááá¯ááºážááá¯ááºážááœá²ááŒááºážááŒáá·áº áááááºááá¬ážááᯠáááºáá®ážáá« - ááá¬áž source_table áááºáá®ážáá«á
- ááá¹ááá»á¬áž áááºáá®ážáá« - ááá¬áž source_table ááá¯áááºáá®ážáá«á á¡ááœáŸááºážááá¯áááºáá®ážáá«á
- á¡ááá·áº 1 ááœáẠáááºáá®ážáá¬ážáá±á¬ á¡ááŸáá¯ááºáá¯á¶ážááᯠáááºááœááºážáá« - pg_restore
ááá¯ááºážááŒá¬ážááŒááºážá¡ááœáẠScripts
ááá¯ážááŸááºážááœááºáá°á á±áááºá¡ááœááºá á¡ááá·áº 2,3,4 ááᯠscript áá áºáá¯á¡ááŒá Ạáá±á«ááºážá ááºáá¬ážáááºá
áá®áá±á¬á·:
á¡áááºážá¡ááŒá
áºááá¬ážáá¡ááŸáá¯ááºáá¯á¶áá
áºáá¯á¶ááá¯ááááºážáááºážáá«á
pg_dump postgres --file=/dump/shipment.dmp --format=c --table=shipment --verbose > /dump/shipment.log 2>&1áááºážááŒá áºááá¬ážááᯠáá»ááºáá« + á¡ááá¯ááºážá¡ááŒá¬ážááœá²ááŒááºážááŸáá·áºá¡áá° áááºáááá¬ážááá¯áááºáá®ážáá« + á¡ááá¯ááºážááœá²áá»á¬ážáááºáá®ážáá«á
--create_partition_shipment.sql
do language plpgsql $$
declare
rec_shipment_date RECORD ;
partition_name varchar;
index_name varchar;
current_year varchar ;
current_month varchar ;
begin_year varchar ;
begin_month varchar ;
next_year varchar ;
next_month varchar ;
first_flag boolean ;
i integer ;
begin
RAISE NOTICE 'CREATE TEMPORARY TABLE FOR SHIPMENT_DATE';
CREATE TEMP TABLE tmp_shipment_date as select distinct "SHIPMENT_DATE" from shipment order by "SHIPMENT_DATE" ;
RAISE NOTICE 'DROP TABLE shipment';
drop table shipment cascade ;
CREATE TABLE public.shipment
(
"SHIPMENT_ID" integer NOT NULL DEFAULT nextval('shipment_shipment_id_seq'::regclass),
"SHIPMENT_NAME" character varying(30) COLLATE pg_catalog."default",
"SHIPMENT_DATE" timestamp without time zone,
"REPORT_NAME" character varying(40) COLLATE pg_catalog."default"
)
PARTITION BY RANGE ("SHIPMENT_DATE")
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
RAISE NOTICE 'CREATE PARTITIONS FOR TABLE shipment';
current_year:='0';
current_month:='0';
begin_year := '0' ;
begin_month := '0' ;
next_year := '0' ;
next_month := '0' ;
FOR rec_shipment_date IN SELECT * FROM tmp_shipment_date LOOP
RAISE NOTICE 'SHIPMENT_DATE=%',rec_shipment_date."SHIPMENT_DATE";
current_year := date_part('year' ,rec_shipment_date."SHIPMENT_DATE");
current_month := date_part('month' ,rec_shipment_date."SHIPMENT_DATE") ;
IF to_number(current_month,'99') < 10 THEN
current_month := '0'||current_month ;
END IF ;
--Init borders
IF begin_year = '0' THEN
first_flag := true ; --first time flag
begin_year := current_year ;
begin_month := current_month ;
IF current_month = '12' THEN
next_year := date_part('year' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 year') ;
ELSE
next_year := current_year ;
END IF;
next_month := date_part('month' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 month') ;
END IF;
-- Check current date into borders NOT for First time
IF to_date( current_year||'.'||current_month, 'YYYY.MM') >= to_date( begin_year||'.'||begin_month, 'YYYY.MM') AND
to_date( current_year||'.'||current_month, 'YYYY.MM') < to_date( next_year||'.'||next_month, 'YYYY.MM') AND
NOT first_flag
THEN
CONTINUE ;
ELSE
--NEW borders only for second and after time
begin_year := current_year ;
begin_month := current_month ;
IF current_month = '12' THEN
next_year := date_part('year' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 year') ;
ELSE
next_year := current_year ;
END IF;
next_month := date_part('month' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 month') ;
END IF;
partition_name := 'shipment_shipment_date_'||begin_year||'-'||begin_month||'-01-'|| next_year||'-'||next_month||'-01' ;
EXECUTE format('CREATE TABLE ' || quote_ident(partition_name) || ' PARTITION OF shipment FOR VALUES FROM ( %L ) TO ( %L ) ' , current_year||'-'||current_month||'-01' , next_year||'-'||next_month||'-01' ) ;
index_name := partition_name||'_shipment_id_idx';
RAISE NOTICE 'INDEX NAME =%',index_name;
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_ID") TABLESPACE pg_default ' ) ;
--Drop first time flag
first_flag := false ;
END LOOP;
end
$$;á¡ááŸáá¯ááºááᯠáááºááœááºážááŒááºážá
pg_restore -d postgres --data-only --format=c --table=shipment --verbose shipment.dmp > /tmp/data_dump/shipment_restore.log 2>&1partitioning ááááºáá»á¬ážááá¯á á áºáá±ážááŒááºážá
ááááºá¡áá±áá²á· áá»áœááºá¯ááºááá¯á·ááŸá¬ áá¬ááŸáááá²á á¡áá±á¬ááºá¡áááºáá±á¬áºááŸá¯á¡á á®á¡á ááºá á á¬áá¬ážá¡ááŒáá·áºá¡á á¯á¶ááŸá¬ ááŒá®ážáá¬ážááŒá®áž áá»ááºážá áá¬áá±á¬ááºážáá±á¬ááŒá±á¬áá·áº ááá·áºááá¯ááºááẠáá±á¬ááºáá¯á¶ážáá¶áá«ááºáá»á¬ážááᯠááá·áºáááºáá¬ážááẠááŒá áºááá¯ááºáá»á±ááŸááááºá
áááŒá áº
áá¯ááºáá»á
ááááº: 502 997.55
áá¯ááºáá±á¬ááºáá»áááº- ááá á
áá¹ááá·áºá
ááŒá áºáá¬áááº
áá¯ááºáá»á
ááááº: 77 872.36
áá¯ááºáá±á¬ááºáá»áááº- ááá á
áá¹ááá·áºá
áá±á¬áºáá±á¬áºáá±á¬ááºážáá²á·ááááºáá«á áá¯ááºáá»á ááááºááŸáá·áº á¡áá±á¬ááºá¡áááºáá±á¬áºáá»áááºááᯠáá»áŸá±á¬á·áá»áá±ážáááºá ááá¯á·ááŒá±á¬áá·áº partitioning ááá¯á¡áá¯á¶ážááŒá¯ááŒááºážáááºáá»áŸá±á¬áºááá·áºáá¬ážáá±á¬á¡áá»áá¯ážáááºáá±á¬ááºááŸá¯ááá¯áá±ážáááºááŸáá·áºáá±áá¯áá»á¡á¬ážááŒáá·áºá¡á¶á·ááŒá áá¬áááŸááá«á
áá±á¬ááºáááºááᯠáá»á±á¬áºááœáŸááºá¡á±á¬ááºáá¯ááºáá«á
á ááºážáááºááŸá¯ááááºáá»á¬ážááᯠááŒááºáááºáá¯á¶ážáááºáááºá¡ááœáẠáá±á¬ááºáááºá¡á¬áž áááºááŒáá²á·áá«áááºá áááºážááᯠááŒááºáááºáá¯á¶ážáááºááŒá®ážáá±á¬ááºá áááºážááá¯á·ááẠáá»áŸá±á¬áºááá·áºááá¬ážáá±á¬ á á®áááºáá»ááºáá»áá¶ááááº- "áá±á¬ááºážááŒá®á "áá±áá¬" ááá¬ážááᯠá¡ááá¯ááºážááá¯ááºážááœá²áá«á
áá¯ááºáá«áááºá ááá¯á·áá±á¬áº áá»áœááºá¯ááºááá¯á·ááẠáá¯á¶ážáááœá²ááŒá¬ážááŒá¬ážáá¬ážáá±á¬ "ááá¯á·áá±á¬ááºááŒááºáž" ááá¬ážááᯠá á áºáá±ážáá²á·áááºá "áá±áá¬" ááá¬ážááœáẠ"SHIPMENT_DATE" á¡ááœááºáááŸááá«á
ááŒá¿áá¬áááŸááá«á ááá·áºáá«á ááŒá±á¬ááºážáá«á á¡áááá¡áá»ááºááá±á¬á· áá±á¬ááºáááºá ááááºááᯠáá»á±áááºáá±áá¬ááŒá±á¬áá·áº á¡áá±á¬ááºá¡áááºáá±á¬áºááŸá¯á¡áá±ážá áááºá á¡áá°ážá¡áá±ážáááŒá®ážáá«áá°ážá
áááºáááá¬áž âáá±áá¬â ááᯠááá¯ááºážááŒá¬ážááŒááºáž
áá±áá°áá»á¡á¬ážááŒáá·áº á¡áá°ážá¡áááºá¡áá² áááŸááá«á ááá¯á·áá±á¬áºáááºážá partitioning algorithm ááẠá¡áááºážáááºááŒá±á¬ááºážáá²ááœá¬ážáá«áááºá
âSHIPMENT_DATAâ áá±á¬áºáá¶ááᯠâáá±áá¬â ááá¬ážááœáẠááá·áºááŒááºážá
psql -h Ñ
ПÑÑ -U база -d ÑзеÑ
=> ALTER TABLE data ADD COLUMN "SHIPMENT_DATE" timestamp without time zone ;âááá¯á·áá±á¬ááºááŒááºážâ ááá¬ážá០áá¬áááºáá°áá±á¬áºáá¶áááºááá¯ážáá»á¬ážááŸáá·áºá¡áá° âSHIPMENT_DATAâ áá±á¬áºáá¶á áááºááá¯ážáá»á¬ážááᯠâáá±áá¬â ááá¬ážááœáẠááŒáá·áºáá«
-----------------------------
--update_data.sql
--updating for altered table "data" to values of "shipment_data" from the table "shipment"
--version 1.0
do language plpgsql $$
declare
rec_shipment_data RECORD ;
shipment_date timestamp without time zone ;
row_count integer ;
total_rows integer ;
begin
select count(*) into total_rows from shipment ;
RAISE NOTICE 'Total %',total_rows;
row_count:= 0 ;
FOR rec_shipment_data IN SELECT * FROM shipment LOOP
update data set "SHIPMENT_DATE" = rec_shipment_data."SHIPMENT_DATE" where "SHIPMENT_ID" = rec_shipment_data."SHIPMENT_ID";
row_count:= row_count +1 ;
RAISE NOTICE 'row count = % , from %',row_count,total_rows;
END LOOP;
end
$$;"áá±áá¬" ááá¬ážáá¡ááŸáá¯ááºáá¯á¶áá áºáá¯á¶ááá¯ááááºážáááºážáá«á
pg_dump postgres --file=/dump/data.dmp --format=c --table=data --verbose > /dump/data.log 2>&1</sourceááá¯ááºážááŒá¬ážáá¬ážáá±á¬ ááá¬áž âáá±áá¬â ááᯠááŒááºáááºáááºáá®ážáá«
--create_partition_data.sql
--create partitions for the table "wafer data" by range column "shipment_data" with one month duration
--version 1.0
do language plpgsql $$
declare
rec_shipment_date RECORD ;
partition_name varchar;
index_name varchar;
current_year varchar ;
current_month varchar ;
begin_year varchar ;
begin_month varchar ;
next_year varchar ;
next_month varchar ;
first_flag boolean ;
i integer ;
begin
RAISE NOTICE 'CREATE TEMPORARY TABLE FOR SHIPMENT_DATE';
CREATE TEMP TABLE tmp_shipment_date as select distinct "SHIPMENT_DATE" from shipment order by "SHIPMENT_DATE" ;
RAISE NOTICE 'DROP TABLE data';
drop table data cascade ;
RAISE NOTICE 'CREATE PARTITIONED TABLE data';
CREATE TABLE public.data
(
"RUN_ID" integer,
"LASERMARK" character varying(20) COLLATE pg_catalog."default" NOT NULL,
"LOTID" character varying(80) COLLATE pg_catalog."default",
"SHIPMENT_ID" integer NOT NULL,
"PARAMETER_ID" integer NOT NULL,
"INTERNAL_VALUE" character varying(75) COLLATE pg_catalog."default",
"REPORTED_VALUE" character varying(75) COLLATE pg_catalog."default",
"LOWER_SPEC_LIMIT" numeric,
"UPPER_SPEC_LIMIT" numeric ,
"SHIPMENT_DATE" timestamp without time zone
)
PARTITION BY RANGE ("SHIPMENT_DATE")
WITH (
OIDS = FALSE
)
TABLESPACE pg_default ;
RAISE NOTICE 'CREATE PARTITIONS FOR TABLE data';
current_year:='0';
current_month:='0';
begin_year := '0' ;
begin_month := '0' ;
next_year := '0' ;
next_month := '0' ;
i := 1;
FOR rec_shipment_date IN SELECT * FROM tmp_shipment_date LOOP
RAISE NOTICE 'SHIPMENT_DATE=%',rec_shipment_date."SHIPMENT_DATE";
current_year := date_part('year' ,rec_shipment_date."SHIPMENT_DATE");
current_month := date_part('month' ,rec_shipment_date."SHIPMENT_DATE") ;
--Init borders
IF begin_year = '0' THEN
RAISE NOTICE '***Init borders';
first_flag := true ; --first time flag
begin_year := current_year ;
begin_month := current_month ;
IF current_month = '12' THEN
next_year := date_part('year' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 year') ;
ELSE
next_year := current_year ;
END IF;
next_month := date_part('month' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 month') ;
END IF;
-- RAISE NOTICE 'current_year=% , current_month=% ',current_year,current_month;
-- RAISE NOTICE 'begin_year=% , begin_month=% ',begin_year,begin_month;
-- RAISE NOTICE 'next_year=% , next_month=% ',next_year,next_month;
-- Check current date into borders NOT for First time
RAISE NOTICE 'Current data = %',to_char( to_date( current_year||'.'||current_month, 'YYYY.MM'), 'YYYY.MM');
RAISE NOTICE 'Begin data = %',to_char( to_date( begin_year||'.'||begin_month, 'YYYY.MM'), 'YYYY.MM');
RAISE NOTICE 'Next data = %',to_char( to_date( next_year||'.'||next_month, 'YYYY.MM'), 'YYYY.MM');
IF to_date( current_year||'.'||current_month, 'YYYY.MM') >= to_date( begin_year||'.'||begin_month, 'YYYY.MM') AND
to_date( current_year||'.'||current_month, 'YYYY.MM') < to_date( next_year||'.'||next_month, 'YYYY.MM') AND
NOT first_flag
THEN
RAISE NOTICE '***CONTINUE';
CONTINUE ;
ELSE
--NEW borders only for second and after time
RAISE NOTICE '***NEW BORDERS';
begin_year := current_year ;
begin_month := current_month ;
IF current_month = '12' THEN
next_year := date_part('year' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 year') ;
ELSE
next_year := current_year ;
END IF;
next_month := date_part('month' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 month') ;
END IF;
IF to_number(current_month,'99') < 10 THEN
current_month := '0'||current_month ;
END IF ;
IF to_number(begin_month,'99') < 10 THEN
begin_month := '0'||begin_month ;
END IF ;
IF to_number(next_month,'99') < 10 THEN
next_month := '0'||next_month ;
END IF ;
RAISE NOTICE 'current_year=% , current_month=% ',current_year,current_month;
RAISE NOTICE 'begin_year=% , begin_month=% ',begin_year,begin_month;
RAISE NOTICE 'next_year=% , next_month=% ',next_year,next_month;
partition_name := 'data_'||begin_year||begin_month||'01_'||next_year||next_month||'01' ;
RAISE NOTICE 'PARTITION NUMBER % , TABLE NAME =%',i , partition_name;
EXECUTE format('CREATE TABLE ' || quote_ident(partition_name) || ' PARTITION OF data FOR VALUES FROM ( %L ) TO ( %L ) ' , begin_year||'-'||begin_month||'-01' , next_year||'-'||next_month||'-01' ) ;
index_name := partition_name||'_shipment_id_parameter_id_idx';
RAISE NOTICE 'INDEX NAME =%',index_name;
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_ID", "PARAMETER_ID") TABLESPACE pg_default ' ) ;
index_name := partition_name||'_lasermark_idx';
RAISE NOTICE 'INDEX NAME =%',index_name;
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("LASERMARK" COLLATE pg_catalog."default") TABLESPACE pg_default ' ) ;
index_name := partition_name||'_shipment_id_idx';
RAISE NOTICE 'INDEX NAME =%',index_name;
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_ID") TABLESPACE pg_default ' ) ;
index_name := partition_name||'_parameter_id_idx';
RAISE NOTICE 'INDEX NAME =%',index_name;
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("PARAMETER_ID") TABLESPACE pg_default ' ) ;
index_name := partition_name||'_shipment_date_idx';
RAISE NOTICE 'INDEX NAME =%',index_name;
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_DATE") TABLESPACE pg_default ' ) ;
--Drop first time flag
first_flag := false ;
END LOOP;
end
$$;
á¡ááá·áº 3 ááœááºáááºáá®ážáá¬ážáá±á¬á¡ááŸáá¯ááºááá¯áááºáá«á
pg_restore -h Ñ
ПÑÑ -ÑÐ·ÐµÑ -d база --data-only --format=c --table=data --verbose data.dmp > data_restore.log 2>&1áá±áá¬áá±á¬ááºážá¡ááœáẠáá®ážááŒá¬ážááá¹ááá áºáᯠáááºáá®ážáá«á
---------------------------------------------------
--create_partition_for_old_dates.sql
--create partitions for keeping old dates
--version 1.0
do language plpgsql $$
declare
rec_shipment_date RECORD ;
partition_name varchar;
index_name varchar;
begin
SELECT min("SHIPMENT_DATE") AS min_date INTO rec_shipment_date from data ;
RAISE NOTICE 'Old date is %',rec_shipment_date.min_date ;
partition_name := 'data_old_dates' ;
RAISE NOTICE 'PARTITION NAME IS %',partition_name;
EXECUTE format('CREATE TABLE ' || quote_ident(partition_name) || ' PARTITION OF data FOR VALUES FROM ( %L ) TO ( %L ) ' , '1900-01-01' ,
to_char( rec_shipment_date.min_date,'YYYY')||'-'||to_char(rec_shipment_date.min_date,'MM')||'-01' ) ;
index_name := partition_name||'_shipment_id_parameter_id_idx';
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_ID", "PARAMETER_ID") TABLESPACE pg_default ' ) ;
index_name := partition_name||'_lasermark_idx';
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("LASERMARK" COLLATE pg_catalog."default") TABLESPACE pg_default ' ) ;
index_name := partition_name||'_shipment_id_idx';
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_ID") TABLESPACE pg_default ' ) ;
index_name := partition_name||'_parameter_id_idx';
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("PARAMETER_ID") TABLESPACE pg_default ' ) ;
index_name := partition_name||'_shipment_date_idx';
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_DATE") TABLESPACE pg_default ' ) ;
end
$$;áá±á¬ááºáá¯á¶ážááááºáá»á¬áž-
áááŒá
áº
áá¯ááºáá»á
ááááº: 502 997.55
ááœááºáá»ááºáá»áááº: áá
á
áá¹ááá·áº
ááŒá
áºáá¬áááº
áá¯ááºáá»á
ááááº: 68 533.70
áá¯ááºáá±á¬ááºáá»áááº- 69 á
áá¹áááºá·
ááá¯ááºáááºáááºá ááá¯ááºáááºáááºá ááŒá®ážáá±á¬á· PostgreSQL 10 ááŸá¬ partitioning ááá¹ááá¬ážááᯠá¡áááºážáá²á·á¡áá»á¬áž áá»áœááºážáá»ááºá¡á±á¬áẠá á®áá¶ááá¯ááºáá²á·áá²á·áááºážáá áºáá»áŸá±á¬ááºááŸá¬ ááááºáá±á¬ááºážáá áºáá¯áá«áá²á
áá®áá»ááºážá á¬áá¬áž
áá®áááºááá¯áá±á¬ááºážá¡á±á¬ááºáá¯ááºááá¯á· ááŒá
áºááá¯ááºáá«á·ááá¬áž - áá¯ááºáá²á·á áááºáá¯ááºááá¯ááºáááºáááá¯ááá¯á·ááŒá¯áá¯ááºááẠáááºááẠMATERIALIZED VIEW ááᯠá¡áá¯á¶ážááŒá¯áááºááá¯á¡ááºáááºá
MATERIALIZED ááŒááºááœááºáž LASERMARK_VIEW áááºáá®ážáá«á
CREATE MATERIALIZED VIEW LASERMARK_VIEW
AS
SELECT w."LASERMARK" , MAX(s."SHIPMENT_DATE") AS "SHIPMENT_DATE"
FROM shipment s INNER JOIN data w ON s."SHIPMENT_ID" = w."SHIPMENT_ID"
GROUP BY w."LASERMARK" ;
CREATE INDEX lasermark_vw_shipment_date_ind on lasermark_view USING btree ("SHIPMENT_DATE") TABLESPACE pg_default;
analyze lasermark_view ;
áá±á¬ááºážááá¯áá»ááºááᯠáááºáá¶áá±ážáá¬ážááá¯ááºáá«áááºá
áá¯ááºáá¯á¶ážáá±á«áºáá¬áá±á¬ ááŒááºááœááºážááᯠá¡áá¯á¶ážááŒá¯á áá±ážááŒááºážááŸá¯
SELECT
p."PARAMETER_ID" as parameter_id,
pc."PC_NAME" AS pc_name,
pc."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w."LASERMARK" AS lasermark,
w."LOTID" AS lotid,
w."REPORTED_VALUE" AS reported_value,
w."LOWER_SPEC_LIMIT" AS lower_spec_limit,
w."UPPER_SPEC_LIMIT" AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SHIPMENT_NAME" AS shipment_name,
s."SHIPMENT_DATE" AS shipment_date,
extract(year from s."SHIPMENT_DATE") AS year,
extract(month from s."SHIPMENT_DATE") as month,
s."REPORT_NAME" AS report_name,
p."STC_NAME" AS STC_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
FROM data w INNER JOIN shipment s ON s."SHIPMENT_ID" = w."SHIPMENT_ID"
INNER JOIN parameters p ON p."PARAMETER_ID" = w."PARAMETER_ID"
INNER JOIN shipment_pc sp ON s."SHIPMENT_ID" = sp."SHIPMENT_ID"
INNER JOIN pc pc ON pc."PC_ID" = sp."PC_ID"
INNER JOIN LASERMARK_VIEW md ON md."SHIPMENT_DATE" = s."SHIPMENT_DATE" AND md."LASERMARK" = w."LASERMARK"
WHERE
s."SHIPMENT_DATE" >= '2018-07-01' AND s."SHIPMENT_DATE" <= '2018-09-30';
áá»áœááºá¯ááºááá¯á·ááẠáá±á¬ááºáááºááááºáá
áºáá¯ááᯠáááŸááá«áááºá
áááŒá
áº
áá¯ááºáá»á
ááááº: 502 997.55
ááœááºáá»ááºáá»áááº: 505 á
áá¹ááá·áº
ááŒá
áºáá¬áááº
áá¯ááºáá»á
ááááº: 42 481.16
áá¯ááºáá±á¬ááºáá»áááº- ááá á
áá¹ááá·áºá
á€áá²á·ááá¯á·áá±á¬ á¡áá¬ážá¡áá¬ááŸááá±á¬ááááºááẠááŸáá·áºá á¬ážáá±á¬áºáááºážá á áááºáá°ážáá»á¬ážááᯠááŒááºáááºáááºážáá áºááẠááá¯á¡ááºáá«áááºá ááá¯á·ááŒá±á¬áá·áº áá±áá¬áááºáá¶áááŸáááẠá á¯á á¯áá±á«ááºážá¡áá»áááºááẠáá»á¬ážá áœá¬á¡áá±á¬ááºá¡áá°ááŒá¯áááºááá¯ááºáá«á áá«áá±ááá·áº á ááºážáááºááŸá¯áá áºáá¯á¡áá±áá²á·ááá±á¬á· á¡áá±á¬áºáá±áž á áááºáááºá á¬ážááá¯á·áá±á¬ááºážáá«áááºá
á¡ááŸááºá¡ááá¯ááºážááŒá±á¬ááááºáá±á¬á· áá»á±ážáá°ážáá²áá»á¬á ááŸáá·áº Habru!-
afterword
áá®áá±á¬á· áá±á¬ááºááẠáá»á±áááºáááºá AND ááá¯á¡áẠá¡ááŒá±á¡áá±ááᯠá¡ááœáá·áºáá±á¬ááºážáá°áá«á
á¡áá¯ááºá¡áá áº: ááá¯ááá¯áááºááŸáá¯ááºážááŒá®áž áá»á²á·ááœááºááá¯á· áááºáá¬ááœá±áááá¯ááºááá²á
ááŒá®ážáá±á¬á·áá«ááŸááºááááẠ- áá±á¬ááºá»á¬ážáá±ážááá¯á·á áá«ááá¯á· PostgreSQL áá±áá¬áá±á·á áºáá»á¬ážááá¯á á±á¬áá·áºááŒáá·áºááŒááºážáááŸááá«á
ááŸáá¯á¶ážááœááºážáá«á AWS ááœáẠCloud Watch áá¯á¶á á¶ááŒáá·áº á á±á¬áá·áºááŒáá·áºááŸá¯á¡áá»áá¯á· ááŸááá«áá±ážáááºá ááá¯á·áá±á¬áº DBA á¡ááœáẠá€á á±á¬áá·áºááŒáá·áºááŒááºážá á¡áá»áá¯ážáá»á±ážáá°ážáá¬áž á¡áááºáááºážá áá±áá°áá»á¡á¬ážááŒáá·áº áááºááœá±á·ááœáẠáááŸááá«á
ááá¯áá·áºá¡ááœáẠá¡áá¯á¶ážáááºááŒá®áž á
áááºáááºá
á¬ážá
áᬠáá
áºáá¯áá¯ááᯠáá¯ááºááœáá·áºáááẠáá®á¡ááœáá·áºá¡áá±ážááᯠá¡áá¯á¶ážáá»ááá¯á· áááá«áá°áž...
ááá¯á·
á€áááºááŸá¬ áá»áœááºá¯ááºááá¯á· á
áááºáááºá
á¬ážá
áá¬á¡áá±á¬ááºážáá¯á¶ážá¡ááá¯ááºážááá¯á· áá±á¬ááºááŸááá¬áá¯á¶ááŒá
áºáá«áááº-
áá®áááºáᬠáá ááááá
PostgreSQL queries áá»á¬ážá á áœááºážáá±á¬ááºáááºááᯠá á±á¬áá·áºááŒáá·áºááŒááºážá¡ááœáẠáááŸáááá¯ááºáá±á¬ á áœááºážáááºáá»á¬ážááᯠá áááºáá¯áá±ááááŒá¯ááẠáá¯á¶ážááŒááºáá»ááºáá»ááŒááºážá
áá«áá±ááá·áº á¡á²áá«á áá¯á¶ážáááŒá¬ážáá¬ážáá²á· áá¬ááºáááºážáá«á
áááºáááºâŠ
source: www.habr.com