ืืงืืื ืึธืืขืจ ืืื ืืขืจ ืืขืืึทื ืง ืคืื ืกืขืงืฉืึทื ืืขืงืืืขื
ืื ืืขืฉืืืืข ืืืืื ืืื ืืึธ: ื ืึธื ืึผืืขื ืึทืืข ืื ืจืขืกืืจืกื ืคึฟืึทืจ ืึธืคึผืืืืืืื ื ืื ืืงืฉื, ืืื ืืขืจ ืฆืืื, ืืขื ืขื ืืืกืืขืืึทืืขืจื, ืื ืงืฉืื ืืื ืืืืคืืขืฉืืื ืขื - ืืืึธืก ืืืืึทืืขืจ? ืืึธืก ืืื ืืื ืืขืจ ืืขืืึทื ืง ืคืื ืฆืขืืืืืื ื ืืื ืืืืคืืขืฉืืื ืขื.
ืืืจืืง ืืืืจืขืฉืึทื:
ืืขื ืื 'ืืื ืืขื ืืึธืืขื ื', ืืืืึทื . ื ืืื ืง ืืื ืืืจื!
ืึทืืื, ืืื ืึทื ืืขืจืฉ ืงืขื ืขื ืืืจ ืืึทืื ืืขื ืงืื ื ืฆืืคืจืืื, ืืื ืืื ืืขืจ ืืขืืืืงืขืจ ืฆืืื ืคึฟืึทืจืืขืกืขืจื ืืืื ืืืืืขื ืข ืกืงืืื?
ืฆื ืคืึทืจืคึผืึธืฉืขืืขืจื ืึทืืฅ ืืื ืคืื ืืื ืืขืืืขื, ืขืก ืืขื ืขื ืืืืื ืฆืืืื ืืืขืื ืฆื ืจืึทืืืงืึทืืื ืคึฟืึทืจืืขืกืขืจื ืขืคึผืขืก ืืื ืื ืคืึธืจืฉืืขืืื ื ืคืื ืื ืืึทืืึทืืืืก:
1) ืืจืืื ืืจื - ืืืจ ืคืึทืจืืจืขืกืขืจื ืจืขืกืืจืกื, ืืืืฉื ืื ืงืึทื ืคืืืืขืจืืืฉืึทื;
2) ืืื ืืขื ืกืืืืข ืืจื - ืึธื ืคึฟืจืขื ืึทืคึผืืึทืืึทืืืืฉืึทื
ืืืืืืื, ืืื ืืืจ ืืืืขืจ, ืืื ืืขืืืื ืฉืืื ื ืืฉื ืืขืืืขื ืงืืืจ ืืืืก ื ืื โโืฆื ืืืืฉื ืืื ืืขืจ ืืงืฉื ืฆื ืคืืจืฉื ืขืืขืจื, ืืื ืืขื ืืืืกืืขืงืืืื ืืขืจ ืืืขื - ืืืฉ ืคึผืืึทื ืขื ืืขืจืื ืืขื.
ืึทืืื, ืื ืืืืคึผื ืงืฉืื ืขืจืืืืึทื: ืืืึธืก ืืื ืืื ืืืขื ืืืจ ืืืืฉื?
ืขืจืฉื ืืืืื ืืื ืืขื
ืขืจืฉืืขืจ, ืขืก ืืื ืืขื ERD (ืืขืืืืื ืืื ืึท ืงืึทื ืืืฉืึทื ืึทืื ืกืืืคึผืืึทืคืืื ืืืขื):
ืืืืคึผื ืคึฟืขืึดืงืืืื:
- ืคืืืข-ืฆื-ืคืืืข ืืืฆืืื ืืขื
- ืืขืจ ืืืฉ ืืื ืฉืืื ืึท ืคึผืึธืืขื ืฆืืขื ืฆืขืืืืืื ื ืฉืืืกื
ืึธืจืืืื ืขื ืืงืฉื:
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' ;
ืจืขืืืืืึทืื ืคืื ืืืจืืคืืจืื ื ืืืืฃ ืื ืคึผืจืึธืืข ืืึทืืึทืืืืก:
ืงืึธืกืื : ืงืกื ืืืงืก ืงืกื ืืืงืก
ืืืจืืคืืจืื ื ืฆืืื: 505 ืกืขืงืื ืืขืก.
ืืืึธืก ืืึธื ืืืจ ืืขื? ื ืจืขืืืืขืจ ืืงืฉื, ืืืืืจื ืืืืฃ ืึท ืฆืืื ืจืขืคืื.
ืืึธืืืจ ืืึทืื ืื ืกืืืคึผืืึทืกื ืืึทืืืฉืืงืึทื ืืึทืฉืึธืจืข: ืืืื ืขืก ืืื ืึท ืืืกืืขืจ ืคืื ืึท ืฆืืื ืจืขืคืื, ืืืขื ืขืก ืืขืืคึฟื ืืื ืื? ืึทื ืก ืจืขืื - ืฆืขืืืืืื ื.
ืืืึธืก ืฆื ืืืืื?
ืืื ืขืจืฉืืขืจ ืืืืง, ืื ืืจืืจื ืืื ืงืืึธืจ ืืื ืืขืจ ืืึธื - ืืขืงืืึทืจืึทืืืืืข ืฆืขืืืืืื ื ืคืื ืื "ืืจืึทื ืกืคึผืึธืจื" ืืืฉ ื ืืฆื ืื "SHIPMENT_DATE" ืฉืืืกื (ืฉืคึผืจืื ืืขื ืฆื ืืืืึทื ืคืึธืจืืืก - ืืื ืื ืกืืฃ ืขืก ืืื ืืขืืืขื ืึท ืืืกื ืคืึทืืฉ ืืื ืคึผืจืึธืืืงืฆืืข).
ืืื ืฆื ืฆืขืืืืื?
ืื ืงืฉืื ืืื ืืืื ื ืืฉื ืฆื ืฉืืืขืจ. ืฆืื ืืืืง, ืืื PostgreSQL 10, ืขืก ืืื ืืืฆื ืึท ืืขื ืืฉ ืคึผืึทืจืืืฉืึทื ืื ื ืืขืงืึทื ืืืึทื.
ืึทืืื:
- ืืื ืึท ืืึทืืคึผ ืคืื ืื ืืงืืจ ืืืฉ - pg_dump source_table
- ืืืกืืขืงื ืื ืึธืจืืืื ืขื ืืืฉ - ืคืึทืื ืืืฉ ืืงืืจ_ืืึทืืืข
- ืฉืึทืคึฟื ืึท ืคืึธืืขืจ ืืืฉ ืืื ืงืืื ืคึผืึทืจืืืฉืึทื ืื ื - ืฉืึทืคึฟื ืืืฉ ืืงืืจ_ืืึทืืืข
- ืฉืึทืคึฟื ืกืขืงืฉืึทื ื - ืฉืึทืคึฟื ืืืฉ ืืงืืจ_ืืึทืืืข, ืฉืึทืคึฟื ืืื ืืขืงืก
- ืึทืจืืึทื ืคืืจ ืื ืืึทืืคึผ ืืืฉืืคื ืืื ืฉืจืื 1 - pg_restore
ืกืงืจืืคึผืก ืคึฟืึทืจ ืคึผืึทืจืืืฉืึทื ื
ืคึฟืึทืจ ืคึผืึทืฉืืขืก ืืื ืงืึทื ืืืื ืืึทื ืก, ืกืืขืคึผืก 2,3,4 ืืขื ืขื ืงืึทืืืืื ื ืืื ืืืื ืฉืจืืคื.
ืึทืืื:
ืืื ืึท ืืึทืืคึผ ืคืื ืื ืืงืืจ ืืืฉ
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>&1ืงืึธื ืืจืึธืืืจืื ื ืื ืคึผืึทืจืืืฉืึทื ืื ื ืจืขืืืืืึทืื
ืืืึธืก ืืึธื ืืืจ ืืึธืื ืืื ืึท ืจืขืืืืืึทื? ืืขืจ ืคืื ืืขืงืกื ืคืื ืืขืจ ืืืจืืคืืจืื ื ืคึผืืึทื ืืื ืืจืืืก ืืื ื ืืื ืข, ืึทืืื ืขืก ืืื ืืึทื ืฅ ืืขืืืขื ืฆื ืืึทืืจืขื ืขืฆื ืืื ืฆื ืื ืืขืฆื ื ืืืขืจื.
ืืื ืืขืืืขื
ืคึผืจืืึทื: 502 997.55
ืืืจืืคืืจืื ื ืฆืืื: ืงืกื ืืืงืก ืกืขืงืื ืืขืก.
ืืื ืืขืืืืจื
ืคึผืจืืึทื: 77 872.36
ืืืจืืคืืจืื ื ืฆืืื: ืงืกื ืืืงืก ืกืขืงืื ืืขืก.
ืืึทื ืฅ ืึท ืืื ืจืขืืืืืึทื. ืจืืืืกื ืคึผืจืืึทื ืืื ืืืจืืคืืจืื ื ืฆืืึทื. ืืืื, ืื ื ืืฆื ืคืื ืคึผืึทืจืืืฉืึทื ืื ื ืืื ืื ืืขืจืืืึทืจื ืืืืจืงืื ื ืืื, ืืื ืึทืืืขืืืื, ืงืืื ืกืึทืคึผืจืืืืื.
ืืึทืื ืื ืงืื ื ืฆืืคืจืืื
ืื ืคึผืจืึธืืข ืจืขืืืืืึทืื ืืขื ืขื ืืขืจืืื ืื ืฆื ืืขืจ ืงืื ื ืคึฟืึทืจ ืึธืคึผืฉืึทืฆืื ื. ืืื ื ืึธื ืจืืืืืืื ื ืขืก, ืืื ืืขื ืขื ืืขืืขืื ืึท ืขืคึผืขืก ืืืืืขืจืืื ืืืขืจืืืงื: "ืืจืืืก, ืฆืขืืืืืื ื ืื "ืืึทืื" ืืืฉ."
ืืึธ, ืึธืืขืจ ืืืจ ืืืืึทืืึทื ื ืึท ืืึธืจ ืึทื ืืขืจืฉ "ืืจืึทื ืกืคึผืึธืจื" ืืืฉ; ืื "ืืึทืื" ืืืฉ ืืื ื ืืฉื ืืึธืื ืื "SHIPMENT_DATE" ืคืขืื.
ืงืืื ืคึผืจืึธืืืขื, ืืืืื, ืืืืฉื. ืื ืืืืคึผื ืืึทื ืืื ืึทื ืืขืจ ืงืื ื ืืื ืฆืืคึฟืจืืื ืืื ืืขืจ ืจืขืืืืืึทื; ืื ืืืคึผืืึทืืขื ืืืืฉืึทื ืืขืืึทืืืก ืืขื ืขื ื ืืฉื ืืขืจ ืืืืคึผื ืืืืืืืง.
ืคึผืึทืจืืืฉืึทื ืื ื ืื ืืืืคึผื ืืืฉ "ืืึทืื"
ืืื ืึทืืืขืืืื, ืงืืื ืืึทืืื ืืขืจ ืฉืืืขืจืืงืืืื. ืืึธืืฉ, ืื ืคึผืึทืจืืืฉืึทื ืื ื ืึทืืืขืจืืืึทื, ืคืื ืงืืจืก, ืืื ืืขืืืื ืขืคึผืขืก.
ืึทืืื ื ืื "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
ืืืจืืคืืจืื ื ืฆืืื: 505 ืกืขืงืื ืืขืก.
ืืื ืืขืืืืจื
ืคึผืจืืึทื: 68 533.70
ืืืจืืคืืจืื ื ืฆืืื: ืงืกื ืืืงืก ืกืขืงืื ืืขืก
ืืืขืจื, ืืึทื ืฅ ืืืขืจื. ืืื ืงืึทื ืกืืืขืจืื ื ืึทื ืฆืืืืืขื ืืขื ืืืขื ืืืจ ืืขืจืืื ืฆื ืืขืจ ืึธืืขืจ ืืืืื ืืงืขืจ ืืขื ืื ืคึผืึทืจืืืฉืึทื ืื ื ืืขืงืึทื ืืืึทื ืืื PostgreSQL 10 - ืึท ืืืกืืขืฆืืืื ื ืจืขืืืืืึทื.
ืืืจืืงืึทื ืืืืืจืขืฉืึทื
ืืื ืขืก ืืขืืืขื ืฆื ืืึธื ืืคืืื ืืขืกืขืจ - ืืึธ, ืืืจ ืงืขื ืขื!ืฆื ืืึธื ืืึธืก, ืืืจ ืืึทืจืคึฟื ืฆื ื ืืฆื MATERIALIZED VIEW.
ืฉืึทืคึฟื ืืึทืืขืจืืึทืืืืขื ืืืื ืื ื 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
ืืืจืืคืืจืื ื ืฆืืื: ืงืกื ืืืงืก ืกืขืงืื ืืขืก.
ืืึธืืฉ ืึทืืึท ืคึผืจืึทืืึทืกืื ื ืจืขืืืืืึทื ืืื ืคืึทืจืคืืจืขืจืืฉ, ืื ืืขืืื ืงืขื ืืึทืจืคึฟื ืฆื ืืืื ืืขืจืงืืืืงื. ืึทืืื ืื ืืึทื ืฅ ืฆืืื ืคึฟืึทืจ ืจืืกืืืืื ื ืืึทืื ืืืขื ื ืืฉื ืืขืืคื ืคืื. ืืืขืจ ืืื ืึทื ืขืงืกืคึผืขืจืืืขื ื ืขืก ืืื ืืึทื ืฅ ืืฉืืงืึทืืืข.
ืึทืงืืืึทืืื, ืืื ืขืก ืคืืจืงืขืจื ืืืืก, ืืึทื ืงืขื ืืืืืขืจ ืืื ืืืจื!
ืึทืคืืขืจืืืึธืจื
ืึทืืื, ืืขืจ ืงืื ื ืืื ืฆืืคึฟืจืืื. ืืื ืืึทืจืคึฟื ืฆื ื ืขืืขื ืืืึทืืข ืคืื โโืื ืกืืืืึทืฆืืข.
ื ืื ืึทืจืืขื: ืืืึธืก ืงืขื ืขื ืืืจ ืงืืืขื ืึทืจืืืฃ ืืื ืฆื ืืืคึผืขืจ ืืื ืืงืกืคึผืึทื ื?
ืืื ืืขืืึธืื ืืื ืืขืืขื ืงืขื - ืืืื, ืืืจ ืืึธื ื ืื ืืึธืื ืืึธื ืืืึธืจืื ื ืคืื ืืื ืืืขืจ PostgreSQL ืืึทืืึทืืืืกืื.
ืืึทื ื ืืืืฃ ืืึทืจืฅ, ืขืก ืืื ื ืึธื ืขืืืขืืข ืืึธื ืืืึธืจืื ื ืืื ืื ืคืึธืจืขื ืคืื ืงืืึธืื ืืืึทื ืืืืฃ AWS. ืึธืืขืจ ืืืึธืก ืืื ืื ื ืืฅ ืคืื ืืขื ืืึธื ืืืึธืจืื ื ืคึฟืึทืจ ืื ืืืึท? ืืื ืึทืืืขืืืื, ืึผืืขื ืืึธืจื ืื.
ืืืื ืืืจ ืืึธื ืึท ืืขืืขืื ืืืื ืฆื ืืึธื ืขืคึผืขืก ื ืืฆืืง ืืื ืืฉืืงืึทืืืข ืคึฟืึทืจ ืืื, ืืืจ ืงืขื ื ื ืืฉื ื ืืฆื ืื ืืขืืขืื ืืืื ...
ืืืึธ
ืืึธืก ืืื ืืื ืืืจ ืงืืืขื ืฆื ืื ืืขืจืกื ืืฉืืงืึทืืืข ืืืื:
3 ืืขืฆืขืืืขืจ 2018.
ืืึทืื ืึท ืืึทืฉืืืก ืฆื ืึธื ืืืืื ืคืึธืจืฉืื ื ืืื ืคืึทืจืึทื ืขื ืงืืืคึผืึทืืืืึทืืื ืคึฟืึทืจ ืืึธื ืืืึธืจืื ื ืื ืคืึธืจืฉืืขืืื ื ืคืื PostgreSQL ืงืืืืจืื.
ืึธืืขืจ ืืึธืก ืืื ืืึธืจ ืึทื ืึทื ืืขืจ ืืขืฉืืืืข.
ืืืฉื...
ืืงืืจ: www.habr.com