Iqela elonwabileyo okanye imigca embalwa yeenkumbulo malunga nokwazi ukwahlulahlula kwiPostgreSQL10

Intshayelelo okanye umbono wokwahlulwa wenzeke njani

Ibali liqala apha: Uyakhumbula ukuba yaqala njani yonke loo nto. Yonke into yayiqala kwaye kwakhona. Emva kokuba phantse zonke izixhobo zokuphucula isicelo, ngelo xesha, zaphela, kwavela umbuzo - yintoni elandelayo? Le yindlela eyavela ngayo imbono yokwahlulahlula.

Ukuphuma kwelizwi:
Ngokuchanekileyo 'ngaloo mzuzu', kuba njengoko kwavelayo, kukho oovimba abangasetyenziswanga... Enkosi asmm kunye noHabru!

Ke, unokwenza njani umthengi onwabe, kwaye kwangaxeshanye uphucule ezakho izakhono?

Ukwenza lula yonke into kangangoko kunokwenzeka, ke zimbini kuphela iindlela zokuphucula kakhulu into ekusebenzeni kwesiseko sedatha:
1) Umendo obanzi - sandisa izixhobo, sitshintshe uqwalaselo;
2) Umendo onzulu - ukulungelelaniswa kwemibuzo

Ekubeni, ndiyaphinda, ngelo xesha kwakungasacacanga ukuba yeyiphi enye into enokutshintsha kwisicelo sokukhawulezisa, kwakhethwa indlela - utshintsho loyilo lwetafile.

Ngoko, umbuzo oyintloko uvela: yintoni kwaye siya kutshintsha njani?

Iimeko zokuqala

Okokuqala, kukho le ERD (eboniswe ngendlela eyenziwe lula):

Iinkalo eziphambili:

1. ubudlelwane abaninzi-kuninzi
2. Itheyibhile sele inesitshixo esinokubakho sokwahlula

Isicelo sokuqala:

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' ;

Iziphumo zokwenziwa kuluhlu lovavanyo:
ixabiso : 502 997.55
Ixesha lokwenziwa: 505 imizuzwana.

Sibona ntoni? Isicelo esiqhelekileyo, esisekelwe kwisilayi sexesha.
Masenze eyona ngqikelelo ilula: ukuba kukho isampulu yesilayi sexesha, ingaba iya kusinceda? Ilungile loo nto - ukwahlulahlula.

Yintoni ukuya kwicandelo?

Kuqala nje, ukhetho lucacile - ulwahlulo oluchazayo lwetafile "yokuthunyelwa" usebenzisa iqhosha elithi "SHIPMENT_DATE" (ukutsibela kude kakhulu - ekugqibeleni kwavela impazamo encinci kwimveliso).

Indlela yokwahlula?

Lo mbuzo nawo awunzima kakhulu. Ngethamsanqa, kwi-PostgreSQL 10, ngoku kukho indlela yokwahlulahlula kwabantu.
Ngoko:

1. Gcina ukulahla itafile yemvelaphi - pg_itheyibhile yokulahla umthombo
2. Cima itafile yoqobo - yehlisa itafile source_table
3. Yenza itafile yomzali enoluhlu lwezahlulo - yenza umthombo wetafile_itheyibhile
4. Yenza amacandelo - yenza i-table source_table, yenza isalathisi
5. Thatha ngaphandle indawo yokulahla eyenziwe kwinqanaba loku-1 - pg_buyisela

Izikripthi zokwahlulahlula

Ukwenza lula kunye nokulula, amanyathelo 2,3,4 adityanisiwe abe siscript esinye.

Ngoko:
Gcina ukulahlwa kwetafile yemvelaphi

pg_dump postgres --file=/dump/shipment.dmp --format=c --table=shipment --verbose > /dump/shipment.log 2>&1

Cima itafile yemvelaphi + Yenza itafile yomzali enoluhlu lokwahlulahlula + Yenza izahlulo

--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
$$;

Ukungenisa ngaphandle indawo yokulahla

pg_restore -d postgres --data-only --format=c --table=shipment --verbose  shipment.dmp > /tmp/data_dump/shipment_restore.log 2>&1

Ukujonga iziphumo zokwahlulahlula

Yintoni esinayo ngenxa yoko? Isicatshulwa esipheleleyo sesicwangciso sokwenza sikhulu kwaye siyadika, ngoko kunokwenzeka ukuba unciphise amanani okugqibela.

Yayingu

Iindleko: 502 997.55
Ixesha lokwenziwa: imizuzwana 505.

Uye waba yi

Iindleko: 77 872.36
Ixesha lokwenziwa: imizuzwana 79.

Isiphumo esihle kakhulu. Iindleko ezincitshisiweyo kunye nexesha lokuqhuba. Ngaloo ndlela, ukusetyenziswa kokwahlula kunika umphumo olindelekileyo kwaye, ngokubanzi, akukho nto imangalisayo.

Yenza umthengi onwabe

Iziphumo zovavanyo zinikezelwe kumthengi ukuze azihlole. Kwaye emva kokuyihlolisisa, banikwa isigwebo esingalindelekanga: "Mkhulu, yahlula itafile" yedatha.

Ewe, kodwa sivavanye itafile "yokuthunyelwa" eyahluke ngokupheleleyo; itafile ye "data" ayinayo indawo "SHIPMENT_DATE".

Akukho ngxaki, yongeza, tshintsha. Into ephambili kukuba umthengi wanelisekile sisiphumo; iinkcukacha zokuphunyezwa azibalulekanga ngokukodwa.

Ukwahlula itafile ephambili "data"

Ngokubanzi, akukho bunzima buthile buvelayo. Nangona, i-algorithm yokwahlula, ewe, itshintshile noko.

Ukongeza i-"SHIPMENT_DATA" ikholamu kwitheyibhile "yedatha".

psql -h хост -U база -d юзер
=> ALTER TABLE data ADD COLUMN "SHIPMENT_DATE" timestamp without time zone ;

Gcwalisa amaxabiso ekholamu "SHIPMENT_DATA" kwitheyibhile "yedatha" kunye namaxabiso oluhlu lwegama elifanayo ukusuka kwitheyibhile "yokuthunyelwa"

-----------------------------
--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
$$;

Gcina ukulahlwa kwetafile "yedatha".

pg_dump postgres --file=/dump/data.dmp --format=c --table=data --verbose > /dump/data.log 2>&1</source

Yenza kwakhona itheyibhile eyahluliweyo "data"

--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
$$;

Layisha indawo yokulahla eyenziwe kwinyathelo lesi-3.

pg_restore -h хост -юзер -d база --data-only --format=c --table=data --verbose  data.dmp > data_restore.log 2>&1

Yenza icandelo elahlukileyo ledatha endala

---------------------------------------------------
--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
$$;

Iziphumo zokugqibela:

Yayingu
Iindleko: 502 997.55
Ixesha lokwenziwa: 505 imizuzwana.

Uye waba yi
Iindleko: 68 533.70
Ixesha lokwenziwa: 69 imizuzwana

Ufanelekile, ufanelekile. Kwaye ngokuqwalasela ukuba endleleni sikwazile ukwenza ngaphezulu okanye ngaphantsi kwendlela yokwahlulahlula kwi-PostgreSQL 10-isiphumo esihle kakhulu.

Ukuqhawulwa kwelizwi

Ngaba kunokwenzeka ukwenza ngcono - EWE, UNGAKWAZI!Ukwenza oku kufuneka usebenzise i-MATERIALIZED VIEW.
YENZA UMBONO OBONILEYO 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 ;

Kwakhona siphinda sibhala isicelo:
Umbuzo usebenzisa imbono ebonakalayo

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';

Kwaye sifumana esinye isiphumo:
Yayingu
Iindleko: 502 997.55
Ixesha lokwenziwaImizuzwana engama-505

Uye waba yi
Iindleko: 42 481.16
Ixesha lokwenziwa: imizuzwana 43.

Nangona, ewe, isiphumo esithembisa ngolo hlobo sisinkohliso; izimvo kufuneka zihlaziywe. Ngoko ixesha elipheleleyo lokufumana idatha aliyi kunceda kakhulu. Kodwa njengovavanyo inika umdla kakhulu.

Ngokwenyani, njengoko kwavelayo, enkosi kwakhona asmm kunye noHabru!- Umbuzo ungaphuculwa ngakumbi.

Emva kwegama

Ngoko, umthengi wanelisekile. KUNYE funeka sebenzisa imeko.

Umsebenzi omtsha: Yintoni onokuthi uze nayo ukuze unzulu kwaye wandise?

Kwaye ke ndiyakhumbula- bafana, asinalo iliso kuluhlu lwethu lwedatha yePostgreSQL.

Isandla entliziyweni, kusekho ukubeka iliso ngendlela ye-Cloud Watch kwi-AWS. Kodwa yintoni inzuzo yokubeka iliso kwi-DBA? Ngokubanzi, akukho nanye.

Ukuba unethuba lokwenza into eluncedo kwaye inomdla kuwe, awungekhe ulisebenzise eli thuba...
FOR



Yile ndlela esifika ngayo kweyona nxalenye inomdla:

NgoDisemba 3, 2018.
Ukwenza isigqibo sokuqalisa uphando kwizakhono ezikhoyo zokubeka iliso ekusebenzeni kwemibuzo yePostgreSQL.

Kodwa elo libali elahluke ngokupheleleyo.

Iza kuqhubeka…

umthombo: www.habr.com