{"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\/et\/blog\/administrirovanie\/happy-party-ili-para-strok-vospominanij-o-znakomstve-s-sektsionirovaniem-v-postgresql10","title":{"rendered":"Happy Party v\u00f5i paar rida meenutusi PostgreSQL10 sektsioonide tundma\u00f5ppimisest","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Eess\u00f5na v\u00f5i kuidas tekkis ideed sektsioonideks jagamisest<\/h4>\n

\nLoo algus siit: Kas sa m\u00e4letad, kuidas k\u00f5ik algas? K\u00f5ik oli esmakordselt ja taas.<\/a><\/noindex> P\u00e4rast seda, kui peaaegu k\u00f5ik ressursid p\u00e4ringute optimeerimiseks olid selleks hetkeks ammendatud, tekkis k\u00fcsimus \u2014 mis edasi? Nii tekkis idee sektsioonideks jagamisest. <\/p>\n

\"Happy
\n
\nL\u00fchi\u00fclevaade:<\/b>
\nJust 'at that moment', because nagu selgus, olid veel kasutamata optimeerimise \u0440\u0435\u0437\u0435\u0440\u0432id<\/a><\/noindex>. Ait\u00e4h asmm<\/a><\/noindex> ja Habrule!<\/i><\/p>\n

Nii et, kuidas veel saaks tellijat justkui \u00f5nnelikuks teha, ja samal ajal ka enda oskusi t\u00f5sta? <\/p>\n

Kui k\u00f5ik \u00e4\u00e4rmiselt lihtsaks teha<\/b>, siis on radikaalselt, et midagi andmebaasi j\u00f5udlust parandada, vaid kaks teed:
\n1) Ekstensiivne tee \u2014 suurendame ressursse, muudame konfiguratsiooni;
\n2) Intensiivne tee \u2014 p\u00e4ringute optimeerimine<\/p>\n

Kuna, kordustan, tol hetkel ei olnud selge, mida veel p\u00e4ringus muuta kiirusel kiirusel, valiti tee \u2014 tabelite kujunduse muutmine.<\/b><\/p>\n

Nii et \u2014 tekib peamine k\u00fcsimus \u2014 mida ja kuidas me muudame? <\/b>
\n<\/a><\/noindex><\/p>\n

Algtingimused<\/h2>\n

\nEsiteks, on olemas selline ERD (kuvatakse tinglikult lihtsustatud):
\n\"Happy
\nPeamised omadused:<\/p>\n

    \n
  1. suhted 'paljudele paljudele'<\/li>\n
  2. tabel juba omab potentsiaalset partitsioonimise v\u00f5tit <\/li>\n<\/ol>\n

    Algne p\u00e4ring:<\/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
    \nT\u00e4itev tulemused testandmebaasis:<\/b>
    \nKulu <\/b>: 502 997.55
    \nT\u00e4itev aeg<\/b>: 505 sekundit.<\/p>\n
    Mida me n\u00e4eme? Tavaline p\u00e4ring, ajalise l\u00f5ike j\u00e4rgi. 
    \nTeeme lihtsa loogilise oletuse: kui on ajaviidete valik, siis aitab see meid? \u00d5ige \u2014 sektsioonimine.<\/p>\n
    Mida sektsioneerida?<\/h2>\n
    \nEsmapilgul tundub valik ilmselge \u2014 deklaratiivne sektsioonimine tabelis \"shipment\" v\u00f5tme \"SHIPMENT_DATE\" alusel (liialdades \u2014 l\u00f5puks ei saanud produktsioon siiski nii nagu arvati<\/i>). <\/p>\n
    Kuidas sektsioneerida?<\/h2>\n
    \nSee k\u00fcsimus ei ole ka liiga keeruline. \u00d5nneks on PostgreSQL 10-s n\u00fc\u00fcd inimlik sektsioonimise mehhanism. 
    \nNii et: <\/p>\n
      \n
    1.  Salvestame algse tabeli koopia \u2014 pg_dump source_table<\/i><\/li>\n
    2.  Kustutame algse tabeli \u2014 drop table source_table<\/i><\/li>\n
    3. Loome vanematabeli sektsioonimiseks vahemiku alusel \u2014 create table source_table<\/i><\/li>\n
    4.  Loome sektsioonid \u2014 create table source_table, create index<\/i><\/li>\n
    5.  Imporitame koopia, mille l\u00f5ime 1. sammul \u2014 pg_restore<\/i><\/li>\n<\/ol>\n
      <\/p>\n
      Sektsioonimistooted <\/h2>\n
      \nLihtsuse ja mugavuse t\u00f5ttu on 2., 3., ja 4. samm \u00fchendatud \u00fches skriptis. <\/p>\n
      Nii et: 
      \nSalvestame algse tabeli koopia<\/b><\/p>\n
      pg_dump postgres --file=\/dump\/shipment.dmp --format=c --table=shipment --verbose > \/dump\/shipment.log 2>&1<\/code><\/pre>\n
      \nKustutame algse tabeli + Loome vanematabeli sektsioonimiseks vahemiku alusel + Loome sektsioonid<\/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 'Loo TEMPORARY TABLE A EGUKSUSEDA';\n  CREATE TEMP TABLE tmp_shipment_date as select distinct \"SHIPMENT_DATE\" from shipment order by \"SHIPMENT_DATE\" ;\n\n  RAISE NOTICE 'KUSTUTA 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 'LOO PARTITSIOONID TABELLE 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       --UUED piirid ainult teist korda ja hiljem \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 '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      --Kustuta esmakordne m\u00e4rk\n      first_flag := false ;\n   \n  END LOOP;\n\nend\n$$;<\/code><\/pre>\n
      \nImportimeed 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
      Kontrollime sektsiooni tulemusi<\/h2>\n
      \nMillised on meie tulemused? T\u00e4ielik t\u00e4itmisplaani tekst on suur ja igav, seega v\u00f5ib t\u00e4iesti piirduda l\u00f5plikke numbreid sisaldava kokkuv\u00f5ttega.<\/p>\n
      Oli<\/h3>\n
      \nKulu:<\/b> 502 997.55
      \nT\u00e4itmisaeg:<\/b> 505 sekundi.<\/p>\n
      Kohandatud<\/h3>\n
      \nKulu:<\/b> 77 872.36
      \nT\u00e4itmisaeg:<\/b> 79 sekundi.<\/p>\n
      Aeglane tulemus. Oleme v\u00e4hendanud kulu ja t\u00e4itmise aega. Seega seciooni kasutamine toob oodatud tulemuse ja on \u00fcldiselt \u2014 ilma \u00fcllatusteta. <\/p>\n
      Rahuldada tellijat<\/h2>\n
      \nTestimise tulemused esitati tellijale vaatamiseks. P\u00e4rast tutvumist anti neile m\u00f5ned ootamatud l\u00f5ppotsused: \u201eSuurep\u00e4rane, sektsioneerige tabel \u201edata\u201c.\u201d<\/p>\n
      Jah, aga me uurisime hoopis teist tabelit \u201eshipment\u201c, tabelil \u201edata\u201c ei ole v\u00e4ljakut \u201eSHIPMENT_DATE\u201c.<\/p>\n
      Pole probleemi, lisage, muutke. Peamine on, et tellijat rahuldaks see, mis l\u00f5puks valmib, teostuse \u00fcksikasjad ei ole eriti olulised.<\/p>\n
      Sektsioneerime p\u00f5hit\u00e4valli \u201edata\u201c<\/h2>\n
      \n\u00dcldiselt ei tekkinud mingeid erilisi raskusi. Kuigi sektsioneerimise algoritm on muidugi veidi muutunud.<\/p>\n
      Lisame veeru \u201eSHIPMENT_DATA\u201c tabelisse \u201edata\u201c<\/b><\/p>\n
      psql -h host -U database -d user\n=> ALTER TABLE data ADD COLUMN \"SHIPMENT_DATE\" timestamp without time zone ;<\/code><\/pre>\n
      T\u00e4idame \u201eSHIPMENT_DATA\u201d veeru v\u00e4\u00e4rtused tabelis \u201edata\u201d sama nimega veeru v\u00e4\u00e4rtustega tabelist \u201eshipment\u201d<\/b><\/p>\n
      -----------------------------\n--update_data.sql\n-- andmete uuendamine muudetud tabelisse \"data\" veergude \"shipment_data\" v\u00e4\u00e4rtustest tabelist \"shipment\"\n--versioon 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 'Kokku %',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 'reaha arv = % , kokku %',row_count,total_rows;\n  END LOOP;\n\nend\n$$;<\/code><\/pre>\n
      \nSalvestame tabeli \u201edata\u201d dump'i<\/b><\/p>\n
      pg_dump postgres --file=\/dump\/data.dmp --format=c --table=data --verbose > \/dump\/data.log 2>&1<source<\/code><\/pre>\n
      Loomme uuesti jaotustabeli \u201edata\u201d<\/b><\/p>\n
      --create_partition_data.sql\n--luua tabeli \"wafer data\" partitsioone vahemikuga veeru \"shipment_data\" alusel, mille kestus on \u00fcks kuu\n--versioon 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 'LOOD T\u00c4HTAJALISE TABELI KODANIKU K\u00dcLLUGA';\n  CREATE TEMP TABLE tmp_shipment_date as select distinct \"SHIPMENT_DATE\" from shipment order by \"SHIPMENT_DATE\" ;\n\n\n  RAISE NOTICE 'KUSTUTA TABEL DATA';\n  drop table data cascade ;\n\n\n  RAISE NOTICE 'LOODA PARTITSIOONITUD 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 'LOODA PARTITSIOONID TABELILE 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      --Alguspiiride algus\n      IF   begin_year = '0' THEN\n       RAISE NOTICE '***Alguspiiride alguse seadistamine';\n       first_flag := true ; --esimese kord t\u00e4iustamine\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      -- Kontrolli praegust kuup\u00e4eva piire jooksul, mitte esmakordsel\n\n      RAISE NOTICE 'Praegused andmed = %',to_char( to_date( current_year||'.'||current_month, 'YYYY.MM'), 'YYYY.MM');\n      RAISE NOTICE 'Algusaeg = %',to_char( to_date( begin_year||'.'||begin_month, 'YYYY.MM'), 'YYYY.MM');\n      RAISE NOTICE 'J\u00e4rgnev kuup\u00e4ev = %',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 '***JATKA';\n         CONTINUE ; \n      ELSE\n       --Uued piirid ainult teise ja edasise jaoks \n       RAISE NOTICE '***Uued PIIRID';\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 'PARTITSIOONI NUMBER % , TABELI NIMI =%',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 NIMI =%',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 NIMI =%',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 NIMI =%',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 NIMI =%',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 NIMI =%',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      --Esimese kord t\u00e4iustmine\n      first_flag := false ;\n\n  END LOOP;\nend\n$$;\n<\/code><\/pre>\n
      \nKandideerime etapi 3 jooksul loodud dumpi.<\/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
      \nLoome eraldi sektsiooni vanade andmete jaoks.<\/b><\/p>\n
      ---------------------------------------------------\n--create_partition_for_old_dates.sql\n--partitsioonide loomine vanade kuup\u00e4evade hoidmiseks\n--versioon 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 'Vana kuup\u00e4ev on %',rec_shipment_date.min_date ;\n\n      partition_name := 'data_old_dates'  ;\n\n      RAISE NOTICE 'PARTITSIOONI NIME ON %',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
      L\u00f5ppresults<\/h3>\n
      \nOli<\/b>
      \nKulu:<\/b> 502 997.55
      \nT\u00e4itev aeg<\/b>: 505 sekundit.<\/p>\n
      Kohandatud<\/b>
      \nKulu:<\/b> 68 533.70
      \nT\u00e4itmisaeg:<\/b> 69 sekundit<\/p>\n
      Auhinnas, t\u00e4iesti \u00f5igustatud. Arvestades, et teel suudeti enam-v\u00e4hem omandada sektsioneerimise mehhanismi PostgreSQL 10-s \u2014 suurep\u00e4rane tulemus.<\/p>\n
      L\u00fchi\u00fclevaade<\/h2>\n
      \nCan we make it even better \u2014 YES, WE CAN!<\/b>Selleks tuleb kasutada MATERIALIZED VIEW'i.
      \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
      \nKordame taaskord \u00fcleskutset:
      \nP\u00e4ring kasutades materialized view'i<\/b><\/p>\n
      VALI\n            p.\"PARAMETER_ID\" kui parameter_id,\n            pc.\"PC_NAME\" kui pc_name,\n            pc.\"CUSTOMER_PARTNUMBER\" kui customer_partnumber,\n            w.\"LASERMARK\" kui lasermark,\n            w.\"LOTID\" kui lotid,\n            w.\"REPORTED_VALUE\" kui reported_value,\n            w.\"LOWER_SPEC_LIMIT\" kui lower_spec_limit,\n            w.\"UPPER_SPEC_LIMIT\" kui upper_spec_limit,\n            p.\"TYPE_CALCUL\" kui type_calcul,\n            s.\"SHIPMENT_NAME\" kui shipment_name,\n            s.\"SHIPMENT_DATE\" kui shipment_date,\n            extract(year from s.\"SHIPMENT_DATE\") kui year,\n            extract(month from s.\"SHIPMENT_DATE\") kui month,\n            s.\"REPORT_NAME\" kui report_name,\n            p.\"STC_NAME\" kui STC_name,\n            p.\"CUSTOMERPARAM_NAME\" kui 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
      \nJa saame veel \u00fche kokkuv\u00f5tte:<\/b>
      \nOli<\/b>
      \nKulu:<\/b> 502 997.55
      \nT\u00e4itev aeg<\/b>: 505 sekundit<\/p>\n
      Kohandatud<\/b>
      \nKulu:<\/b> 42 481.16
      \nT\u00e4itmisaeg:<\/b> 43 sekundit.<\/p>\n
      Kuigi muidugi, nii palju lubav tulemus on petlik, arusaamu tuleb v\u00e4rskendada. Seega l\u00f5pptulemus andmete hankimise osas pole just eriti abistav. Kuid katse jaoks on see p\u00e4ris huvitav.<\/p>\n
      Tegelikult, nagu selgus, t\u00e4nu veelkord asmm<\/a><\/noindex> ja Habrule! - p\u00e4ringut saab veel t\u00e4iustada. <\/a><\/noindex><\/p>\n
      J\u00e4rels\u00f5na<\/h2>\n
      \nNii et tellija on rahul. Ja vaja <\/b>kasutada olukorda. <\/p>\n
      Uus \u00fclesanne<\/b>: Mida v\u00f5iks v\u00e4lja m\u00f5elda, et s\u00fcvendada ja laiendada?<\/p>\n
      Ja siis meenub \u2014 poisid, meil ei ole meie PostgreSQL andmebaaside seiretegit.<\/p>\n
      K\u00e4si s\u00fcdamele, mingisugune seire Cloud Watchiga AWS-is tegelikult on. Kuid kui palju kasu on sellest seirest DBA-le? \u00dcldiselt peaaegu mitte midagi.<\/p>\n
      Kui on v\u00f5imalus teha midagi kasulikku ja huvitavat ka enda jaoks, siis ei saa sellist v\u00f5imalust kasutamata j\u00e4tta \u2026
      \nSEEDEL
      \n<\/b>
      \n\"Happy
      \n
      \nNii me j\u00f5udsime k\u00f5ige huvitavamani:<\/p>\n
      3. detsember 2018.<\/b>
      \nOtsus alustada PostgreSQL p\u00e4ringute tulemuslikkuse j\u00e4lgimise v\u00f5imaluste uurimist.\n<\/p><\/blockquote>\n
      Aga see on juba t\u00e4iesti teine \u200b\u200blugu.<\/b><\/p>\n
      To be continued\u2026<\/i>
      \n
      Allikas:       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 v\u00f5i paar rida meenutusi tutvumisest sektsioneerimisega PostgreSQL10 | ProHoster","description":"Eess\u00f5na v\u00f5i kuidas tekkis idee sektsioneerimisest. Loo algus siin: Kas sa m\u00e4letad, kuidas k\u00f5ik algas. K\u00f5ik oli esmakordne ja uus. P\u00e4rast seda, kui k\u00f5ik ressursid p\u00e4ringu optimeerimiseks olid tollal ammendatud, tekkis k\u00fcsimus \u2014 mis siis edasi? Nii tekkis idee sektsioneerimisest. Luuletuslik tagasivaatamine: Just 'tollal', sest nagu","canonical_url":"https:\/\/prohoster.info\/et\/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":"et_EE","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. \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","og:url":"https:\/\/prohoster.info\/et\/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\/et\/wp-json\/wp\/v2\/posts\/31905","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/comments?post=31905"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/posts\/31905\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/media\/23765"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/media?parent=31905"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/categories?post=31905"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/tags?post=31905"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}