{"id":31905,"date":"2019-10-31T21:43:52","date_gmt":"2019-10-31T18:43:52","guid":{"rendered":"https:\/\/prohoster.info\/blog\/happy-party-ili-para-strok-vospominanij-o-znakomstve-s-sektsionirovaniem-v-postgresql10\/"},"modified":"2019-10-31T21:43:52","modified_gmt":"2019-10-31T18:43:52","slug":"happy-party-ili-para-strok-vospominanij-o-znakomstve-s-sektsionirovaniem-v-postgresql10","status":"publish","type":"post","link":"https:\/\/prohoster.info\/fr\/blog\/administrirovanie\/happy-party-ili-para-strok-vospominanij-o-znakomstve-s-sektsionirovaniem-v-postgresql10","title":{"rendered":"Happy Party ou quelques lignes de souvenirs sur la d\u00e9couverte de la partition dans PostgreSQL 10","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Pr\u00e9face ou comment l'id\u00e9e de la section a \u00e9merg\u00e9<\/h4>\n

\nLe d\u00e9but de l'histoire ici : Tu te souviens comment tout a commenc\u00e9. Tout \u00e9tait nouveau et in\u00e9dit.<\/a><\/noindex> Apr\u00e8s que presque toutes les ressources pour optimiser la requ\u00eate aient \u00e9t\u00e9 \u00e9puis\u00e9es \u00e0 l'\u00e9poque, la question s'est pos\u00e9e : que faire ensuite ? Ainsi est n\u00e9e l'id\u00e9e de la section. <\/p>\n

\"Happy
\n
\nD\u00e9tour lyrique :<\/b>
\nC'est justement \u00e0 ce moment-l\u00e0, parce que comme il s'est av\u00e9r\u00e9, il restait des r\u00e9serves inexploit\u00e9es d'optimisation<\/a><\/noindex>. Merci asmm<\/a><\/noindex> et Habr !<\/i><\/p>\n

Alors, comment rendre le client un peu heureux, tout en d\u00e9veloppant mes comp\u00e9tences ? <\/p>\n

Si l'on simplifie au maximum<\/b>il n'y a en gros que deux fa\u00e7ons d'am\u00e9liorer radicalement la performance de la base de donn\u00e9es :
\n1) Chemin extensif \u2014 augmenter les ressources, changer la configuration ;
\n2) Chemin intensif \u2014 optimiser les requ\u00eates<\/p>\n

Puisque, je le r\u00e9p\u00e8te, \u00e0 l'\u00e9poque il n'\u00e9tait d\u00e9j\u00e0 pas clair ce qu'on pouvait encore changer dans la requ\u00eate pour acc\u00e9l\u00e9rer, le choix s'est port\u00e9 sur la modification du design des tables.<\/b><\/p>\n

Ainsi, la question principale se pose : qu'allons-nous changer et comment ? <\/b>
\n<\/a><\/noindex><\/p>\n

Conditions initiales<\/h2>\n

\nTout d'abord, nous avons un ERD comme ceci (montr\u00e9 de mani\u00e8re conditionnelle et simplifi\u00e9e) :
\n\"Happy
\nCaract\u00e9ristiques principales :<\/p>\n

    \n
  1. relations 'beaucoup \u00e0 beaucoup'<\/li>\n
  2. la table a d\u00e9j\u00e0 une cl\u00e9 potentielle de section <\/li>\n<\/ol>\n

    Requ\u00eate d'origine :<\/p>\n

    SELECT\n            p.\"PARAMETER_ID\" as  parameter_id,\n            pc.\"PC_NAME\" AS pc_name,\n            pc.\"CUSTOMER_PARTNUMBER\" AS customer_partnumber,\n            w.\"LASERMARK\" AS lasermark,\n            w.\"LOTID\" AS lotid,\n            w.\"REPORTED_VALUE\" AS reported_value,\n            w.\"LOWER_SPEC_LIMIT\" AS lower_spec_limit,\n            w.\"UPPER_SPEC_LIMIT\" AS upper_spec_limit,\n            p.\"TYPE_CALCUL\" AS type_calcul,\n            s.\"SHIPMENT_NAME\" AS shipment_name,\n            s.\"SHIPMENT_DATE\" AS shipment_date,\n            extract(year from s.\"SHIPMENT_DATE\") AS year,\n            extract(month from s.\"SHIPMENT_DATE\") as month,\n            s.\"REPORT_NAME\" AS report_name,\n            p.\"SPARAM_NAME\" AS SPARAM_name,\n            p.\"CUSTOMERPARAM_NAME\" AS customerparam_name\n        FROM data w INNER JOIN shipment s ON s.\"SHIPMENT_ID\" = w.\"SHIPMENT_ID\"\n             INNER JOIN parameters p ON p.\"PARAMETER_ID\" = w.\"PARAMETER_ID\"\n             INNER JOIN shipment_pc sp ON s.\"SHIPMENT_ID\" = sp.\"SHIPMENT_ID\"\n             INNER JOIN pc pc ON pc.\"PC_ID\" = sp.\"PC_ID\"\n             INNER JOIN ( SELECT w2.\"LASERMARK\" , MAX(s2.\"SHIPMENT_DATE\") AS \"SHIPMENT_DATE\"\n                          FROM shipment s2 INNER JOIN data w2 ON s2.\"SHIPMENT_ID\" = w2.\"SHIPMENT_ID\" \n                          GROUP BY w2.\"LASERMARK\"\n                         ) md ON md.\"SHIPMENT_DATE\" = s.\"SHIPMENT_DATE\" AND md.\"LASERMARK\" = w.\"LASERMARK\"\n        WHERE \n             s.\"SHIPMENT_DATE\" >= '2018-07-01' AND s.\"SHIPMENT_DATE\" <= '2018-09-30' ;\n<\/code><\/pre>\n
    \nR\u00e9sultats d'ex\u00e9cution sur la base de donn\u00e9es de test :<\/b>
    \nCo\u00fbt <\/b>: 502 997.55
    \nTemps d'ex\u00e9cution<\/b>: 505 secondes.<\/p>\n
    Que voyons-nous ? Une requ\u00eate ordinaire, selon une tranche temporelle. 
    \nFaisons une hypoth\u00e8se logique simple : si nous avons un \u00e9chantillon d'une tranche de temps, cela nous aide-t-il ? Correct \u2014 le partitionnement.<\/p>\n
    Que faut-il partitionner ?<\/h2>\n
    \n\u00c0 premi\u00e8re vue, le choix est \u00e9vident \u2014 le partitionnement d\u00e9claratif de la table \u00ab shipment \u00bb par la cl\u00e9 \u00ab SHIPMENT_DATE \u00bb (en allant tr\u00e8s loin \u2014 au final, cela ne s'est pas pass\u00e9 tout \u00e0 fait comme pr\u00e9vu en production<\/i>). <\/p>\n
    Comment partitionner ?<\/h2>\n
    \nCette question n'est pas trop complexe non plus. Heureusement, avec PostgreSQL 10, il y a maintenant un m\u00e9canisme humain de partitionnement. 
    \nDonc : <\/p>\n
      \n
    1.  Nous sauvegardons le dump de la table source \u2014 pg_dump source_table<\/i><\/li>\n
    2.  Nous supprimons la table source \u2014 drop table source_table<\/i><\/li>\n
    3. Nous cr\u00e9ons la table parent avec partitionnement par plage \u2014 create table source_table<\/i><\/li>\n
    4.  Nous cr\u00e9ons des sections \u2014 create table source_table, create index<\/i><\/li>\n
    5.  Nous importons le dump cr\u00e9\u00e9 \u00e0 l'\u00e9tape 1 \u2014 pg_restore<\/i><\/li>\n<\/ol>\n
      <\/p>\n
      Scripts pour le partitionnement <\/h2>\n
      \nPour des raisons de simplicit\u00e9 et de commodit\u00e9, les \u00e9tapes 2, 3 et 4 ont \u00e9t\u00e9 regroup\u00e9es dans un seul script. <\/p>\n
      Donc : 
      \nNous sauvegardons le dump de la table source<\/b><\/p>\n
      pg_dump postgres --file=\\\/dump\\\/shipment.dmp --format=c --table=shipment --verbose > \\\/dump\\\/shipment.log 2>&1<\/code><\/pre>\n
      \nNous supprimons la table source + Nous cr\u00e9ons la table parent avec partitionnement par plage + Nous cr\u00e9ons des sections<\/b><\/p>\n
      --create_partition_shipment.sql\ndo language plpgsql $$\ndeclare \nrec_shipment_date RECORD ;\npartition_name varchar;\nindex_name varchar;\ncurrent_year varchar ;\ncurrent_month varchar ;\nbegin_year varchar ;\nbegin_month varchar ;\nnext_year varchar ;\nnext_month varchar ;\nfirst_flag boolean ;\ni integer ;\nbegin\n  RAISE NOTICE 'CR\u00c9ER UNE TABLE TEMPORAIRE POUR SHIPMENT_DATE';\n  CREATE TEMP TABLE tmp_shipment_date as select distinct \"SHIPMENT_DATE\" from shipment order by \"SHIPMENT_DATE\" ;\n\n  RAISE NOTICE 'SUPPRIMER LA TABLE shipment';\n  drop table shipment cascade ;\n  \n  CREATE TABLE public.shipment\n  (\n    \"SHIPMENT_ID\" integer NOT NULL DEFAULT nextval('shipment_shipment_id_seq'::regclass),\n    \"SHIPMENT_NAME\" character varying(30) COLLATE pg_catalog.\"default\",\n    \"SHIPMENT_DATE\" timestamp without time zone,\n    \"REPORT_NAME\" character varying(40) COLLATE pg_catalog.\"default\"\n  )\n  PARTITION BY RANGE (\"SHIPMENT_DATE\")\n  WITH (\n      OIDS = FALSE\n  )\n  TABLESPACE pg_default;\n\n  RAISE NOTICE 'CR\u00c9ER DES PARTITIONS POUR LA TABLE shipment';\n\n  current_year:='0';\n  current_month:='0';\n\n  begin_year := '0' ;\n  begin_month := '0'  ;\n  next_year := '0' ;\n  next_month := '0'  ;\n\n  FOR rec_shipment_date IN SELECT * FROM tmp_shipment_date LOOP\n      \n      RAISE NOTICE 'SHIPMENT_DATE=%',rec_shipment_date.\"SHIPMENT_DATE\";\n      \n      current_year := date_part('year' ,rec_shipment_date.\"SHIPMENT_DATE\");\n      current_month := date_part('month' ,rec_shipment_date.\"SHIPMENT_DATE\") ; \n\n      IF to_number(current_month,'99') = to_date( begin_year||'.'||begin_month, 'YYYY.MM') AND \n         to_date( current_year||'.'||current_month, 'YYYY.MM') < to_date( next_year||'.'||next_month, 'YYYY.MM') AND \n         NOT first_flag \n      THEN\n         CONTINUE ; \n      ELSE\n       --NEW borders only for second and after time \n       begin_year := current_year ;\n       begin_month := current_month ;   \n   \n        IF current_month = '12' THEN\n          next_year := date_part('year' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 year') ;\n        ELSE\n          next_year := current_year ;\n        END IF;\n     \n       next_month := date_part('month' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 month') ;\n\n      END IF;      \n\n      partition_name := 'shipment_shipment_date_'||begin_year||'-'||begin_month||'-01-'|| next_year||'-'||next_month||'-01'  ;\n \n     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'  ) ; \n\n      index_name := partition_name||'_shipment_id_idx';\n      RAISE NOTICE 'NOM D'INDEX =%',index_name;\n      EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_ID") TABLESPACE pg_default ' ) ; \n\n      --Drop first time flag\n      first_flag := false ;\n   \n  END LOOP;\n\nend\n$$;<\/code><\/pre>\n
      \nImporter le dump<\/b><\/p>\n
      pg_restore -d postgres --data-only --format=c --table=shipment --verbose  shipment.dmp > \/tmp\/data_dump\/shipment_restore.log 2>&1<\/code><\/pre>\n
      <\/p>\n
      V\u00e9rifions les r\u00e9sultats de la partition<\/h2>\n
      \nQue pouvons-nous tirer de tout cela ? Le texte complet du plan d'ex\u00e9cution est long et ennuyeux, il est donc tout \u00e0 fait possible de se limiter aux chiffres finaux.<\/p>\n
      Il y avait<\/h3>\n
      \nCo\u00fbt :<\/b> 502 997.55
      \nTemps d'ex\u00e9cution :<\/b> 505 secondes.<\/p>\n
      Il est devenu<\/h3>\n
      \nCo\u00fbt :<\/b> 77 872.36
      \nTemps d'ex\u00e9cution :<\/b> 79 secondes.<\/p>\n
      Un r\u00e9sultat tout \u00e0 fait bon. Nous avons r\u00e9duit le co\u00fbt et le temps d'ex\u00e9cution. Ainsi, l'utilisation de la partitionnement donne l'effet attendu et, en g\u00e9n\u00e9ral, sans surprises. <\/p>\n
      Rendre le client heureux<\/h2>\n
      \nLes r\u00e9sultats des tests ont \u00e9t\u00e9 pr\u00e9sent\u00e9s au client pour examen. Et apr\u00e8s avoir pris connaissance, il a \u00e9mis un verdict quelque peu inattendu : \u00ab Excellent, partitionnez la table \u00abdata\u00bb \u00bb.<\/p>\n
      Oui, mais nous avons \u00e9tudi\u00e9 une table compl\u00e8tement diff\u00e9rente, la table \u00abshipment\u00bb, la table \u00abdata\u00bb n'a pas de champ \u00abSHIPMENT_DATE\u00bb.<\/p>\n
      Pas de probl\u00e8me, ajoutez, modifiez. L'essentiel est que le client soit satisfait du r\u00e9sultat final, les d\u00e9tails de mise en \u0153uvre ne sont pas si importants.<\/p>\n
      Nous partitionnons la table principale \u00abdata\u00bb<\/h2>\n
      \nEn fin de compte, il n'y a pas eu de difficult\u00e9s particuli\u00e8res. Bien que, l'algorithme de partitionnement ait bien s\u00fbr l\u00e9g\u00e8rement chang\u00e9.<\/p>\n
      Ajout d'une colonne \u00abSHIPMENT_DATA\u00bb dans la table \u00abdata\u00bb<\/b><\/p>\n
      psql -h h\u00f4te -U base -d utilisateur\n=> ALTER TABLE data ADD COLUMN \"SHIPMENT_DATE\" timestamp without time zone ;<\/code><\/pre>\n
      Nous remplissons les valeurs de la colonne \u00abSHIPMENT_DATA\u00bb dans la table \u00abdata\u00bb avec les valeurs de la colonne homonyme dans la table \u00abshipment\u00bb<\/b><\/p>\n
      -----------------------------\n--update_data.sql\n--mise \u00e0 jour pour la table \"data\" modifi\u00e9e avec les valeurs de \"shipment_data\" de la table \"shipment\"\n--version 1.0\ndo language plpgsql $$\ndeclare\nrec_shipment_data RECORD ;\nshipment_date timestamp without time zone ;\nrow_count integer ;\ntotal_rows integer ;\nbegin\n\n  select count(*) into total_rows from shipment ;\n  RAISE NOTICE 'Total %',total_rows;\n  row_count:= 0 ;\n\n  FOR rec_shipment_data IN SELECT * FROM shipment LOOP\n\n   update data set \"SHIPMENT_DATE\" = rec_shipment_data.\"SHIPMENT_DATE\" where \"SHIPMENT_ID\" = rec_shipment_data.\"SHIPMENT_ID\";\n   \n   row_count:=  row_count +1 ;\n   RAISE NOTICE 'row count = % , from %',row_count,total_rows;\n  END LOOP;\n\nend\n$$;<\/code><\/pre>\n
      \nNous sauvegardons le dump de la table \u00abdata\u00bb<\/b><\/p>\n
      pg_dump postgres --file=\\\/dump\\\/data.dmp --format=c --table=data --verbose > \\\/dump\\\/data.log 2>&1<\\\/source<\/code><\/pre>\n
      Nous recr\u00e9ons la table \u00abdata\u00bb partitionn\u00e9e<\/b><\/p>\n
      --create_partition_data.sql\n--cr\u00e9er des partitions pour la table \"wafer data\" par la colonne \"shipment_data\" avec une dur\u00e9e d'un mois\n--version 1.0\ndo language plpgsql $$\ndeclare \nrec_shipment_date RECORD ;\npartition_name varchar;\nindex_name varchar;\ncurrent_year varchar ;\ncurrent_month varchar ;\nbegin_year varchar ;\nbegin_month varchar ;\nnext_year varchar ;\nnext_month varchar ;\nfirst_flag boolean ;\ni integer ;\n\nbegin\n\n  RAISE NOTICE 'CR\u00c9ER UNE TABLE TEMPORAIRE POUR SHIPMENT_DATE';\n  CREATE TEMP TABLE tmp_shipment_date as select distinct \"SHIPMENT_DATE\" from shipment order by \"SHIPMENT_DATE\" ;\n\n\n  RAISE NOTICE 'SUPPRIMER LA TABLE data';\n  drop table data cascade ;\n\n\n  RAISE NOTICE 'CR\u00c9ER LA TABLE PARTITIONN\u00c9E data';\n  \n  CREATE TABLE public.data\n  (\n    \"RUN_ID\" integer,\n    \"LASERMARK\" character varying(20) COLLATE pg_catalog.\"default\" NOT NULL,\n    \"LOTID\" character varying(80) COLLATE pg_catalog.\"default\",\n    \"SHIPMENT_ID\" integer NOT NULL,\n    \"PARAMETER_ID\" integer NOT NULL,\n    \"INTERNAL_VALUE\" character varying(75) COLLATE pg_catalog.\"default\",\n    \"REPORTED_VALUE\" character varying(75) COLLATE pg_catalog.\"default\",\n    \"LOWER_SPEC_LIMIT\" numeric,\n    \"UPPER_SPEC_LIMIT\" numeric , \n    \"SHIPMENT_DATE\" timestamp without time zone\n  )\n  PARTITION BY RANGE (\"SHIPMENT_DATE\")\n  WITH (\n    OIDS = FALSE\n  )\n  TABLESPACE pg_default ;\n\n\n  RAISE NOTICE 'CR\u00c9ER DES PARTITIONS POUR LA TABLE data';\n\n  current_year:='0';\n  current_month:='0';\n\n  begin_year := '0' ;\n  begin_month := '0'  ;\n  next_year := '0' ;\n  next_month := '0'  ;\n  i := 1;\n\n  FOR rec_shipment_date IN SELECT * FROM tmp_shipment_date LOOP\n      \n      RAISE NOTICE 'SHIPMENT_DATE=%',rec_shipment_date.\"SHIPMENT_DATE\";\n      \n      current_year := date_part('year' ,rec_shipment_date.\"SHIPMENT_DATE\");\n      current_month := date_part('month' ,rec_shipment_date.\"SHIPMENT_DATE\") ; \n\n      --Init borders\n      IF   begin_year = '0' THEN\n       RAISE NOTICE '***Init borders';\n       first_flag := true ; --first time flag\n       begin_year := current_year ;\n       begin_month := current_month ;   \n   \n        IF current_month = '12' THEN\n          next_year := date_part('year' ,rec_shipment_date.\"SHIPMENT_DATE\" + interval '1 year') ;\n        ELSE\n          next_year := current_year ;\n        END IF;\n     \n       next_month := date_part('month' ,rec_shipment_date.\"SHIPMENT_DATE\" + interval '1 month') ;\n\n      END IF;\n\n--      RAISE NOTICE 'current_year=% , current_month=% ',current_year,current_month;\n--      RAISE NOTICE 'begin_year=% , begin_month=% ',begin_year,begin_month;\n--      RAISE NOTICE 'next_year=% , next_month=% ',next_year,next_month;\n\n      -- V\u00e9rifier si la date actuelle est dans les limites, PAS pour la premi\u00e8re fois\n\n      RAISE NOTICE 'Current data = %',to_char( to_date( current_year||'.'||current_month, 'YYYY.MM'), 'YYYY.MM');\n      RAISE NOTICE 'Begin data = %',to_char( to_date( begin_year||'.'||begin_month, 'YYYY.MM'), 'YYYY.MM');\n      RAISE NOTICE 'Next data = %',to_char( to_date( next_year||'.'||next_month, 'YYYY.MM'), 'YYYY.MM');\n\n      IF to_date( current_year||'.'||current_month, 'YYYY.MM') >= to_date( begin_year||'.'||begin_month, 'YYYY.MM') AND \n         to_date( current_year||'.'||current_month, 'YYYY.MM') < to_date( next_year||'.'||next_month, 'YYYY.MM') AND \n         NOT first_flag \n      THEN\n         RAISE NOTICE '***CONTINUE';\n         CONTINUE ; \n      ELSE\n       --Nouvelles limites uniquement pour la seconde fois et apr\u00e8s \n       RAISE NOTICE '***NOUVELLES LIMITES';\n       begin_year := current_year ;\n       begin_month := current_month ;   \n   \n        IF current_month = '12' THEN\n          next_year := date_part('year' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 year') ;\n        ELSE\n          next_year := current_year ;\n        END IF;\n     \n       next_month := date_part('month' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 month') ;\n\n\n      END IF;      \n\n      IF to_number(current_month,'99') < 10 THEN\n        current_month := '0'||current_month ; \n      END IF ;\n\n      IF to_number(begin_month,'99') < 10 THEN\n        begin_month := '0'||begin_month ; \n      END IF ;\n\n      IF to_number(next_month,'99') < 10 THEN\n        next_month := '0'||next_month ; \n      END IF ;\n\n      RAISE NOTICE 'current_year=% , current_month=% ',current_year,current_month;\n      RAISE NOTICE 'begin_year=% , begin_month=% ',begin_year,begin_month;\n      RAISE NOTICE 'next_year=% , next_month=% ',next_year,next_month;\n\n      partition_name := 'data_'||begin_year||begin_month||'01_'||next_year||next_month||'01'  ;\n\n      RAISE NOTICE 'PARTITION NUMBER % , TABLE NAME =%',i , partition_name;\n      \n      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'  ) ; \n\n      index_name := partition_name||'_shipment_id_parameter_id_idx';\n      RAISE NOTICE 'INDEX NAME =%',index_name;\n      EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_ID", "PARAMETER_ID") TABLESPACE pg_default ' ) ; \n\n      index_name := partition_name||'_lasermark_idx';\n      RAISE NOTICE 'INDEX NAME =%',index_name;\n      EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("LASERMARK" COLLATE pg_catalog."default") TABLESPACE pg_default ' ) ; \n\n      index_name := partition_name||'_shipment_id_idx';\n      RAISE NOTICE 'INDEX NAME =%',index_name;\n      EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_ID") TABLESPACE pg_default ' ) ; \n\n      index_name := partition_name||'_parameter_id_idx';\n      RAISE NOTICE 'INDEX NAME =%',index_name;\n      EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("PARAMETER_ID") TABLESPACE pg_default ' ) ; \n\n      index_name := partition_name||'_shipment_date_idx';\n      RAISE NOTICE 'INDEX NAME =%',index_name;\n      EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_DATE") TABLESPACE pg_default ' ) ; \n\n      --Supprimer le drapeau de premi\u00e8re fois\n      first_flag := false ;\n\n  END LOOP;\nend\n$$;\n<\/code><\/pre>\n
      \nNous chargeons le dump cr\u00e9\u00e9 \u00e0 l'\u00e9tape 3.<\/b><\/p>\n
      pg_restore -h h\u00f4te -user -d base --data-only --format=c --table=data --verbose data.dmp > data_restore.log 2>&1<\/code><\/pre>\n
      \nCr\u00e9ons une section distincte pour les anciennes donn\u00e9es<\/b><\/p>\n
      ---------------------------------------------------\n--create_partition_for_old_dates.sql\n--cr\u00e9er des partitions pour conserver les anciennes dates \n--version 1.0\ndo language plpgsql $$\ndeclare \nrec_shipment_date RECORD ;\npartition_name varchar;\nindex_name varchar;\n\nbegin\n\n      SELECT min(\"SHIPMENT_DATE\") AS min_date INTO rec_shipment_date from data ;\n\n      RAISE NOTICE 'Ancienne date est %',rec_shipment_date.min_date ;\n\n      partition_name := 'data_old_dates'  ;\n\n      RAISE NOTICE 'NOM DE LA PARTITION EST %',partition_name;\n\n      EXECUTE format('CREATE TABLE ' || quote_ident(partition_name) || ' PARTITION OF data FOR VALUES FROM ( %L ) TO ( %L )  ' , '1900-01-01' , \n              to_char( rec_shipment_date.min_date,'YYYY')||'-'||to_char(rec_shipment_date.min_date,'MM')||'-01'  ) ; \n\n      index_name := partition_name||'_shipment_id_parameter_id_idx';\n      EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree (\"SHIPMENT_ID\", \"PARAMETER_ID\") TABLESPACE pg_default ' ) ; \n\n      index_name := partition_name||'_lasermark_idx';\n      EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree (\"LASERMARK\" COLLATE pg_catalog.\"default\") TABLESPACE pg_default ' ) ; \n\n      index_name := partition_name||'_shipment_id_idx';\n      EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree (\"SHIPMENT_ID\") TABLESPACE pg_default ' ) ; \n\n      index_name := partition_name||'_parameter_id_idx';\n      EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree (\"PARAMETER_ID\") TABLESPACE pg_default ' ) ; \n\n      index_name := partition_name||'_shipment_date_idx';\n      EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree (\"SHIPMENT_DATE\") TABLESPACE pg_default ' ) ; \n\nend\n$$;<\/code><\/pre>\n
      R\u00e9sultats finaux :<\/h3>\n
      \nIl y avait<\/b>
      \nCo\u00fbt :<\/b> 502 997.55
      \nTemps d'ex\u00e9cution<\/b>: 505 secondes.<\/p>\n
      Il est devenu<\/b>
      \nCo\u00fbt :<\/b> 68 533.70
      \nTemps d'ex\u00e9cution :<\/b> 69 secondes<\/p>\n
      C'est respectable, tout \u00e0 fait respectable. Et \u00e9tant donn\u00e9 que nous avons r\u00e9ussi \u00e0 ma\u00eetriser plus ou moins le m\u00e9canisme de partitionnement dans PostgreSQL 10 en cours de route \u2014 c'est un excellent r\u00e9sultat.<\/p>\n
      Parenth\u00e8se lyrique<\/h2>\n
      \nEt on peut encore faire mieux \u2014 OUI, ON PEUT !<\/b>Pour cela, il faut utiliser une VIEW MAT\u00c9RIALIS\u00c9E.
      \nCREATE MATERIALIZED VIEW LASERMARK_VIEW<\/b><\/p>\n
      CREATE MATERIALIZED VIEW LASERMARK_VIEW \nAS\nSELECT w.\"LASERMARK\" , MAX(s.\"SHIPMENT_DATE\") AS \"SHIPMENT_DATE\"\nFROM shipment s INNER JOIN data w ON s.\"SHIPMENT_ID\" = w.\"SHIPMENT_ID\" \nGROUP BY w.\"LASERMARK\" ;\n\nCREATE INDEX lasermark_vw_shipment_date_ind on lasermark_view USING btree (\"SHIPMENT_DATE\") TABLESPACE pg_default;\nanalyze lasermark_view ;\n<\/code><\/pre>\n
      \nUne fois de plus, nous r\u00e9\u00e9crivons la requ\u00eate :
      \nRequ\u00eate avec utilisation de la vue mat\u00e9rialis\u00e9e<\/b><\/p>\n
      S\u00c9LECTIONNER\n            p.\"PARAMETER_ID\" comme parameter_id,\n            pc.\"PC_NAME\" comme pc_name,\n            pc.\"CUSTOMER_PARTNUMBER\" comme customer_partnumber,\n            w.\"LASERMARK\" comme lasermark,\n            w.\"LOTID\" comme lotid,\n            w.\"REPORTED_VALUE\" comme reported_value,\n            w.\"LOWER_SPEC_LIMIT\" comme lower_spec_limit,\n            w.\"UPPER_SPEC_LIMIT\" comme upper_spec_limit,\n            p.\"TYPE_CALCUL\" comme type_calcul,\n            s.\"SHIPMENT_NAME\" comme shipment_name,\n            s.\"SHIPMENT_DATE\" comme shipment_date,\n            extraire(ann\u00e9e de s.\"SHIPMENT_DATE\") comme year,\n            extraire(mois de s.\"SHIPMENT_DATE\") comme month,\n            s.\"REPORT_NAME\" comme report_name,\n            p.\"STC_NAME\" comme STC_name,\n            p.\"CUSTOMERPARAM_NAME\" comme customerparam_name\n        \u00c0 PARTIR DE data w INNER JOIN shipment s ON s.\"SHIPMENT_ID\" = w.\"SHIPMENT_ID\"\n             INNER JOIN parameters p ON p.\"PARAMETER_ID\" = w.\"PARAMETER_ID\"\n             INNER JOIN shipment_pc sp ON s.\"SHIPMENT_ID\" = sp.\"SHIPMENT_ID\"\n             INNER JOIN pc pc ON pc.\"PC_ID\" = sp.\"PC_ID\"\n             INNER JOIN LASERMARK_VIEW md ON md.\"SHIPMENT_DATE\" = s.\"SHIPMENT_DATE\" ET md.\"LASERMARK\" = w.\"LASERMARK\"\n        O\u00d9 \n              s.\"SHIPMENT_DATE\" >= '2018-07-01' ET s.\"SHIPMENT_DATE\" <= '2018-09-30';\n<\/code><\/pre>\n
      \nEt nous avons encore un r\u00e9sultat :<\/b>
      \nIl y avait<\/b>
      \nCo\u00fbt :<\/b> 502 997.55
      \nTemps d'ex\u00e9cution<\/b>: 505 secondes<\/p>\n
      Il est devenu<\/b>
      \nCo\u00fbt :<\/b> 42 481.16
      \nTemps d'ex\u00e9cution :<\/b> 43 secondes.<\/p>\n
      Bien s\u00fbr, un r\u00e9sultat aussi prometteur peut \u00eatre trompeur, il faut rafra\u00eechir les id\u00e9es. Donc, le temps final pour obtenir les donn\u00e9es n'aidera pas beaucoup. Mais c'est assez int\u00e9ressant en tant qu'exp\u00e9rience.<\/p>\n
      En fait, comme il s'est av\u00e9r\u00e9, encore merci asmm<\/a><\/noindex> et \u00e0 Habr !- la requ\u00eate peut encore \u00eatre am\u00e9lior\u00e9e. <\/a><\/noindex><\/p>\n
      Postface<\/h2>\n
      \nDonc, le client est satisfait. Et doit <\/b>profiter de la situation. <\/p>\n
      Nouvelle t\u00e2che<\/b>: Que peut-on imaginer pour approfondir et \u00e9largir ?<\/p>\n
      Et ici je me souviens - les gars, nous n'avons pas de surveillance de nos bases de donn\u00e9es PostgreSQL.<\/p>\n
      Pour \u00eatre honn\u00eate, il y a en fait une certaine surveillance sous la forme de Cloud Watch sur AWS. Mais quelle est l'utilit\u00e9 de cette surveillance pour le DBA ? En gros, aucune.<\/p>\n
      Si l'occasion se pr\u00e9sente de faire quelque chose d'utile et d'int\u00e9ressant pour soi-m\u00eame, on ne peut pas laisser passer une telle chance\u2026
      \nCAR
      \n<\/b>
      \n\"Happy
      \n
      \nEt c'est ainsi que nous en sommes arriv\u00e9s au plus int\u00e9ressant :<\/p>\n
      3 D\u00e9cembre 2018.<\/b>
      \nPrise de d\u00e9cision sur le d\u00e9but des travaux d'exploration des possibilit\u00e9s de surveillance des performances des requ\u00eates PostgreSQL.\n<\/p><\/blockquote>\n
      Mais c'est d\u00e9j\u00e0 une toute autre histoire.<\/b><\/p>\n
      La suite, bient\u00f4t\u2026<\/i>
      \n
      Source :       habr.com<\/a><\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"
      \u041f\u0440\u0435\u0434\u0438\u0441\u043b\u043e\u0432\u0438\u0435 \u0438\u043b\u0438 \u043a\u0430\u043a \u0432\u043e\u0437\u043d\u0438\u043a\u043b\u0430 \u0438\u0434\u0435\u044f \u0441\u0435\u043a\u0446\u0438\u043e\u043d\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u044f \u041d\u0430\u0447\u0430\u043b\u043e \u0438\u0441\u0442\u043e\u0440\u0438\u0438 \u0437\u0434\u0435\u0441\u044c: \u0422\u044b \u043f\u043e\u043c\u043d\u0438\u0448\u044c, \u043a\u0430\u043a \u0432\u0441\u0435 \u043d\u0430\u0447\u0438\u043d\u0430\u043b\u043e\u0441\u044c. \u0412\u0441\u0435 \u0431\u044b\u043b\u043e \u0432\u043f\u0435\u0440\u0432\u044b\u0435 \u0438 \u0432\u043d\u043e\u0432\u044c. \u041f\u043e\u0441\u043b\u0435 \u0442\u043e\u0433\u043e, \u043a\u0430\u043a \u043f\u043e\u0447\u0442\u0438 \u0432\u0441\u0435 \u0440\u0435\u0441\u0443\u0440\u0441\u044b \u0434\u043b\u044f \u043e\u043f\u0442\u0438\u043c\u0438\u0437\u0430\u0446\u0438\u0438 \u0437\u0430\u043f\u0440\u043e\u0441\u0430, \u043d\u0430 \u0442\u043e\u0442 \u043c\u043e\u043c\u0435\u043d\u0442, \u0431\u044b\u043b\u0438 \u0438\u0441\u0447\u0435\u0440\u043f\u0430\u043d\u044b, \u0432\u0441\u0442\u0430\u043b \u0432\u043e\u043f\u0440\u043e\u0441 \u2014 \u0430 \u0447\u0442\u043e \u0436\u0435 \u0434\u0430\u043b\u044c\u0448\u0435? \u0422\u0430\u043a \u0438 \u0432\u043e\u0437\u043d\u0438\u043a\u043b\u0430 \u0438\u0434\u0435\u044f \u043e \u0441\u0435\u043a\u0446\u0438\u043e\u043d\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u0438. \u041b\u0438\u0440\u0438\u0447\u0435\u0441\u043a\u043e\u0435 \u043e\u0442\u0441\u0442\u0443\u043f\u043b\u0435\u043d\u0438\u0435: \u0418\u043c\u0435\u043d\u043d\u043e ‘\u043d\u0430 \u0442\u043e\u0442 \u043c\u043e\u043c\u0435\u043d\u0442’, \u043f\u043e\u0442\u043e\u043c\u0443, \u0447\u0442\u043e \u043a\u0430\u043a […]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":23765,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-31905","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\n","aioseo_head_json":{"title":"\ud83e\udd47Happy Party ou quelques lignes de souvenirs sur la d\u00e9couverte de la partition dans PostgreSQL10 | ProHoster","description":"Pr\u00e9face ou comment l'id\u00e9e de la partition a \u00e9merg\u00e9. Le d\u00e9but de l'histoire ici : Tu te souviens comment tout a commenc\u00e9.","canonical_url":"https:\/\/prohoster.info\/fr\/blog\/administrirovanie\/happy-party-ili-para-strok-vospominanij-o-znakomstve-s-sektsionirovaniem-v-postgresql10","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"fr_FR","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47Happy Party \u0438\u043b\u0438 \u043f\u0430\u0440\u0430 \u0441\u0442\u0440\u043e\u043a-\u0432\u043e\u0441\u043f\u043e\u043c\u0438\u043d\u0430\u043d\u0438\u0439 \u043e \u0437\u043d\u0430\u043a\u043e\u043c\u0441\u0442\u0432\u0435 \u0441 \u0441\u0435\u043a\u0446\u0438\u043e\u043d\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u0435\u043c \u0432 PostgreSQL10 | ProHoster","og:description":"\u041f\u0440\u0435\u0434\u0438\u0441\u043b\u043e\u0432\u0438\u0435 \u0438\u043b\u0438 \u043a\u0430\u043a \u0432\u043e\u0437\u043d\u0438\u043a\u043b\u0430 \u0438\u0434\u0435\u044f \u0441\u0435\u043a\u0446\u0438\u043e\u043d\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u044f \u041d\u0430\u0447\u0430\u043b\u043e \u0438\u0441\u0442\u043e\u0440\u0438\u0438 \u0437\u0434\u0435\u0441\u044c: \u0422\u044b \u043f\u043e\u043c\u043d\u0438\u0448\u044c, \u043a\u0430\u043a \u0432\u0441\u0435 \u043d\u0430\u0447\u0438\u043d\u0430\u043b\u043e\u0441\u044c.","og:url":"https:\/\/prohoster.info\/fr\/blog\/administrirovanie\/happy-party-ili-para-strok-vospominanij-o-znakomstve-s-sektsionirovaniem-v-postgresql10","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2019-10-31T18:43:52+00:00","article:modified_time":"2019-10-31T18:43:52+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"31905","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":"2026-01-21 08:22:19","breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-03-01 03:09:25","updated":"2026-01-21 08:22:19","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/fr\/wp-json\/wp\/v2\/posts\/31905","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/fr\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/fr\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/fr\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/fr\/wp-json\/wp\/v2\/comments?post=31905"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/fr\/wp-json\/wp\/v2\/posts\/31905\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/fr\/wp-json\/wp\/v2\/media\/23765"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/fr\/wp-json\/wp\/v2\/media?parent=31905"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/fr\/wp-json\/wp\/v2\/categories?post=31905"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/fr\/wp-json\/wp\/v2\/tags?post=31905"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}