Happy Party ຫຼືສອງສາມເສັ້ນຂອງຄວາມຊົງຈໍາກ່ຽວກັບການຮູ້ຈັກການແບ່ງປັນໃນ PostgreSQL10

ຄໍານໍາຫຼືວິທີການແນວຄວາມຄິດຂອງພາກສ່ວນໄດ້ມາກ່ຽວກັບ

ເລື່ອງເລີ່ມຕົ້ນທີ່ນີ້: ເຈົ້າຈື່ໄດ້ບໍວ່າມັນທັງໝົດເລີ່ມຕົ້ນແນວໃດ. ທຸກສິ່ງທຸກຢ່າງແມ່ນຄັ້ງທໍາອິດແລະອີກເທື່ອຫນຶ່ງ. ຫຼັງຈາກເກືອບທັງຫມົດຊັບພະຍາກອນສໍາລັບການເພີ່ມປະສິດທິພາບຄໍາຮ້ອງຂໍ, ໃນເວລານັ້ນ, ໄດ້ຫມົດໄປ, ຄໍາຖາມທີ່ເກີດຂື້ນ - ແມ່ນຫຍັງຕໍ່ໄປ? ນີ້ແມ່ນວິທີທີ່ຄວາມຄິດຂອງການແບ່ງປັນໄດ້ເກີດຂື້ນ.

ເນື້ອຮ້ອງ ທຳນອງ:
ທີ່ຊັດເຈນ 'ໃນເວລານັ້ນ', ເພາະວ່າ ຍ້ອນວ່າມັນຫັນອອກ, ມີສະຫງວນການເພີ່ມປະສິດທິພາບທີ່ບໍ່ໄດ້ໃຊ້... ຂອບ​ໃຈ asmm ແລະ Habru!

ດັ່ງນັ້ນ, ເຮັດແນວໃດອີກແດ່ທີ່ທ່ານສາມາດເຮັດໃຫ້ລູກຄ້າມີຄວາມສຸກ, ແລະໃນເວລາດຽວກັນປັບປຸງທັກສະຂອງຕົນເອງ?

ເພື່ອເຮັດໃຫ້ທຸກສິ່ງທຸກຢ່າງງ່າຍດາຍຫຼາຍເທົ່າທີ່ເປັນໄປໄດ້ຫຼັງຈາກນັ້ນ, ມີພຽງແຕ່ສອງວິທີທີ່ຈະປັບປຸງບາງສິ່ງບາງຢ່າງໃນການປະຕິບັດຂອງຖານຂໍ້ມູນ:
1) ເສັ້ນທາງຢ່າງກວ້າງຂວາງ - ພວກເຮົາເພີ່ມຊັບພະຍາກອນ, ປ່ຽນແປງການຕັ້ງຄ່າ;
2) ເສັ້ນທາງທີ່ເຂັ້ມຂຸ້ນ - ການເພີ່ມປະສິດທິພາບແບບສອບຖາມ

ນັບຕັ້ງແຕ່, ຂ້າພະເຈົ້າເຮັດເລື້ມຄືນ, ໃນເວລານັ້ນມັນບໍ່ຊັດເຈນວ່າມີຫຍັງປ່ຽນແປງອີກໃນຄໍາຮ້ອງຂໍເພື່ອເລັ່ງ, ເສັ້ນທາງໄດ້ຖືກເລືອກ - ການປ່ຽນແປງການອອກແບບຕາຕະລາງ.

ດັ່ງນັ້ນ, ຄໍາຖາມຕົ້ນຕໍທີ່ເກີດຂື້ນ: ແມ່ນຫຍັງແລະພວກເຮົາຈະປ່ຽນແປງແນວໃດ?

ເງື່ອນໄຂເບື້ອງຕົ້ນ

ປະການທໍາອິດ, ມີ ERD ນີ້ (ສະແດງໃຫ້ເຫັນໃນວິທີການງ່າຍດາຍທີ່ມີເງື່ອນໄຂ):

сновныеособенности:

1. ການ​ພົວ​ພັນ​ຫຼາຍ​ຕໍ່​ຫຼາຍ​
2. ຕາຕະລາງມີລະຫັດພາທິຊັນທີ່ມີທ່າແຮງແລ້ວ

ຄຳຮ້ອງຂໍຕົ້ນສະບັບ:

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
ເວລາປະຕິບັດ: 505 ວິນາທີ.

ພວກເຮົາເຫັນຫຍັງ? ຄໍາຮ້ອງຂໍປົກກະຕິ, ອີງໃສ່ສ່ວນທີ່ໃຊ້ເວລາ.
ຂໍໃຫ້ສົມມຸດຕິຖານທີ່ງ່າຍດາຍທີ່ສຸດ: ຖ້າມີຕົວຢ່າງຂອງເວລາ, ມັນຈະຊ່ວຍພວກເຮົາບໍ? ນັ້ນແມ່ນສິດ - ການແບ່ງສ່ວນ.

ພາກສ່ວນໃດ?

ຢູ່ glance ທໍາອິດ, ທາງເລືອກແມ່ນຈະແຈ້ງ - ປະກາດການແບ່ງປັນຕາຕະລາງ "ການຂົນສົ່ງ" ໂດຍໃຊ້ປຸ່ມ "SHIPMENT_DATE" (ໂດດໄປໄກເກີນໄປລ່ວງຫນ້າ - ໃນທີ່ສຸດມັນໄດ້ຫັນອອກເລັກນ້ອຍຜິດພາດໃນການຜະລິດ).

ວິ​ທີ​ການ​ແບ່ງ​ປັນ​?

ຄໍາຖາມນີ້ກໍ່ບໍ່ຍາກເກີນໄປ. ໂຊກດີ, ໃນ PostgreSQL 10, ໃນປັດຈຸບັນມີກົນໄກການແບ່ງສ່ວນຂອງມະນຸດ.
ດັ່ງນັ້ນ:

1. ບັນທຶກການຖິ້ມຂີ້ເຫຍື້ອຂອງຕາຕະລາງແຫຼ່ງ - pg_dump source_table
2. ລຶບຕາຕະລາງຕົ້ນສະບັບ - ວາງຕາຕະລາງ source_table
3. ສ້າງຕາຕະລາງແມ່ດ້ວຍການແບ່ງເຂດ - ສ້າງຕາຕະລາງ source_table
4. ສ້າງ​ພາກ​ສ່ວນ - ສ້າງຕາຕະລາງ source_table, ສ້າງດັດສະນີ
5. ນໍາເຂົ້າຂີ້ເຫຍື້ອທີ່ສ້າງຂຶ້ນໃນຂັ້ນຕອນທີ 1 - pg_restore

ສະຄຣິບສຳລັບການແບ່ງສ່ວນ

ເພື່ອຄວາມງ່າຍດາຍແລະສະດວກສະບາຍ, ຂັ້ນຕອນ 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>&1

ການ​ກວດ​ສອບ​ຜົນ​ໄດ້​ຮັບ​ການ​ແບ່ງ​ປັນ​

ພວກເຮົາມີຜົນໄດ້ຮັບຫຍັງ? ຂໍ້ຄວາມເຕັມຂອງແຜນການປະຕິບັດແມ່ນໃຫຍ່ແລະຫນ້າເບື່ອ, ສະນັ້ນມັນເປັນໄປໄດ້ຂ້ອນຂ້າງທີ່ຈະຈໍາກັດຕົວເອງກັບຕົວເລກສຸດທ້າຍ.

ມັນແມ່ນ

ຄ່າໃຊ້ຈ່າຍ: 502 997.55
ເວລາປະຕິບັດ: ວິນາທີ 505.

ໄດ້ກາຍເປັນ

ຄ່າໃຊ້ຈ່າຍ: 77 872.36
ເວລາປະຕິບັດ: ວິນາທີ 79.

ຂ້ອນຂ້າງເປັນຜົນດີ. ການຫຼຸດຜ່ອນຄ່າໃຊ້ຈ່າຍແລະເວລາປະຕິບັດ. ດັ່ງນັ້ນ, ການນໍາໃຊ້ການແບ່ງປັນໃຫ້ຜົນກະທົບທີ່ຄາດໄວ້ແລະ, ໂດຍທົ່ວໄປ, ບໍ່ມີຄວາມແປກໃຈ.

ເຮັດໃຫ້ລູກຄ້າມີຄວາມສຸກ

ຜົນການທົດສອບໄດ້ຖືກນໍາສະເຫນີໃຫ້ລູກຄ້າທົບທວນຄືນ. ແລະຫຼັງຈາກການທົບທວນຄືນມັນ, ພວກເຂົາໄດ້ຮັບຄໍາຕັດສິນທີ່ບໍ່ຄາດຄິດ: "ດີຫຼາຍ, ແບ່ງປັນຕາຕະລາງ "ຂໍ້ມູນ".

ແມ່ນແລ້ວ, ແຕ່ພວກເຮົາໄດ້ກວດເບິ່ງຕາຕະລາງ "ການຂົນສົ່ງ" ທີ່ແຕກຕ່າງກັນຢ່າງສິ້ນເຊີງ; ຕາຕະລາງ "ຂໍ້ມູນ" ບໍ່ມີຊ່ອງໃສ່ຂໍ້ມູນ "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
$$;

ໂຫຼດ dump ທີ່ສ້າງຂຶ້ນໃນຂັ້ນຕອນ 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
ເວລາປະຕິບັດ: ວິນາທີ 69

ສົມຄວນ, ຂ້ອນຂ້າງສົມຄວນ. ແລະພິຈາລະນາວ່າຕາມວິທີທີ່ພວກເຮົາໄດ້ຈັດການຫຼາຍຫຼືຫນ້ອຍແມ່ບົດກົນໄກການແບ່ງປັນໃນ PostgreSQL 10 - ເປັນຜົນໄດ້ຮັບທີ່ດີເລີດ.

ການຍ່ອຍອາຫານດົນ

ມັນເປັນໄປໄດ້ບໍທີ່ຈະເຮັດໄດ້ດີກວ່າ - ແມ່ນແລ້ວ, ເຈົ້າສາມາດ!ເພື່ອເຮັດສິ່ງນີ້, ທ່ານຈໍາເປັນຕ້ອງໃຊ້ MATERIALIZED VIEW.
ສ້າງ MATTERIALIZED 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
ເວລາປະຕິບັດ: ວິນາທີ 43.

ເຖິງແມ່ນວ່າ, ແນ່ນອນ, ຜົນໄດ້ຮັບທີ່ດີດັ່ງກ່າວເປັນການຫຼອກລວງ; ແນວຄວາມຄິດຕ້ອງໄດ້ຮັບການປັບປຸງໃຫມ່. ດັ່ງນັ້ນເວລາທັງຫມົດທີ່ຈະໄດ້ຮັບຂໍ້ມູນຈະບໍ່ຊ່ວຍຫຼາຍ. ແຕ່ເປັນການທົດລອງມັນເປັນທີ່ຫນ້າສົນໃຈດີ.

ໃນຄວາມເປັນຈິງ, ຍ້ອນວ່າມັນໄດ້ຫັນອອກ, ຂອບໃຈອີກເທື່ອຫນຶ່ງ asmm ແລະ Habru!- ການສອບຖາມສາມາດປັບປຸງຕື່ມອີກ.

Afterword

ດັ່ງນັ້ນ, ລູກຄ້າພໍໃຈ. ແລະ ຕ້ອງການ ໃຊ້ປະໂຫຍດຈາກສະຖານະການ.

ວຽກງານໃໝ່: ເຈົ້າສາມາດລົງເລິກ ແລະ ຂະຫຍາຍອັນໃດແດ່?

ແລະຫຼັງຈາກນັ້ນຂ້າພະເຈົ້າຈື່ - guys, ພວກເຮົາບໍ່ມີການຕິດຕາມຖານຂໍ້ມູນ PostgreSQL ຂອງພວກເຮົາ.

ດ້ວຍຫົວໃຈ, ຍັງມີການຕິດຕາມບາງຢ່າງໃນຮູບແບບຂອງ Cloud Watch ໃນ AWS. ແຕ່ຜົນປະໂຫຍດຂອງການຕິດຕາມນີ້ສໍາລັບ DBA ແມ່ນຫຍັງ? ໂດຍທົ່ວໄປ, ການປະຕິບັດບໍ່ມີ.

ຖ້າເຈົ້າມີໂອກາດເຮັດສິ່ງທີ່ມີປະໂຫຍດ ແລະ ໜ້າສົນໃຈສຳລັບຕົນເອງ, ເຈົ້າບໍ່ສາມາດສວຍໂອກາດນີ້...
ສໍາລັບ



ນີ້ແມ່ນວິທີທີ່ພວກເຮົາມາຮອດພາກສ່ວນທີ່ຫນ້າສົນໃຈທີ່ສຸດ:

ວັນທີ 3 ທັນວາ 2018.
ການຕັດສິນໃຈທີ່ຈະເລີ່ມຕົ້ນການຄົ້ນຄວ້າກ່ຽວກັບຄວາມສາມາດທີ່ມີຢູ່ສໍາລັບການຕິດຕາມກວດກາການປະຕິບັດການສອບຖາມ PostgreSQL.

ແຕ່ນັ້ນແມ່ນເລື່ອງທີ່ແຕກຕ່າງກັນຫມົດ.

ຕິດ​ຕາມ​ຕອນ​ຕໍ່​ໄປ…

ແຫຼ່ງຂໍ້ມູນ: www.habr.com