{"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\/nl\/blog\/administrirovanie\/happy-party-ili-para-strok-vospominanij-o-znakomstve-s-sektsionirovaniem-v-postgresql10","title":{"rendered":"Happy Party of een paar zinnen herinneringen aan het leren van partitionering in PostgreSQL10","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Voorwoord of hoe het idee van partitionering is ontstaan<\/h4>\n

\nHet begin van het verhaal hier: Je herinnert je vast nog hoe alles begon. Alles was nieuw en voor het eerst.<\/a><\/noindex> Nadat bijna alle bronnen voor het optimaliseren van de aanvraag op dat moment uitgeput waren, werd de vraag gesteld \u2014 wat nu? En zo ontstond het idee van partitionering. <\/p>\n

\"Happy
\n
\nLyrische omleiding:<\/b>
\nJuist 'op dat moment', omdat zoals bleek, er ongebruikte optimaliseringsreserves waren<\/a><\/noindex>. Dank je asmm<\/a><\/noindex> en Habr!<\/i><\/p>\n

Dus, hoe kunnen we de klant nog gelukkiger maken en tegelijk onze vaardigheden verbeteren? <\/p>\n

Als we alles tot het uiterste vereenvoudigen<\/b>, zijn er eigenlijk maar twee radicale manieren om de prestaties van de database te verbeteren:
\n1) Extensiepad \u2014 we verhogen de middelen, veranderen de configuratie;
\n2) Intensief pad \u2014 optimalisatie van aanvragen<\/p>\n

Aangezien, herhaal ik, op dat moment al niet duidelijk was wat er nog meer veranderd kon worden in de aanvraag voor versnelling, werd de keuze gemaakt voor het pad \u2014 wijzigingen aan het ontwerp van tabellen.<\/b><\/p>\n

Dus \u2014 de belangrijkste vraag ontstaat \u2014 wat en hoe gaan we veranderen? <\/b>
\n<\/a><\/noindex><\/p>\n

Beginvoorwaarden<\/h2>\n

\nTen eerste, hier is een ERD (in vereenvoudigde vorm weergegeven):
\n\"Happy
\nBelangrijkste kenmerken:<\/p>\n

    \n
  1. relaties 'veel-op-veel'<\/li>\n
  2. de tabel heeft al een potenti\u00eble sleutel voor partitionering <\/li>\n<\/ol>\n

    Oorspronkelijke aanvraag:<\/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
    \nResultaten van de uitvoering op de testdatabase:<\/b>
    \nKosten <\/b>: 502 997.55
    \nUitvoertijd<\/b>: 505 seconden.<\/p>\n
    Wat zien we? Een normale aanvraag, per tijdsinterval. 
    \nWe make a simple logical assumption: if there is a sample of a time slice, will it help us? Correct \u2014 partitioning.<\/p>\n
    What to partition?<\/h2>\n
    \nAt first glance, the choice seems obvious \u2014 declarative partitioning of the 'shipment' table by the 'SHIPMENT_DATE' key (putting it mildly \u2014 in the end it turned out a bit differently in production<\/i>). <\/p>\n
    How to partition?<\/h2>\n
    \nThis question isn't too complicated either. Fortunately, in PostgreSQL 10, there is now a human-readable partitioning mechanism. 
    \nDus: <\/p>\n
      \n
    1.  We save a dump of the original table \u2014 pg_dump source_table<\/i><\/li>\n
    2.  We delete the original table \u2014 drop table source_table<\/i><\/li>\n
    3. We create a parent table with range partitioning \u2014 create table source_table<\/i><\/li>\n
    4.  We create partitions \u2014 create table source_table, create index<\/i><\/li>\n
    5.  We import the dump created in step 1 \u2014 pg_restore<\/i><\/li>\n<\/ol>\n
      <\/p>\n
      Scripts for partitioning <\/h2>\n
      \nFor simplicity and convenience, steps 2, 3, and 4 have been combined into a single script. <\/p>\n
      Dus: 
      \nWe save a dump of the original table<\/b><\/p>\n
      pg_dump postgres --file=\\\/dump\\\/shipment.dmp --format=c --table=shipment --verbose > \\\/dump\\\/shipment.log 2>&1<\/code><\/pre>\n
      \nWe delete the original table + create a parent table with range partitioning + create partitions<\/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 'CRE\u00cbER T\u00c9MPOR\u00c6RE TABEL VOOR SHIPMENT_DATE';\n  CREATE TEMP TABLE tmp_shipment_date as select distinct \"SHIPMENT_DATE\" from shipment order by \"SHIPMENT_DATE\" ;\n\n  RAISE NOTICE 'VERWIJDER TABEL 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 'CRE\u00cbER PARTITIES VOOR TABEL 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       --NIEUWE grenzen alleen voor tweede en volgende keren \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('CRE\u00cbER TABEL ' || 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 'INDEX NAAM =%',index_name;\n      EXECUTE format('CRE\u00cbER INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_ID") TABLESPACE pg_default ' ) ; \n\n      --Verwijder vlag voor eerste keer\n      first_flag := false ;\n   \n  END LOOP;\n\nend\n$$;<\/code><\/pre>\n
      \nImporteer 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
      Controleer de resultaten van partitie<\/h2>\n
      \nWat hebben we uiteindelijk? De volledige uitvoeringsplan is groot en saai, dus we kunnen ons beperken tot de eindcijfers.<\/p>\n
      Was<\/h3>\n
      \nKosten:<\/b> 502 997.55
      \nUitvoeringstijd:<\/b> 505 seconden.<\/p>\n
      Is<\/h3>\n
      \nKosten:<\/b> 77 872.36
      \nUitvoeringstijd:<\/b> 79 seconden.<\/p>\n
      Een heel goed resultaat. We hebben zowel de kosten als de uitvoeringstijd verlaagd. Het gebruik van partitionering geeft dus het verwachte effect en over het geheel genomen \u2014 zonder verrassingen. <\/p>\n
      De klant tevreden stellen<\/h2>\n
      \nDe testresultaten werden aan de klant gepresenteerd ter overweging. En na kennisneming werd een behoorlijk onverwacht oordeel gegeven: 'Uitstekend, partitioneer de tabel \"data\"'.<\/p>\n
      Ja, maar we hebben een totaal andere tabel \"shipment\" onderzocht, de tabel \"data\" heeft geen veld \"SHIPMENT_DATE\".<\/p>\n
      Geen probleem, voeg toe, wijzig. Het belangrijkste is dat de klant tevreden is met wat er uit voortkomt; de details van de uitvoering zijn niet zo belangrijk.<\/p>\n
      We partitioneren de hoofdtafel \"data\"<\/h2>\n
      \nEr zijn in feite geen bijzondere complicaties ontstaan. Hoewel het algoritme voor partitionering natuurlijk enigszins is veranderd.<\/p>\n
      Voeg de kolom \"SHIPMENT_DATA\" toe aan de tabel \"data\"<\/b><\/p>\n
      psql -h host -U database -d gebruiker\n=> ALTER TABLE data ADD COLUMN \"SHIPMENT_DATE\" timestamp without time zone ;<\/code><\/pre>\n
      Vul de waarden van de kolom \"SHIPMENT_DATA\" in de tabel \"data\" met de overeenkomende waarden uit de tafel \"shipment\"<\/b><\/p>\n
      -----------------------------\n--update_data.sql\n--bijwerken voor de gewijzigde tabel \"data\" met waarden van \"shipment_data\" uit de tabel \"shipment\"\n--versie 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 'Totaal %',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 'aantal rijen = % , van %',row_count,total_rows;\n  END LOOP;\n\nend\n$$;<\/code><\/pre>\n
      \nBewaar de dump van de tabel \"data\"<\/b><\/p>\n
      pg_dump postgres --file=\\\/dump\\\/data.dmp --format=c --table=data --verbose > \\\/dump\\\/data.log 2>&1<\\\/source<\/code><\/pre>\n
      Hercre\u00eber de gepartitioneerde tabel \"data\"<\/b><\/p>\n
      --create_partition_data.sql\n--maak partities voor de tabel \"wafer data\" op basis van de kolom \"shipment_data\" met een looptijd van \u00e9\u00e9n maand\n--versie 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 'CRE\u00cbER TICKETTABEL VOOR 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 'VERWIJDER TABEL data';\n  drop table data cascade ;\n\n\n  RAISE NOTICE 'CRE\u00cbER GEDEELDE TABEL 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 'CRE\u00cbER PARTITIES VOOR DE TABEL 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 grenzen\n      IF   begin_year = '0' THEN\n       RAISE NOTICE '***Init grenzen';\n       first_flag := true ; --eerste keer vlag\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      -- Controleer de huidige datum binnen de grenzen NIET voor de eerste keer\n\n      RAISE NOTICE 'Huidige 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 'Volgende 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 '***GA DOOR';\n         CONTINUE ; \n      ELSE\n       --NIEUWE grenzen alleen voor de tweede en daaropvolgende keren \n       RAISE NOTICE '***NIEUWE GRENZEN';\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 'PARTITIE NUMMER % , TAFELNAAM =%',i , partition_name;\n      \n      EXECUTE format('CRE\u00cbER TABEL ' || quote_ident(partition_name) || ' PARTITION VAN data VOOR WAARDEN VAN ( %L ) TOT ( %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 NAAM =%',index_name;\n      EXECUTE format('CRE\u00cbER INDEX ' || quote_ident(index_name) || ' OP '|| quote_ident(partition_name) ||' MET b-tree ("SHIPMENT_ID", "PARAMETER_ID") TABLESPACE pg_default ' ) ; \n\n      index_name := partition_name||'_lasermark_idx';\n      RAISE NOTICE 'INDEX NAAM =%',index_name;\n      EXECUTE format('CRE\u00cbER INDEX ' || quote_ident(index_name) || ' OP '|| quote_ident(partition_name) ||' MET b-tree ("LASERMARK" COLLATE pg_catalog."default") TABLESPACE pg_default ' ) ; \n\n      index_name := partition_name||'_shipment_id_idx';\n      RAISE NOTICE 'INDEX NAAM =%',index_name;\n      EXECUTE format('CRE\u00cbER INDEX ' || quote_ident(index_name) || ' OP '|| quote_ident(partition_name) ||' MET b-tree ("SHIPMENT_ID") TABLESPACE pg_default ' ) ; \n\n      index_name := partition_name||'_parameter_id_idx';\n      RAISE NOTICE 'INDEX NAAM =%',index_name;\n      EXECUTE format('CRE\u00cbER INDEX ' || quote_ident(index_name) || ' OP '|| quote_ident(partition_name) ||' MET b-tree ("PARAMETER_ID") TABLESPACE pg_default ' ) ; \n\n      index_name := partition_name||'_shipment_date_idx';\n      RAISE NOTICE 'INDEX NAAM =%',index_name;\n      EXECUTE format('CRE\u00cbER INDEX ' || quote_ident(index_name) || ' OP '|| quote_ident(partition_name) ||' MET b-tree ("SHIPMENT_DATE") TABLESPACE pg_default ' ) ; \n\n      --Verwijder eerste keer vlag\n      first_flag := false ;\n\n  END LOOP;\nend\n$$;\n<\/code><\/pre>\n
      \nWe laden de dump die in stap 3 is gemaakt.<\/b><\/p>\n
      pg_restore -h host -user -d database --data-only --format=c --table=data --verbose data.dmp > data_restore.log 2>&1<\/code><\/pre>\n
      \nWe cre\u00ebren een aparte sectie voor oude gegevens<\/b><\/p>\n
      ---------------------------------------------------\n--create_partition_for_old_dates.sql\n--maak partities om oude data te behouden\n--versie 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 'Oude datum is %', rec_shipment_date.min_date;\n\n      partition_name := 'data_old_dates';\n\n      RAISE NOTICE 'PARTITIE NAAM IS %', 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
      Eindresultaten:<\/h3>\n
      \nWas<\/b>
      \nKosten:<\/b> 502 997.55
      \nUitvoertijd<\/b>: 505 seconden.<\/p>\n
      Is<\/b>
      \nKosten:<\/b> 68 533.70
      \nUitvoeringstijd:<\/b> 69 seconden<\/p>\n
      Verdiend, heel verdienstelijk. En gezien het feit dat we enigszins de mechanica van partitionering in PostgreSQL 10 onder de knie hebben weten te krijgen - Geweldig resultaat.<\/p>\n
      Lyrische uitweiding<\/h2>\n
      \nEn het kan nog beter - JA, HET KAN!<\/b>Hiervoor moet je MATERIALIZED VIEW gebruiken.
      \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
      \nWe herschrijven de query opnieuw:
      \nQuery met behulp van materialized view<\/b><\/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.\"STC_NAME\" AS STC_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 LASERMARK_VIEW 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
      \nEn we krijgen nog een resultaat:<\/b>
      \nWas<\/b>
      \nKosten:<\/b> 502 997.55
      \nUitvoertijd<\/b>: 505 seconden<\/p>\n
      Is<\/b>
      \nKosten:<\/b> 42 481.16
      \nUitvoeringstijd:<\/b> 43 seconden.<\/p>\n
      Hoewel dit veelbelovende resultaat misleidend is, moeten de weergaven worden ververst. Dus de uiteindelijke tijd om gegevens te verkrijgen zal niet veel helpen. Maar als experiment is het zeker interessant.<\/p>\n
      In feite, zoals het bleek, nogmaals bedankt asmm<\/a><\/noindex> en Habr! - de aanvraag kan nog verbeterd worden. <\/a><\/noindex><\/p>\n
      Naschrift<\/h2>\n
      \nDus, de klant is tevreden. En moet <\/b>gebruik maken van de situatie. <\/p>\n
      Nieuwe taak<\/b>: Wat moeten we bedenken om te verdiepen en uit te breiden?<\/p>\n
      En dan herinnert men zich - jongens, we hebben geen monitoring van onze PostgreSQL-databases.<\/p>\n
      Eerlijk gezegd is er wel een soort monitoring in de vorm van Cloud Watch op AWS. Maar wat heeft deze monitoring voor DBA eigenlijk voor zin? Bijna niets.<\/p>\n
      Als er een kans is om iets nuttigs en interessants voor jezelf te doen, kun je zo'n kans niet laten liggen ...
      \nOMDAT
      \n<\/b>
      \n\"Happy
      \n
      \nZo zijn we aangekomen bij het meest interessante:<\/p>\n
      3 december 2018.<\/b>
      \nBesluit over de start van het werk voor het onderzoeken van de beschikbare mogelijkheden voor het monitoren van de prestaties van PostgreSQL-queries.\n<\/p><\/blockquote>\n
      Maar dit is al een heel ander verhaal.<\/b><\/p>\n
      Vervolg volgt...<\/i>
      \n
      Bron:       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\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 of een paar zinnen ter herinnering aan de kennismaking met partitionering in PostgreSQL10 | ProHoster","description":"Voorwoord of hoe het idee van partitionering ontstond Het begin van het verhaal hier: Je herinnert je hoe het allemaal begon.","canonical_url":"https:\/\/prohoster.info\/nl\/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":"nl_NL","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\/nl\/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\/nl\/wp-json\/wp\/v2\/posts\/31905","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/nl\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/nl\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/nl\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/nl\/wp-json\/wp\/v2\/comments?post=31905"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/nl\/wp-json\/wp\/v2\/posts\/31905\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/nl\/wp-json\/wp\/v2\/media\/23765"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/nl\/wp-json\/wp\/v2\/media?parent=31905"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/nl\/wp-json\/wp\/v2\/categories?post=31905"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/nl\/wp-json\/wp\/v2\/tags?post=31905"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}