Ua ntej los yog yuav ua li cas lub tswv yim ntawm kev faib tawm tuaj txog
Zaj dab neeg pib ntawm no: Tom qab yuav luag tag nrho cov peev txheej rau kev ua kom zoo dua qhov kev thov, thaum lub sijhawm ntawd, tau qaug zog, cov lus nug tshwm sim - dab tsi ntxiv? Qhov no yog li cas lub tswv yim ntawm partitioning tshwm sim.
Lyrical digression:
Precisely 'thaum ntawd', vim ... Ua tsaug rau koj thiab Habru!
Yog li, ua li cas lwm tus tuaj yeem ua rau cov neeg siv khoom zoo siab, thiab tib lub sijhawm txhim kho koj tus kheej kev txawj ntse?
Kom yooj yim txhua yam kom ntau li ntau tau, ces tsuas muaj ob txoj hauv kev los txhim kho qee yam hauv kev ua haujlwm ntawm cov ntaub ntawv:
1) Kev nthuav dav - peb nce cov peev txheej, hloov kev teeb tsa;
2) Intensive txoj kev - nug optimization
Txij li thaum, kuv rov hais dua, thaum lub sijhawm ntawd nws tsis paub meej tias yuav hloov dab tsi ntxiv hauv kev thov kom nrawm, txoj kev tau xaiv - cov qauv tsim hloov pauv.
Yog li, lo lus nug tseem ceeb tshwm sim: dab tsi thiab peb yuav hloov li cas?
Cov xwm txheej pib
Ua ntej, muaj ERD no (pom hauv ib txoj kev yooj yim yooj yim):
Lub ntsiab nta:
- ntau-rau-ntau txoj kev sib raug zoo
- lub rooj twb muaj peev xwm muab faib key
Original thov:
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' ;
Cov txiaj ntsig ntawm kev ua tiav ntawm kev xeem database:
nqi : 502 997.55
Lub sijhawm ua haujlwm:505 nqi.
Peb pom dab tsi? Ib qho kev thov tsis tu ncua, raws li lub sijhawm hlais.
Cia peb ua qhov kev xav yooj yim tshaj plaws: yog tias muaj ib qho piv txwv ntawm lub sijhawm hlais, nws puas yuav pab tau peb? Yog lawm - partitioning.
Yuav ua li cas rau seem?
Thaum xub thawj siab ib muag, qhov kev xaiv yog pom tseeb - tshaj tawm muab faib ntawm "kev xa khoom" lub rooj siv "SHIPMENT_DATE" tus yuam sij (dhia dhau deb ua ntej - thaum kawg nws muab tawm me ntsis yuam kev hauv kev tsim khoom).
Yuav muab faib li cas?
Lo lus nug no kuj tsis nyuaj dhau. Hmoov zoo, hauv PostgreSQL 10, tam sim no muaj tib neeg kev faib cov txheej txheem.
Yog li ntawd,
- Txuag ib pob pov tseg ntawm lub rooj qhov chaw - pg_dump source_table
- Rho tawm cov lus qub - poob table source_table
- Tsim ib lub rooj niam txiv nrog ntau qhov kev faib tawm - tsim table source_table
- Tsim ntu - tsim table source_table, tsim index
- Ntshuam cov pob tseg tsim nyob rau hauv kauj ruam 1 - pg_rov
Scripts rau partitioning
Rau kev yooj yim thiab yooj yim, cov kauj ruam 2,3,4 tau muab tso ua ke rau hauv ib tsab ntawv.
Yog li ntawd,
Txuag ib pob pov tseg ntawm lub rooj qhov chaw
pg_dump postgres --file=/dump/shipment.dmp --format=c --table=shipment --verbose > /dump/shipment.log 2>&1Rho tawm cov lus hauv qab + Tsim cov lus niam txiv nrog ntau qhov kev faib tawm + Tsim cov kev faib tawm
--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
$$;Importing lub pob tseg
pg_restore -d postgres --data-only --format=c --table=shipment --verbose shipment.dmp > /tmp/data_dump/shipment_restore.log 2>&1Tshawb xyuas cov txiaj ntsig muab faib
Peb muaj dab tsi los ntawm qhov tshwm sim? Cov ntawv sau tag nrho ntawm txoj kev npaj ua tiav yog qhov loj thiab tho txawv, yog li nws muaj peev xwm txwv koj tus kheej mus rau tus lej kawg.
Yog
Tus nqi: 502 997.55
Lub sijhawm ua haujlwm: 505 vib nas this.
Tau ua
Tus nqi: 77 872.36
Lub sijhawm ua haujlwm: 79 vib nas this.
Muaj txiaj ntsig zoo. Txo tus nqi thiab lub sijhawm ua haujlwm. Yog li, kev siv cov partitioning muab cov txiaj ntsig xav tau thiab, feem ntau, tsis muaj qhov xav tsis thoob.
Ua kom cov neeg siv khoom zoo siab
Cov txiaj ntsig tau raug nthuav tawm rau cov neeg siv khoom los xyuas. Thiab tom qab tshuaj xyuas nws, lawv tau raug txiav txim siab me ntsis: "Zoo, muab faib rau "cov ntaub ntawv" lub rooj."
Yog lawm, tab sis peb tau tshuaj xyuas cov lus "kev xa khoom" sib txawv kiag li; cov lus "cov ntaub ntawv" tsis muaj "SHIPMENT_DATE" teb.
Tsis muaj teeb meem, ntxiv, hloov. Qhov tseem ceeb tshaj plaws yog tias tus neeg siv khoom txaus siab rau qhov tshwm sim; cov ntsiab lus ntawm kev siv tsis yog qhov tseem ceeb tshwj xeeb.
Partitioning lub ntsiab lus "cov ntaub ntawv"
Feem ntau, tsis muaj teeb meem tshwj xeeb tshwm sim. Txawm li cas los xij, qhov kev faib algorithm, ntawm chav kawm, tau hloov me ntsis.
Ntxiv kab "SHIPMENT_DATA" rau "cov ntaub ntawv" cov lus
psql -h Ρ
ΠΎΡΡ -U Π±Π°Π·Π° -d ΡΠ·Π΅Ρ
=> ALTER TABLE data ADD COLUMN "SHIPMENT_DATE" timestamp without time zone ;Sau cov txiaj ntsig ntawm "SHIPMENT_DATA" kem hauv "cov ntaub ntawv" cov lus nrog cov txiaj ntsig ntawm kab ntawm tib lub npe los ntawm cov lus "kev xa khoom"
-----------------------------
--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
$$;Txuag ib pob pov tseg ntawm "cov ntaub ntawv" lub rooj
pg_dump postgres --file=/dump/data.dmp --format=c --table=data --verbose > /dump/data.log 2>&1</sourceRecreate lub partitioned rooj "cov ntaub ntawv"
--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
$$;
Load lub pob tseg uas tsim nyob rau hauv kauj ruam 3.
pg_restore -h Ρ
ΠΎΡΡ -ΡΠ·Π΅Ρ -d Π±Π°Π·Π° --data-only --format=c --table=data --verbose data.dmp > data_restore.log 2>&1Tsim ib ntu cais rau cov ntaub ntawv qub
---------------------------------------------------
--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
$$;Cov txiaj ntsig kawg:
Yog
Tus nqi: 502 997.55
Lub sijhawm ua haujlwm:505 nqi.
Tau ua
Tus nqi: 68 533.70
Lub sijhawm ua haujlwm: 69 vib nas this
Tsim nyog, tsim nyog heev. Thiab xav tias raws li peb tau tswj kom ntau los yog tsawg dua tus tswv ntawm kev faib cov txheej txheem hauv PostgreSQL 10 - qhov txiaj ntsig zoo heev.
Zaj nkauj teev kev loj hlob
Nws puas tuaj yeem ua tau zoo dua - YOG, KOJ YUAV TSUM!Txhawm rau ua qhov no koj yuav tsum siv MATERIALIZED VIEW.
CREATE 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 ;
Ib zaug ntxiv, peb rov sau dua qhov kev thov:
Cov lus nug siv cov ntaub ntawv pom
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';
Thiab peb tau txais lwm qhov txiaj ntsig:
Yog
Tus nqi: 502 997.55
Lub sijhawm ua haujlwm: 505 vib nas this
Tau ua
Tus nqi: 42 481.16
Lub sijhawm ua haujlwm: 43 vib nas this.
Txawm hais tias, tau kawg, cov txiaj ntsig zoo li no yog kev dag ntxias; cov tswv yim yuav tsum tau kho dua tshiab. Yog li tag nrho lub sijhawm kom tau txais cov ntaub ntawv yuav tsis pab ntau. Tab sis raws li kev sim nws yog qhov nthuav heev.
Qhov tseeb, raws li nws muab tawm, ua tsaug dua thiab Habru!-
Tom qab ntawd
Yog li, cov neeg siv khoom txaus siab. THIAB yuav tsum tau coj kom zoo dua ntawm qhov xwm txheej.
Txoj haujlwm tshiab: Koj tuaj yeem ua dab tsi kom tob thiab nthuav dav?
Thiab tom qab ntawd kuv nco qab - cov txiv neej, peb tsis muaj kev saib xyuas ntawm peb PostgreSQL databases.
Tes ntawm lub siab, tseem muaj qee qhov kev saib xyuas hauv daim ntawv ntawm Cloud Watch ntawm AWS. Tab sis dab tsi yog qhov txiaj ntsig ntawm kev saib xyuas no rau DBA? Feem ntau, xyaum tsis muaj.
Yog tias koj muaj sijhawm los ua ib yam dab tsi muaj txiaj ntsig thiab nthuav rau koj tus kheej, koj tsis tuaj yeem siv lub sijhawm no ...
RAU
Nov yog qhov peb tuaj rau qhov nthuav tshaj plaws:
Kaum Ob Hlis 3, 2018.
Kev txiav txim siab los pib tshawb fawb txog kev muaj peev xwm los saib xyuas qhov ua tau zoo ntawm PostgreSQL cov lus nug.
Tab sis qhov ntawd yog ib zaj dab neeg sib txawv kiag li.
Yuav tsum tau txuas ntxivβ¦
Tau qhov twg los: www.hab.com