{"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\/es\/blog\/administrirovanie\/happy-party-ili-para-strok-vospominanij-o-znakomstve-s-sektsionirovaniem-v-postgresql10","title":{"rendered":"Happy Party o algunas l\u00edneas de recuerdos sobre el conocimiento del particionamiento en PostgreSQL10","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Prefacio o c\u00f3mo surgi\u00f3 la idea de la partici\u00f3n<\/h4>\n

\nEl comienzo de la historia aqu\u00ed: Recuerdas c\u00f3mo empez\u00f3 todo. Era todo nuevo y una vez m\u00e1s.<\/a><\/noindex> Despu\u00e9s de que casi todos los recursos para optimizar la consulta se hubieron agotado en ese momento, surgi\u00f3 la pregunta: \u00bfy ahora qu\u00e9? As\u00ed fue como naci\u00f3 la idea de la partici\u00f3n. <\/p>\n

\"Happy
\n
\nUn inciso l\u00edrico:<\/b>
\nJusto 'en ese momento', porque como se descubri\u00f3, hab\u00eda reservas no utilizadas de optimizaci\u00f3n<\/a><\/noindex>. Gracias asmm<\/a><\/noindex> \u00a1y a Habr!<\/i><\/p>\n

Entonces, \u00bfc\u00f3mo m\u00e1s podemos hacer que el cliente est\u00e9 satisfecho y al mismo tiempo mejorar nuestras habilidades? <\/p>\n

Si simplificamos todo al m\u00e1ximo<\/b>, las v\u00edas radicales para mejorar el rendimiento de la base de datos son solo dos:
\n1) Camino extensivo: aumentamos recursos, cambiamos la configuraci\u00f3n;
\n2) Camino intensivo: optimizaci\u00f3n de consultas<\/p>\n

Dado que, reitero, en ese momento no estaba claro qu\u00e9 m\u00e1s cambiar en la consulta para acelerarla, se eligi\u00f3 el camino de cambiar el dise\u00f1o de las tablas.<\/b><\/p>\n

As\u00ed que surge la pregunta principal: \u00bfqu\u00e9 y c\u00f3mo vamos a cambiar? <\/b>
\n<\/a><\/noindex><\/p>\n

Condiciones iniciales<\/h2>\n

\nPrimero, tenemos este ERD (mostrado de manera simplificada):
\n\"Happy
\nCaracter\u00edsticas principales:<\/p>\n

    \n
  1. relaciones de 'muchos a muchos'<\/li>\n
  2. la tabla ya tiene una clave potencial de partici\u00f3n <\/li>\n<\/ol>\n

    Consulta original:<\/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
    \nResultados de la ejecuci\u00f3n en la base de datos de prueba:<\/b>
    \nCosto <\/b>: 502 997.55
    \nTiempo de ejecuci\u00f3n<\/b>: 505 segundos.<\/p>\n
    \u00bfQu\u00e9 vemos? Una consulta normal, en un rango temporal. 
    \nHacemos una suposici\u00f3n l\u00f3gica simple: si hay una muestra de un corte temporal, \u00bfnos ayudar\u00e1? Correcto, la partici\u00f3n.<\/p>\n
    \u00bfQu\u00e9 particionar?<\/h2>\n
    \nA primera vista, la elecci\u00f3n es obvia: particionar declarativamente la tabla 'shipment' por la clave 'SHIPMENT_DATE' (adelant\u00e1ndonos mucho, el resultado en producci\u00f3n fue un poco diferente<\/i>). <\/p>\n
    \u00bfC\u00f3mo particionar?<\/h2>\n
    \nEsta pregunta tampoco es demasiado complicada. Afortunadamente, en PostgreSQL 10, ahora existe un mecanismo de particionado m\u00e1s amigable. 
    \nAs\u00ed que: <\/p>\n
      \n
    1.  Guardamos el volcado de la tabla original \u2014 pg_dump source_table<\/i><\/li>\n
    2.  Eliminamos la tabla original \u2014 drop table source_table<\/i><\/li>\n
    3. Creamos la tabla padre con particionado por rango \u2014 create table source_table<\/i><\/li>\n
    4.  Creamos las secciones \u2014 create table source_table, create index<\/i><\/li>\n
    5.  Importamos el volcado creado en el paso 1 \u2014 pg_restore<\/i><\/li>\n<\/ol>\n
      <\/p>\n
      Scripts para particionado <\/h2>\n
      \nPara simplificar y facilitar, los pasos 2, 3 y 4 se han combinado en un solo script. <\/p>\n
      As\u00ed que: 
      \nGuardamos el volcado de la tabla original<\/b><\/p>\n
      pg_dump postgres --file=\\\/dump\\\/shipment.dmp --format=c --table=shipment --verbose > \\\/dump\\\/shipment.log 2>&1<\/code><\/pre>\n
      \nEliminamos la tabla original + Creamos la tabla padre con particionado por rango + Creamos las secciones<\/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 'CREAR TABLA TEMPORAL PARA SHIPMENT_DATE';\n  CREATE TEMP TABLE tmp_shipment_date as select distinct \"SHIPMENT_DATE\" from shipment order by \"SHIPMENT_DATE\" ;\n\n  RAISE NOTICE 'ELIMINAR TABLA 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 'CREAR PARTICIONES PARA LA TABLA 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 'NOMBRE DEL \u00cdNDICE =%',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
      \nImportar el volcado<\/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
      Verificamos los resultados de la partici\u00f3n<\/h2>\n
      \n\u00bfQu\u00e9 tenemos al final? El texto completo del plan de ejecuci\u00f3n es largo y aburrido, por lo que se puede limitar a las cifras finales.<\/p>\n
      Era<\/h3>\n
      \nCosto:<\/b> 502 997.55
      \nTiempo de ejecuci\u00f3n:<\/b> 505 segundos.<\/p>\n
      Se volvi\u00f3<\/h3>\n
      \nCosto:<\/b> 77 872.36
      \nTiempo de ejecuci\u00f3n:<\/b> 79 segundos.<\/p>\n
      Es un buen resultado. Hemos reducido el costo y el tiempo de ejecuci\u00f3n. As\u00ed, el uso de la partici\u00f3n da el efecto esperado y, en general, sin sorpresas. <\/p>\n
      Alegrar al cliente<\/h2>\n
      \nLos resultados de las pruebas fueron presentados al cliente para su revisi\u00f3n. Y despu\u00e9s de su examen, se emiti\u00f3 un veredicto algo inesperado: 'Excelente, seccione la tabla \u00abdata\u00bb.<\/p>\n
      S\u00ed, pero investigamos una tabla diferente, \u00abshipment\u00bb, la tabla \u00abdata\u00bb no tiene el campo \u00abSHIPMENT_DATE\u00bb.<\/p>\n
      No hay problema, a\u00f1adan, cambien. Lo principal es que al cliente le satisfaga el resultado, los detalles de implementaci\u00f3n no son tan importantes.<\/p>\n
      Seccionamos la tabla principal \u00abdata\u00bb<\/h2>\n
      \nEn general, no surgieron dificultades especiales. Aunque, el algoritmo de partici\u00f3n, por supuesto, ha cambiado un poco.<\/p>\n
      A\u00f1adimos la columna \u00abSHIPMENT_DATA\u00bb en la tabla \u00abdata\u00bb<\/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
      Rellenamos los valores de la columna \u00abSHIPMENT_DATA\u00bb en la tabla \u00abdata\u00bb con los valores de la columna hom\u00f3nima de la tabla \u00abshipment\u00bb<\/b><\/p>\n
      -----------------------------\n--update_data.sql\n--actualizaci\u00f3n para la tabla \"data\" alterada a los valores de \"shipment_data\" de la tabla \"shipment\"\n--versi\u00f3n 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
      \nGuardamos el volcado de la tabla \u00abdata\u00bb<\/b><\/p>\n
      pg_dump postgres --file=\\\/dump\\\/data.dmp --format=c --table=data --verbose > \\\/dump\\\/data.log 2>&1<\/code><\/pre>\n
      Recreamos la tabla particionada \u00abdata\u00bb<\/b><\/p>\n
      --create_partition_data.sql\n--crear particiones para la tabla \"wafer data\" por rango de la columna \"shipment_data\" con una duraci\u00f3n de un mes\n--versi\u00f3n 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 'CREAR TABLA TEMPORAL PARA 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 'ELIMINAR TABLA data';\n  drop table data cascade;\n\n\n  RAISE NOTICE 'CREAR TABLA PARTICIONADA 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 'CREAR PARTICIONES PARA LA TABLA 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      --Iniciar l\u00edmites\n      IF   begin_year = '0' THEN\n       RAISE NOTICE '***Iniciar l\u00edmites';\n       first_flag := true; --marcador de primera vez\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      -- Comprobar fecha actual en l\u00edmites NO para la primera vez\n\n      RAISE NOTICE 'Fecha actual = %',to_char(to_date(current_year||'.'||current_month, 'YYYY.MM'), 'YYYY.MM');\n      RAISE NOTICE 'Fecha de inicio = %',to_char(to_date(begin_year||'.'||begin_month, 'YYYY.MM'), 'YYYY.MM');\n      RAISE NOTICE 'Siguiente fecha = %',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 '***CONTINUAR';\n         CONTINUE;\n      ELSE\n       --Nuevos l\u00edmites solo para la segunda vez y posteriores\n       RAISE NOTICE '***NUEVOS L\u00cdMITES';\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 'N\u00daMERO DE PARTICI\u00d3N % , NOMBRE DE LA TABLA =%',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 'NOMBRE DEL \u00cdNDICE =%',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 'NOMBRE DEL \u00cdNDICE =%',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 'NOMBRE DEL \u00cdNDICE =%',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 'NOMBRE DEL \u00cdNDICE =%',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 'NOMBRE DEL \u00cdNDICE =%',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      --Eliminar el marcador de primera vez\n      first_flag := false;\n\n  END LOOP;\nend\n$$;\n<\/code><\/pre>\n
      \nEstamos cargando el volcado creado en el paso 3.<\/b><\/p>\n
      pg_restore -h host -u user -d database --data-only --format=c --table=data --verbose data.dmp > data_restore.log 2>&1<\/code><\/pre>\n
      \nCreamos una secci\u00f3n separada para los datos antiguos<\/b><\/p>\n
      ---------------------------------------------------\n--create_partition_for_old_dates.sql\n--crear particiones para mantener fechas antiguas \n--versi\u00f3n 1.0\ndo language plpgsql $$\ndeclare \nrec_shipment_date RECORD ;\npartition_name varchar;\nindex_name varchar;\nbegin\n\n      SELECT min(\"SHIPMENT_DATE\") AS min_date INTO rec_shipment_date from data ;\n\n      RAISE NOTICE 'La fecha antigua es %',rec_shipment_date.min_date ;\n\n      partition_name := 'data_old_dates'  ;\n\n      RAISE NOTICE 'EL NOMBRE DE LA PARTICI\u00d3N ES %',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
      Resultados finales:<\/h3>\n
      \nEra<\/b>
      \nCosto:<\/b> 502 997.55
      \nTiempo de ejecuci\u00f3n<\/b>: 505 segundos.<\/p>\n
      Se volvi\u00f3<\/b>
      \nCosto:<\/b> 68 533.70
      \nTiempo de ejecuci\u00f3n:<\/b> 69 segundos<\/p>\n
      Es bastante bueno, muy bueno. Y considerando que en el camino se logr\u00f3 dominar m\u00e1s o menos el mecanismo de particionamiento en PostgreSQL 10 \u2014 es un excelente resultado.<\/p>\n
      Un desv\u00edo l\u00edrico<\/h2>\n
      \n\u00bfY se puede hacer a\u00fan mejor? \u2014 \u00a1S\u00cd, SE PUEDE!<\/b>Para ello, es necesario utilizar MATERIALIZED VIEW.
      \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
      \nUna vez m\u00e1s, reescribimos la consulta:
      \nConsulta utilizando materialized view<\/b><\/p>\n
      SELECCIONAR\n            p.\"PARAMETER_ID\" como parameter_id,\n            pc.\"PC_NAME\" como pc_name,\n            pc.\"CUSTOMER_PARTNUMBER\" como customer_partnumber,\n            w.\"LASERMARK\" como lasermark,\n            w.\"LOTID\" como lotid,\n            w.\"REPORTED_VALUE\" como reported_value,\n            w.\"LOWER_SPEC_LIMIT\" como lower_spec_limit,\n            w.\"UPPER_SPEC_LIMIT\" como upper_spec_limit,\n            p.\"TYPE_CALCUL\" como type_calcul,\n            s.\"SHIPMENT_NAME\" como shipment_name,\n            s.\"SHIPMENT_DATE\" como shipment_date,\n            extraer(a\u00f1o de s.\"SHIPMENT_DATE\") como a\u00f1o,\n            extraer(mes de s.\"SHIPMENT_DATE\") como mes,\n            s.\"REPORT_NAME\" como report_name,\n            p.\"STC_NAME\" como STC_name,\n            p.\"CUSTOMERPARAM_NAME\" como customerparam_name\n        DE datos w INNER JOIN env\u00edo s EN s.\"SHIPMENT_ID\" = w.\"SHIPMENT_ID\"\n             INNER JOIN par\u00e1metros p EN p.\"PARAMETER_ID\" = w.\"PARAMETER_ID\"\n             INNER JOIN shipment_pc sp EN s.\"SHIPMENT_ID\" = sp.\"SHIPMENT_ID\"\n             INNER JOIN pc pc EN pc.\"PC_ID\" = sp.\"PC_ID\"\n             INNER JOIN LASERMARK_VIEW md EN md.\"SHIPMENT_DATE\" = s.\"SHIPMENT_DATE\" Y md.\"LASERMARK\" = w.\"LASERMARK\"\n        DONDE\n              s.\"SHIPMENT_DATE\" >= '2018-07-01' Y s.\"SHIPMENT_DATE\" <= '2018-09-30';\n<\/code><\/pre>\n
      \nY obtenemos otro resultado:<\/b>
      \nEra<\/b>
      \nCosto:<\/b> 502 997.55
      \nTiempo de ejecuci\u00f3n<\/b>: 505 segundos<\/p>\n
      Se volvi\u00f3<\/b>
      \nCosto:<\/b> 42 481.16
      \nTiempo de ejecuci\u00f3n:<\/b> 43 segundos.<\/p>\n
      Aunque, por supuesto, un resultado tan prometedor es enga\u00f1oso, ya que es necesario actualizar las presentaciones. As\u00ed que el tiempo total para obtener los datos no ayudar\u00e1 mucho. Pero como experimento, es bastante interesante.<\/p>\n
      En realidad, como result\u00f3, gracias otra vez asmm<\/a><\/noindex> y a Habr! - la consulta se puede mejorar a\u00fan m\u00e1s. <\/a><\/noindex><\/p>\n
      P\u00f3scrito<\/h2>\n
      \nAs\u00ed que, el cliente est\u00e1 satisfecho. Y necesitan <\/b>aprovechar la situaci\u00f3n. <\/p>\n
      Nueva tarea<\/b>: \u00bfQu\u00e9 se puede idear para profundizar y ampliar?<\/p>\n
      Y aqu\u00ed recuerdo - chicos, en realidad no tenemos monitoreo de nuestras bases de datos PostgreSQL.<\/p>\n
      Hablando sinceramente, hay cierto monitoreo en forma de Cloud Watch en AWS, pero \u00bfcu\u00e1l es la utilidad de ese monitoreo para un DBA? En general, pr\u00e1cticamente ninguna.<\/p>\n
      Si hay una oportunidad de hacer algo \u00fatil e interesante tambi\u00e9n para uno mismo, no se puede dejar pasar esa oportunidad...
      \nPORQUE
      \n<\/b>
      \n\"Happy
      \n
      \nAs\u00ed fue como llegamos a lo m\u00e1s interesante:<\/p>\n
      3 de diciembre de 2018.<\/b>
      \nDecisi\u00f3n de comenzar los trabajos para investigar las posibilidades existentes de monitoreo del rendimiento de las consultas de PostgreSQL.\n<\/p><\/blockquote>\n
      Pero esa es otra historia por completo.<\/b><\/p>\n
      Continuar\u00e1\u2026<\/i>
      \n
      Fuente:       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 o un par de l\u00edneas-recuerdos sobre el encuentro con la partici\u00f3n en PostgreSQL 10 | ProHoster","description":"Pr\u00f3logo o c\u00f3mo surgi\u00f3 la idea de la partici\u00f3n. El comienzo de la historia aqu\u00ed: Recuerdas c\u00f3mo empez\u00f3 todo.","canonical_url":"https:\/\/prohoster.info\/es\/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":"es_ES","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\/es\/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\/es\/wp-json\/wp\/v2\/posts\/31905","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/es\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/es\/wp-json\/wp\/v2\/comments?post=31905"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/es\/wp-json\/wp\/v2\/posts\/31905\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/es\/wp-json\/wp\/v2\/media\/23765"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/es\/wp-json\/wp\/v2\/media?parent=31905"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/es\/wp-json\/wp\/v2\/categories?post=31905"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/es\/wp-json\/wp\/v2\/tags?post=31905"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}