Ro-ràdh no mar a thàinig am beachd air roinneadh
Tha an sgeulachd a’ tòiseachadh an seo: Às deidh cha mhòr a h-uile goireas airson an iarrtas a mheudachadh, aig an àm sin, a bhith sgìth, dh ’èirich a’ cheist - dè an ath rud? Seo mar a dh’èirich am beachd air sgaradh.
Eadar-dhealachadh liriceach:
Precisely ‘at that moment’, oir ... mòran taing agus Habru!
Mar sin, ciamar eile as urrainn dhut an neach-ceannach a dhèanamh toilichte, agus aig an aon àm na sgilean agad fhèin a leasachadh?
Gus a h-uile dad a dhèanamh nas sìmplidhe cho mòr 'sa ghabhas, chan eil ann ach dà dhòigh air rudeigin a leasachadh gu mòr ann an coileanadh an stòr-dàta:
1) Slighe farsaing - bidh sinn a 'meudachadh ghoireasan, ag atharrachadh an rèiteachadh;
2) slighe dian - optimization ceist
Leis, tha mi ag ràdh a-rithist, aig an àm sin cha robh e soilleir tuilleadh dè eile a dh’ atharraicheadh san iarrtas airson astar, chaidh an t-slighe a thaghadh - atharraich dealbhadh bùird.
Mar sin, tha a 'phrìomh cheist ag èirigh: dè agus ciamar a dh'atharraicheas sinn?
Suidheachaidhean tùsail
An toiseach, tha an ERD seo (air a shealltainn ann an dòigh a tha air a shìmpleachadh le cumhachan):
Prìomh fheartan:
- dàimhean iomadach ri mòran
- tha iuchair sgaradh comasach aig a’ bhòrd mu thràth
Iarrtas tùsail:
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' ;
Toraidhean cur gu bàs air an stòr-dàta deuchainn:
cosgais : 502 997.55
Ùine cur gu bàs: 505 diogan.
Dè a chì sinn? Iarrtas cunbhalach, stèidhichte air sliseag ùine.
Feuch an dèan sinn a’ bharail loidsigeach as sìmplidh: ma tha sampall de shleamhnag ùine ann, an cuidich e sinn? Tha sin ceart - sgaradh.
Dè a roinn?
Aig a’ chiad sealladh, tha an roghainn follaiseach - sgaradh dearbhach den chlàr “luchdachadh” a ’cleachdadh an iuchair“ SHIPMENT_DATE ”(leum ro fhada air adhart - aig a 'cheann thall thionndaidh e a-mach beagan ceàrr ann an riochdachadh).
Ciamar a sgaradh?
Chan eil a’ cheist seo ro dhoirbh cuideachd. Gu fortanach, ann am PostgreSQL 10, tha inneal sgaradh daonna ann a-nis.
Mar sin:
- Sàbhail dump den chlàr stòr - pg_dump source_table
- Sguab às an clàr tùsail - clàr tuiteam source_table
- Cruthaich clàr pàrant le sgaradh raon - cruthaich clàr source_table
- Cruthaich earrannan - cruthaich clàr source_table, cruthaich clàr-amais
- Cuir a-steach an dump a chaidh a chruthachadh ann an ceum 1 - pg_ aisig
Sgriobtaichean airson partitioning
Airson sìmplidheachd agus goireasachd, chaidh ceumannan 2,3,4 a chur còmhla ann an aon sgriobt.
Mar sin:
Sàbhail cnap den chlàr stòr
pg_dump postgres --file=/dump/shipment.dmp --format=c --table=shipment --verbose > /dump/shipment.log 2>&1Sguab às an clàr stòr + Cruthaich clàr pàrant le sgaradh raon + Cruthaich sgaraidhean
--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
$$;A 'toirt a-steach an dump
pg_restore -d postgres --data-only --format=c --table=shipment --verbose shipment.dmp > /tmp/data_dump/shipment_restore.log 2>&1A’ sgrùdadh nan toraidhean roinneadh
Dè a th’ againn mar thoradh air? Tha teacsa iomlan a’ phlana cur gu bàs mòr agus dòrainneach, agus mar sin tha e gu math comasach thu fhèin a chuingealachadh ris na h-àireamhan deireannach.
Bha
Cosgais: 502 997.55
Ùine cur gu bàs: 505 diogan.
Air fàs
Cosgais: 77 872.36
Ùine cur gu bàs: 79 diogan.
Toradh math gu leòr. Lùghdachadh cosgais agus ùine cur gu bàs. Mar sin, tha cleachdadh sgaradh a 'toirt a' bhuaidh ris a bheil dùil agus, san fharsaingeachd, chan eil iongnadh sam bith ann.
Dèan an neach-ceannach toilichte
Chaidh toraidhean an deuchainn a thaisbeanadh don neach-ceannach airson ath-sgrùdadh. Agus às deidh dhaibh ath-sgrùdadh a dhèanamh, fhuair iad co-dhùnadh caran ris nach robh dùil: “Sgoinneil, dealaich an clàr“ dàta ”.
Bha, ach rinn sinn sgrùdadh air clàr “giùlain” gu tur eadar-dhealaichte; chan eil an raon “SHIPMENT_DATE” aig a’ chlàr “dàta”.
Gun duilgheadas, cuir ris, atharraich. Is e am prìomh rud gu bheil an neach-ceannach riaraichte leis an toradh; chan eil mion-fhiosrachadh buileachaidh gu sònraichte cudromach.
A’ sgaradh a’ phrìomh chlàr “dàta”
San fharsaingeachd, cha do dh'èirich duilgheadasan sònraichte. Ged, tha an algairim sgaradh, gu dearbh, air atharrachadh beagan.
A’ cur a’ cholbh “SHIPMENT_DATA” ris a’ chlàr “dàta”.
psql -h хост -U база -d юзер
=> ALTER TABLE data ADD COLUMN "SHIPMENT_DATE" timestamp without time zone ;Lìon a-steach luachan a’ cholbh “SHIPMENT_DATA” sa chlàr “dàta” le luachan a’ cholbh den aon ainm bhon chlàr “giùlain”
-----------------------------
--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
$$;Sàbhail dump den chlàr “dàta”.
pg_dump postgres --file=/dump/data.dmp --format=c --table=data --verbose > /dump/data.log 2>&1</sourceAth-chruthaich an clàr roinnte “dàta”
--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
$$;
Luchdaich sìos an dump a chaidh a chruthachadh ann an ceum 3.
pg_restore -h хост -юзер -d база --data-only --format=c --table=data --verbose data.dmp > data_restore.log 2>&1Cruthaich earrann air leth airson seann dàta
---------------------------------------------------
--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
$$;Toraidhean deireannach:
Bha
Cosgais: 502 997.55
Ùine cur gu bàs: 505 diogan.
Air fàs
Cosgais: 68 533.70
Ùine cur gu bàs: 69 diogan
Is fhiach, gu math airidh. Agus a’ beachdachadh air an sin air an t-slighe chaidh againn air barrachd no nas lugha a mhaighstireachd air an uidheamachd sgaradh ann am PostgreSQL 10 - toradh sàr-mhath.
Digression digiar
A bheil e comasach a dhèanamh eadhon nas fheàrr - THA, FEUMAIDH SINN!Gus seo a dhèanamh feumaidh tu VIEW MATERIALIZED VIEW.
CRUTHACHADH SEALLADH TORAIDHEAN 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 ;
A-rithist bidh sinn ag ath-sgrìobhadh an iarrtas:
Ceist a’ cleachdadh sealladh tàthaichte
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';
Agus gheibh sinn toradh eile:
Bha
Cosgais: 502 997.55
Ùine cur gu bàs: 505 diogan
Air fàs
Cosgais: 42 481.16
Ùine cur gu bàs: 43 diogan.
Ged, gu dearbh, tha toradh cho gealltanach meallta; feumar beachdan ùrachadh. Mar sin cha chuidich an ùine iomlan airson dàta fhaighinn mòran. Ach mar dheuchainn tha e gu math inntinneach.
Gu dearbh, mar a thionndaidh e a-mach, taing a-rithist agus Habru!-
Post-d gu caraid
Mar sin, tha an neach-ceannach riaraichte. AGUS feum brath a ghabhail air an t-suidheachadh.
Obair ùr: Dè as urrainn dhut a dhèanamh gus a dhoimhneachadh agus a leudachadh?
Agus an uairsin tha cuimhne agam - guys, chan eil sgrùdadh againn air na stòran-dàta PostgreSQL againn.
Làimhe air cridhe, tha beagan sgrùdaidh ann fhathast ann an cruth Cloud Watch air AWS. Ach dè a’ bhuannachd a tha san sgrùdadh seo don DBA? San fharsaingeachd, cha mhòr gin.
Ma tha cothrom agad rudeigin feumail is inntinneach a dhèanamh dhut fhèin, chan urrainn dhut brath a ghabhail air a’ chothrom seo...
AIRSON
Seo mar a thig sinn chun phàirt as inntinniche:
Dùbhlachd 3, 2018.
A’ tighinn gu co-dhùnadh rannsachadh a thòiseachadh air na comasan a tha rim faighinn airson sùil a chumail air coileanadh cheistean PostgreSQL.
Ach ’s e sgeulachd gu tur eadar-dhealaichte a tha sin.
Ri leantainn…
Source: www.habr.com