Happy Party ΠΈΠ»ΠΈ ΠΏΠ°Ρ€Π° строк-воспоминаний ΠΎ знакомствС с сСкционированиСм Π² PostgreSQL10

ΠŸΡ€Π΅Π΄ΠΈΡΠ»ΠΎΠ²ΠΈΠ΅ ΠΈΠ»ΠΈ ΠΊΠ°ΠΊ Π²ΠΎΠ·Π½ΠΈΠΊΠ»Π° идСя сСкционирования

Начало истории здСсь: Π’Ρ‹ помнишь, ΠΊΠ°ΠΊ всС Π½Π°Ρ‡ΠΈΠ½Π°Π»ΠΎΡΡŒ. ВсС Π±Ρ‹Π»ΠΎ Π²ΠΏΠ΅Ρ€Π²Ρ‹Π΅ ΠΈ вновь. ПослС Ρ‚ΠΎΠ³ΠΎ, ΠΊΠ°ΠΊ ΠΏΠΎΡ‡Ρ‚ΠΈ всС рСсурсы для ΠΎΠΏΡ‚ΠΈΠΌΠΈΠ·Π°Ρ†ΠΈΠΈ запроса, Π½Π° Ρ‚ΠΎΡ‚ ΠΌΠΎΠΌΠ΅Π½Ρ‚, Π±Ρ‹Π»ΠΈ исчСрпаны, встал вопрос β€” Π° Ρ‡Ρ‚ΠΎ ΠΆΠ΅ дальшС? Π’Π°ΠΊ ΠΈ Π²ΠΎΠ·Π½ΠΈΠΊΠ»Π° идСя ΠΎ сСкционировании.

Happy Party ΠΈΠ»ΠΈ ΠΏΠ°Ρ€Π° строк-воспоминаний ΠΎ знакомствС с сСкционированиСм Π² PostgreSQL10

ЛиричСскоС отступлСниС:
ИмСнно ‘Π½Π° Ρ‚ΠΎΡ‚ ΠΌΠΎΠΌΠ΅Π½Ρ‚’, ΠΏΠΎΡ‚ΠΎΠΌΡƒ, Ρ‡Ρ‚ΠΎ ΠΊΠ°ΠΊ Π²Ρ‹ΡΡΠ½ΠΈΠ»ΠΎΡΡŒ, имСлись Π½Π΅ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Π½Π½Ρ‹Π΅ Ρ€Π΅Π·Π΅Ρ€Π²Ρ‹ ΠΎΠΏΡ‚ΠΈΠΌΠΈΠ·Π°Ρ†ΠΈΠΈ. Бпасибо asmm ΠΈ Π₯Π°Π±Ρ€Ρƒ !

Π˜Ρ‚Π°ΠΊ, ΠΊΠ°ΠΊ Π΅Ρ‰Π΅ ΠΌΠΎΠΆΠ½ΠΎ ΡΠ΄Π΅Π»Π°Ρ‚ΡŒ Π·Π°ΠΊΠ°Π·Ρ‡ΠΈΠΊΠ° ΠΊΠ°ΠΊ Π±Ρ‹ счастливым, Π·Π°ΠΎΠ΄Π½ΠΎ ΠΈ ΠΏΡ€ΠΎΠΊΠ°Ρ‡Π°Ρ‚ΡŒ собствСнныС скилы?

Если ΠΏΡ€Π΅Π΄Π΅Π»ΡŒΠ½ΠΎ всё ΡƒΠΏΡ€ΠΎΡΡ‚ΠΈΡ‚ΡŒ, Ρ‚ΠΎ, ΠΏΡƒΡ‚Π΅ΠΉ ΠΊΠ°Ρ€Π΄ΠΈΠ½Π°Π»ΡŒΠ½ΠΎ, Ρ‡Ρ‚ΠΎ-Ρ‚ΠΎ ΡƒΠ»ΡƒΡ‡ΡˆΠΈΡ‚ΡŒ Π² быстродСйствии Π±Π°Π·Ρ‹ Π΄Π°Π½Π½Ρ‹Ρ…, всСго Π΄Π²Π°:
1)ЭкстСнсивный ΠΏΡƒΡ‚ΡŒ β€” Π½Π°Ρ€Π°Ρ‰ΠΈΠ²Π°Π΅ΠΌ рСсурсы, мСняСм ΠΊΠΎΠ½Ρ„ΠΈΠ³ΡƒΡ€Π°Ρ†ΠΈΡŽ;
2)Π˜Π½Ρ‚Π΅Π½ΡΠΈΠ²Π½Ρ‹ΠΉ ΠΏΡƒΡ‚ΡŒ β€” оптимизация запросов

ΠŸΠΎΡΠΊΠΎΠ»ΡŒΠΊΡƒ, ΠΏΠΎΠ²Ρ‚ΠΎΡ€ΡŽΡΡŒ, Π½Π° Ρ‚ΠΎΡ‚ ΠΌΠΎΠΌΠ΅Π½Ρ‚ ΡƒΠΆΠ΅ Π½Π΅ понятно Π±Ρ‹Π»ΠΎ, Ρ‡Ρ‚ΠΎ ΠΆΠ΅ Π΅Ρ‰Π΅ ΠΏΠΎΠΌΠ΅Π½ΡΡ‚ΡŒ Π² запросС для ускорСния, Π±Ρ‹Π» Π²Ρ‹Π±Ρ€Π°Π½ ΠΏΡƒΡ‚ΡŒ β€” измСнСния Π΄ΠΈΠ·Π°ΠΉΠ½Π° Ρ‚Π°Π±Π»ΠΈΡ†.

Π˜Ρ‚Π°ΠΊ β€” Π²ΠΎΠ·Π½ΠΈΠΊΠ°Π΅Ρ‚ Π³Π»Π°Π²Π½Ρ‹ΠΉ вопрос β€” Π°, Ρ‡Ρ‚ΠΎ ΠΈ ΠΊΠ°ΠΊ ΠΌΠ΅Π½ΡΡ‚ΡŒ Π±ΡƒΠ΄Π΅ΠΌ?

ΠΠ°Ρ‡Π°Π»ΡŒΠ½Ρ‹Π΅ условия

Π’ΠΎ-ΠΏΠ΅Ρ€Π²Ρ‹Ρ…, имССтся Π²ΠΎΡ‚ такая ERD (ΠΏΠΎΠΊΠ°Π·Π°Π½ΠΎ условно-ΡƒΠΏΡ€ΠΎΡ‰Π΅Π½Π½ΠΎ):
Happy Party ΠΈΠ»ΠΈ ΠΏΠ°Ρ€Π° строк-воспоминаний ΠΎ знакомствС с сСкционированиСм Π² PostgreSQL10
ΠžΡΠ½ΠΎΠ²Π½Ρ‹Π΅ особСнности:

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

Π Π΅Π·ΡƒΠ»ΡŒΡ‚Π°Ρ‚Ρ‹ выполнСния Π½Π° тСстовой Π±Π°Π·Π΅ Π΄Π°Π½Π½Ρ‹Ρ…:
Cost : 502 997.55
Execution time: 505 seconds.

Π§Ρ‚ΠΎ ΠΌΡ‹ Π²ΠΈΠ΄ΠΈΠΌ? ΠžΠ±Ρ‹Ρ‡Π½Ρ‹ΠΉ запрос, ΠΏΠΎ Π²Ρ€Π΅ΠΌΠ΅Π½Π½ΠΎΠΌΡƒ срСзу.
Π”Π΅Π»Π°Π΅ΠΌ ΠΏΡ€ΠΎΡΡ‚Π΅ΠΉΡˆΠ΅Π΅ логичСскоС ΠΏΡ€Π΅Π΄ΠΏΠΎΠ»ΠΎΠΆΠ΅Π½ΠΈΠ΅: Ссли Π΅ΡΡ‚ΡŒ Π²Ρ‹Π±ΠΎΡ€ΠΊΠ° Π²Ρ€Π΅ΠΌΠ΅Π½Π½ΠΎΠ³ΠΎ срСза, Ρ‚ΠΎ Π½Π°ΠΌ ΠΏΠΎΠΌΠΎΠΆΠ΅Ρ‚? ΠŸΡ€Π°Π²ΠΈΠ»ΡŒΠ½ΠΎ β€” сСкционированиС.

Π§Ρ‚ΠΎ ΡΠ΅ΠΊΡ†ΠΈΠΎΠ½ΠΈΡ€ΠΎΠ²Π°Ρ‚ΡŒ?

На ΠΏΠ΅Ρ€Π²Ρ‹ΠΉ взгляд, Π²Ρ‹Π±ΠΎΡ€ ΠΎΡ‡Π΅Π²ΠΈΠ΄Π΅Π½ β€” Π΄Π΅ΠΊΠ»Π°Ρ€Π°Ρ‚ΠΈΠ²Π½ΠΎΠ΅ сСкционированиС Ρ‚Π°Π±Π»ΠΈΡ†Ρ‹ Β«shipmentΒ» ΠΏΠΎ ΠΊΠ»ΡŽΡ‡Ρƒ Β«SHIPMENT_DATEΒ» (забСгая сильно Π²ΠΏΠ΅Ρ€Π΅Π΄ β€” Π² ΠΈΡ‚ΠΎΠ³Π΅ Π½Π° ΠΏΡ€ΠΎΠ΄Π°ΠΊΡˆΠ½ ΠΏΠΎΠ»ΡƒΡ‡ΠΈΠ»ΠΎΡΡŒ Π½Π΅ΠΌΠ½ΠΎΠ³ΠΎ Π½Π΅ Ρ‚Π°ΠΊ).

Как ΡΠ΅ΠΊΡ†ΠΈΠΎΠ½ΠΈΡ€ΠΎΠ²Π°Ρ‚ΡŒ?

Π­Ρ‚ΠΎΡ‚ вопрос Ρ‚ΠΎΠΆΠ΅ Π½Π΅ слишком слоТный. Π‘Π»Π°Π³ΠΎ, Π² PostgreSQL 10, Ρ‚Π΅ΠΏΠ΅Ρ€ΡŒ чСловСчСский ΠΌΠ΅Ρ…Π°Π½ΠΈΠ·ΠΌ сСкционирования.
Π˜Ρ‚Π°ΠΊ:

  1. БохраняСм Π΄Π°ΠΌΠΏ исходной Ρ‚Π°Π±Π»ΠΈΡ†Ρ‹ β€” pg_dump source_table
  2. УдаляСм ΠΈΡΡ…ΠΎΠ΄Π½ΡƒΡŽ Ρ‚Π°Π±Π»ΠΈΡ†Ρƒ β€” drop table source_table
  3. Π‘ΠΎΠ·Π΄Π°Π΅ΠΌ Ρ€ΠΎΠ΄ΠΈΡ‚Π΅Π»ΡŒΡΠΊΡƒΡŽ Ρ‚Π°Π±Π»ΠΈΡ†Ρƒ с сСкционированиСм ΠΏΠΎ Π΄ΠΈΠ°ΠΏΠ°Π·ΠΎΠ½Ρƒ β€” create table source_table
  4. Π‘ΠΎΠ·Π΄Π°Π΅ΠΌ сСкции β€” create table source_table, create index
  5. Π˜ΠΌΠΏΠΎΡ€Ρ‚ΠΈΡ€ΡƒΠ΅ΠΌ Π΄Π°ΠΌΠΏ, созданный Π½Π° шагС 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

ΠŸΡ€ΠΎΠ²Π΅Ρ€ΡΠ΅ΠΌ Ρ€Π΅Π·ΡƒΠ»ΡŒΡ‚Π°Ρ‚Ρ‹ сСкционирования

Π§Ρ‚ΠΎ ΠΆΠ΅ ΠΌΡ‹ ΠΈΠΌΠ΅Π΅ΠΌ Π² Ρ€Π΅Π·ΡƒΠ»ΡŒΡ‚Π°Ρ‚Π΅? ΠŸΠΎΠ»Π½Ρ‹ΠΉ тСкст ΠΏΠ»Π°Π½Π° выполнСния большой ΠΈ скучный, поэтому Π²ΠΏΠΎΠ»Π½Π΅ ΠΌΠΎΠΆΠ½ΠΎ ΠΎΠ³Ρ€Π°Π½ΠΈΡ‡ΠΈΡ‚ΡŒΡΡ ΠΈΡ‚ΠΎΠ³ΠΎΠ²Ρ‹ΠΌΠΈ Ρ†ΠΈΡ„Ρ€Π°ΠΌΠΈ.

Π‘Ρ‹Π»ΠΎ

Cost: 502 997.55
Execution time: 505 seconds.

Π‘Ρ‚Π°Π»ΠΎ

Cost: 77 872.36
Execution time: 79 seconds.

Π’ΠΏΠΎΠ»Π½Π΅ Ρ…ΠΎΡ€ΠΎΡˆΠΈΠΉ Ρ€Π΅Π·ΡƒΠ»ΡŒΡ‚Π°Ρ‚. УмСньшили ΡΡ‚ΠΎΠΈΠΌΠΎΡΡ‚ΡŒ ΠΈ врСмя выполнСния. Π’Π°ΠΊΠΈΠΌ ΠΎΠ±Ρ€Π°Π·ΠΎΠΌ использованиС сСкционирования Π΄Π°Π΅Ρ‚ ΠΎΠΆΠΈΠ΄Π°Π΅ΠΌΡ‹ΠΉ эффСкт ΠΈ Π² Ρ†Π΅Π»ΠΎΠΌ β€” Π±Π΅Π· нСоТиданностСй.

ΠžΠ±Ρ€Π°Π΄ΠΎΠ²Π°Ρ‚ΡŒ Π·Π°ΠΊΠ°Π·Ρ‡ΠΈΠΊΠ°

Π Π΅Π·ΡƒΠ»ΡŒΡ‚Π°Ρ‚Ρ‹ тСстирования Π±Ρ‹Π»ΠΈ прСдставлСны Π·Π°ΠΊΠ°Π·Ρ‡ΠΈΠΊΡƒ Π½Π° рассмотрСниС. И послС ознакомлСния ΠΈΠΌ Π±Ρ‹Π» Π²Ρ‹Π΄Π°Π½ нСсколько Π½Π΅ΠΎΠΆΠΈΠ΄Π°Π½Π½Ρ‹ΠΉ Π²Π΅Ρ€Π΄ΠΈΠΊΡ‚: Β«ΠžΡ‚Π»ΠΈΡ‡Π½ΠΎ, сСкционируйтС Ρ‚Π°Π±Π»ΠΈΡ†Ρƒ Β«data»».

Π”Π°, Π½ΠΎ ΠΌΡ‹ вСдь исслСдовали совсСм Π΄Ρ€ΡƒΠ³ΡƒΡŽ Ρ‚Π°Π±Π»ΠΈΡ†Ρƒ Β«shipmentΒ», Ρ‚Π°Π±Π»ΠΈΡ†Π° Β«dataΒ» Π½Π΅ ΠΈΠΌΠ΅Π΅Ρ‚ поля Β«SHIPMENT_DATEΒ».

НС ΠΏΡ€ΠΎΠ±Π»Π΅ΠΌΠ°, добавляйтС, мСняйтС. Π“Π»Π°Π²Π½ΠΎΠ΅, Ρ‡Ρ‚ΠΎΠ±Ρ‹ Π·Π°ΠΊΠ°Π·Ρ‡ΠΈΠΊΠ° устраивало, Ρ‡Ρ‚ΠΎ получится Π² Ρ€Π΅Π·ΡƒΠ»ΡŒΡ‚Π°Ρ‚Π΅, подробности Ρ€Π΅Π°Π»ΠΈΠ·Π°Ρ†ΠΈΠΈ Π½Π΅ особо Ρ‚ΠΎ ΠΈ Π²Π°ΠΆΠ½Ρ‹.

Π‘Π΅ΠΊΡ†ΠΈΠΎΠ½ΠΈΡ€ΡƒΠ΅ΠΌ ΠΎΡΠ½ΠΎΠ²Π½ΡƒΡŽ Ρ‚Π°Π±Π»ΠΈΡ†Ρƒ Β«dataΒ»

Π’ ΠΎΠ±Ρ‰Π΅ΠΌ-Ρ‚ΠΎ Π½ΠΈΠΊΠ°ΠΊΠΈΡ… особых слоТностСй Π½Π΅ Π²ΠΎΠ·Π½ΠΈΠΊΠ»ΠΎ. Π₯отя, Π°Π»Π³ΠΎΡ€ΠΈΡ‚ΠΌ сСкционирования, ΠΊΠΎΠ½Π΅Ρ‡Π½ΠΎ, нСсколько помСнялся.

ДобавляСм столбСц Β«SHIPMENT_DATAΒ» Π² Ρ‚Π°Π±Π»ΠΈΡ†Ρƒ Β«dataΒ»

psql -h хост -U Π±Π°Π·Π° -d ΡŽΠ·Π΅Ρ€
=> ALTER TABLE data ADD COLUMN "SHIPMENT_DATE" timestamp without time zone ;

ЗаполняСм значСния столбца Β«SHIPMENT_DATAΒ» Π² Ρ‚Π°Π±Π»ΠΈΡ†Π΅ Β«dataΒ», значСниями ΠΎΠ΄Π½ΠΎΠΈΠΌΠ΅Π½Π½ΠΎΠ³ΠΎ столбца ΠΈΠ· Ρ‚Π°Π±Π»ΠΈΡ†Ρ‹ Β«shipmentΒ»

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

БохраняСм Π΄Π°ΠΌΠΏ Ρ‚Π°Π±Π»ΠΈΡ†Ρ‹ Β«dataΒ»

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

ΠŸΠ΅Ρ€Π΅ΡΠΎΠ·Π΄Π°Π΅ΠΌ ΡΠ΅ΠΊΡ†ΠΈΠΎΠ½ΠΈΡ€ΠΎΠ²Π°Π½Π½ΡƒΡŽ Ρ‚Π°Π±Π»ΠΈΡ†Ρƒ Β«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
$$;

Π—Π°Π³Ρ€ΡƒΠΆΠ°Π΅ΠΌ Π΄Π°ΠΌΠΏ созданный Π½Π° шагС 3.

pg_restore -h хост -ΡŽΠ·Π΅Ρ€ -d Π±Π°Π·Π° --data-only --format=c --table=data --verbose  data.dmp > data_restore.log 2>&1

CΠΎΠ·Π΄Π°Π΅ΠΌ ΠΎΡ‚Π΄Π΅Π»ΡŒΠ½ΡƒΡŽ ΡΠ΅ΠΊΡ†ΠΈΡŽ для старых Π΄Π°Π½Π½Ρ‹Ρ…

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

Π˜Ρ‚ΠΎΠ³ΠΎΠ²Ρ‹Π΅ Ρ€Π΅Π·ΡƒΠ»ΡŒΡ‚Π°Ρ‚Ρ‹:

Π‘Ρ‹Π»ΠΎ
Cost: 502 997.55
Execution time: 505 seconds.

Π‘Ρ‚Π°Π»ΠΎ
Cost: 68 533.70
Execution time: 69 seconds

Достойно, Π²ΠΏΠΎΠ»Π½Π΅ достойно. А учитывая, Ρ‡Ρ‚ΠΎ ΠΏΠΎ ΠΏΡƒΡ‚ΠΈ ΡƒΠ΄Π°Π»ΠΎΡΡŒ Π±ΠΎΠ»Π΅Π΅-ΠΌΠ΅Π½Π΅Π΅ ΠΎΡΠ²ΠΎΠΈΡ‚ΡŒ ΠΌΠ΅Ρ…Π°Π½ΠΈΠ·ΠΌ сСкционирования Π² PostgreSQL 10 β€” ΠžΡ‚Π»ΠΈΡ‡Π½Ρ‹ΠΉ Ρ€Π΅Π·ΡƒΠ»ΡŒΡ‚Π°Ρ‚.

ЛиричСскоС отступлСниС

А ΠΌΠΎΠΆΠ½ΠΎ, ΡΠ΄Π΅Π»Π°Ρ‚ΡŒ Π΅Ρ‰Π΅ Π»ΡƒΡ‡ΡˆΠ΅ — ДА, ΠœΠžΠ–ΠΠž!Для этого Π½ΡƒΠΆΠ½ΠΎ ΠΈΡΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Ρ‚ΡŒ 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 ;

Π’ ΠΎΡ‡Π΅Ρ€Π΅Π΄Π½ΠΎΠΉ Ρ€Π°Π· пСрСписываСм запрос:
Запрос с использованиСм materialized 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';

И ΠΏΠΎΠ»ΡƒΡ‡Π°Π΅ΠΌ Π΅Ρ‰Π΅ ΠΎΠ΄ΠΈΠ½ ΠΈΡ‚ΠΎΠ³:
Π‘Ρ‹Π»ΠΎ
Cost: 502 997.55
Execution time: 505 seconds

Π‘Ρ‚Π°Π»ΠΎ
Cost: 42 481.16
Execution time: 43 seconds.

Π₯отя ΠΊΠΎΠ½Π΅Ρ‡Π½ΠΎ, ΡΡ‚ΠΎΠ»ΡŒ ΠΌΠ½ΠΎΠ³ΠΎΠΎΠ±Π΅Ρ‰Π°ΡŽΡ‰ΠΈΠΉ Ρ€Π΅Π·ΡƒΠ»ΡŒΡ‚Π°Ρ‚ ΠΎΠ½ ΠΎΠ±ΠΌΠ°Π½Ρ‡ΠΈΠ², прСдставлСния-Ρ‚ΠΎ Π½Π°Π΄ΠΎ Ρ€Π΅Ρ„Ρ€Π΅ΡˆΠΈΡ‚ΡŒ. Π’Π°ΠΊ Ρ‡Ρ‚ΠΎ ΠΈΡ‚ΠΎΠ³ΠΎΠ²ΠΎΠ΅ врСмя получСния Π΄Π°Π½Π½Ρ‹Ρ… Π½Π΅ сильно-Ρ‚ΠΎ ΠΈ ΠΏΠΎΠΌΠΎΠΆΠ΅Ρ‚. Но Π² качСствС экспСримСнта Π²ΠΏΠΎΠ»Π½Π΅ интСрСсно.

На самом Π΄Π΅Π»Π΅, ΠΊΠ°ΠΊ Π²Ρ‹ΡΡΠ½ΠΈΠ»ΠΎΡΡŒ, Π΅Ρ‰Ρ‘ Ρ€Π°Π· спасибо asmm ΠΈ Π₯Π°Π±Ρ€Ρƒ !- запрос ΠΌΠΎΠΆΠ½ΠΎ Π΅Ρ‰Π΅ ΡƒΠ»ΡƒΡ‡ΡˆΠΈΡ‚ΡŒ.

ПослСсловиС

Π˜Ρ‚Π°ΠΊ, Π·Π°ΠΊΠ°Π·Ρ‡ΠΈΠΊ ΡƒΠ΄ΠΎΠ²Π»Π΅Ρ‚Π²ΠΎΡ€Π΅Π½. И Π½ΡƒΠΆΠ½ΠΎ ΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Ρ‚ΡŒΡΡ ситуациСй.

Новая Π·Π°Π΄Π°Ρ‡Π°: Π§Ρ‚ΠΎ ΠΌΠΎΠΆΠ½ΠΎ Ρ‚Π°ΠΊΠΎΠ³ΠΎ ΠΏΡ€ΠΈΠ΄ΡƒΠΌΠ°Ρ‚ΡŒ, Ρ‡Ρ‚ΠΎΠ±Ρ‹ ΡƒΠ³Π»ΡƒΠ±ΠΈΡ‚ΡŒ ΠΈ Ρ€Π°ΡΡˆΠΈΡ€ΠΈΡ‚ΡŒ?

И Ρ‚ΡƒΡ‚ вспоминаСтся β€” рСбята, Π° вСдь Ρƒ нас Π½Π΅Ρ‚ ΠΌΠΎΠ½ΠΈΡ‚ΠΎΡ€ΠΈΠ½Π³Π° Π½Π°ΡˆΠΈΡ… Π±Π°Π· Π΄Π°Π½Π½Ρ‹Ρ… PostgreSQL.

ПолоТа Ρ€ΡƒΠΊΡƒ Π½Π° сСрдцС, Π½Π΅ΠΊΠΈΠΉ ΠΌΠΎΠ½ΠΈΡ‚ΠΎΡ€ΠΈΠ½Π³ Π² Π²ΠΈΠ΄Π΅ Cloud Watch Π½Π° AWS всС-Ρ‚Π°ΠΊΠΈ Π΅ΡΡ‚ΡŒ. Но какая польза ΠΎΡ‚ этого ΠΌΠΎΠ½ΠΈΡ‚ΠΎΡ€ΠΈΠ½Π³Π° для DBA? Π’ ΠΎΠ±Ρ‰Π΅ΠΌ-Ρ‚ΠΎ практичСски Π½ΠΈΠΊΠ°ΠΊΠΎΠΉ.

Если Π²Ρ‹ΠΏΠ°Π» шанс ΡΠ΄Π΅Π»Π°Ρ‚ΡŒ ΠΏΠΎΠ»Π΅Π·Π½ΠΎΠ΅ ΠΈ интСрСсноС ΠΈ для сСбя, Π½Π΅ Π²ΠΎΡΠΏΠΎΠ»ΡŒΠ·ΠΎΠ²Π°Ρ‚ΡŒΡΡ Ρ‚Π°ΠΊΠΈΠΌ шансом нСльзя …
Π˜Π‘Πž

Happy Party ΠΈΠ»ΠΈ ΠΏΠ°Ρ€Π° строк-воспоминаний ΠΎ знакомствС с сСкционированиСм Π² PostgreSQL10

Π’ΠΎΡ‚ Ρ‚Π°ΠΊ ΠΌΡ‹ ΠΈ подошли ΠΊ самому интСрСсному:

3 ДСкабря 2018 Π³ΠΎΠ΄Π°.
ΠŸΡ€ΠΈΠ½ΡΡ‚ΠΈΠ΅ Ρ€Π΅ΡˆΠ΅Π½ΠΈΡ ΠΎ Π½Π°Ρ‡Π°Π»Π΅ Ρ€Π°Π±ΠΎΡ‚ ΠΏΠΎ исслСдованию ΠΈΠΌΠ΅ΡŽΡ‰ΠΈΡ…ΡΡ возмоТностСй ΠΌΠΎΠ½ΠΈΡ‚ΠΎΡ€ΠΈΠ½Π³Π° ΠΏΡ€ΠΎΠΈΠ·Π²ΠΎΠ΄ΠΈΡ‚Π΅Π»ΡŒΠ½ΠΎΡΡ‚ΠΈ запросов PostgreSQL.

Но это ΡƒΠΆΠ΅ совсСм, другая история.

ΠŸΡ€ΠΎΠ΄ΠΎΠ»ΠΆΠ΅Π½ΠΈΠ΅, слСдуСт…

Π˜ΡΡ‚ΠΎΡ‡Π½ΠΈΠΊ: habr.com