🥇Festa zoriontsua edo Pare bat oroitzapen lerroa PostgreSQL-en partizioa ezagutzeari buruz10

Hitzaurrea edo nola sortu zen sekzioaren ideia

Istorioa hemen hasten da: Gogoratzen al zara nola hasi zen dena. Dena izan zen lehen aldiz eta berriro. Une hartan eskaera optimizatzeko ia baliabide guztiak agortu ondoren, galdera sortu zen: zer? Horrela sortu zen zatiketaren ideia.

Digresio lirikoa:
Hain zuzen, 'momentu horretan', zeren ondorioztatu zenez, ustiatu gabeko optimizazio-erreserbak zeuden... eskerrak asmm eta Habru!

Beraz, bestela nola egin dezakezu bezeroa pozik eta, aldi berean, zure gaitasunak hobetu?

Dena ahalik eta gehien sinplifikatzeko, orduan datu-basearen errendimenduan zerbait errotik hobetzeko bi modu baino ez daude:
1) Bide zabala - baliabideak handitzen ditugu, konfigurazioa aldatzen dugu;
2) Bide trinkoa - kontsultaren optimizazioa

Berriro diot, orduan jada ez zegoenez argi zer gehiago aldatu bizkortzeko eskaeran, bidea aukeratu zen - mahaiaren diseinu aldaketak.

Beraz, galdera nagusia sortzen da: zer eta nola aldatuko dugu?

Hasierako baldintzak

Lehenik eta behin, ERD hau dago (baldintza modu sinplifikatuan erakusten da):

Ezaugarri nagusiak:

askoren arteko harremanak
taulak dagoeneko badu balizko partizio-gako bat

Jatorrizko eskaera:

SELECT
            p."PARAMETER_ID" as  parameter_id,
            pc."PC_NAME" AS pc_name,
            pc."CUSTOMER_PARTNUMBER" AS customer_partnumber,
            w."LASERMARK" AS lasermark,
            w."LOTID" AS lotid,
            w."REPORTED_VALUE" AS reported_value,
            w."LOWER_SPEC_LIMIT" AS lower_spec_limit,
            w."UPPER_SPEC_LIMIT" AS upper_spec_limit,
            p."TYPE_CALCUL" AS type_calcul,
            s."SHIPMENT_NAME" AS shipment_name,
            s."SHIPMENT_DATE" AS shipment_date,
            extract(year from s."SHIPMENT_DATE") AS year,
            extract(month from s."SHIPMENT_DATE") as month,
            s."REPORT_NAME" AS report_name,
            p."SPARAM_NAME" AS SPARAM_name,
            p."CUSTOMERPARAM_NAME" AS customerparam_name
        FROM data w INNER JOIN shipment s ON s."SHIPMENT_ID" = w."SHIPMENT_ID"
             INNER JOIN parameters p ON p."PARAMETER_ID" = w."PARAMETER_ID"
             INNER JOIN shipment_pc sp ON s."SHIPMENT_ID" = sp."SHIPMENT_ID"
             INNER JOIN pc pc ON pc."PC_ID" = sp."PC_ID"
             INNER JOIN ( SELECT w2."LASERMARK" , MAX(s2."SHIPMENT_DATE") AS "SHIPMENT_DATE"
                          FROM shipment s2 INNER JOIN data w2 ON s2."SHIPMENT_ID" = w2."SHIPMENT_ID" 
                          GROUP BY w2."LASERMARK"
                         ) md ON md."SHIPMENT_DATE" = s."SHIPMENT_DATE" AND md."LASERMARK" = w."LASERMARK"
        WHERE 
             s."SHIPMENT_DATE" >= '2018-07-01' AND s."SHIPMENT_DATE" <= '2018-09-30' ;

Proba datu-basearen exekuzioaren emaitzak:
Kostua : 502 997.55
Exekuzio denbora: 505 segundo.

Zer ikusten dugu? Ohiko eskaera, denbora tarte batean oinarrituta.
Egin dezagun hipotesi logikorik errazena: denbora zati baten lagin bat badago, lagunduko al digu? Hori bai - zatiketa.

Zer atalkatu?

Lehen begiratuan, aukera begi-bistakoa da - "bidalketa" taularen deklarazio zatiketa "SHIPMENT_DATE" tekla erabiliz (Aurreraegi jauzi eginez - azkenean apur bat gaizki atera zen ekoizpenean).

Nola banatu?

Galdera hau ere ez da oso zaila. Zorionez, PostgreSQL 10-n, gaur egun giza zatiketa mekanismo bat dago.
Beraz:

Gorde iturburu-taularen iraulketa bat - pg_dump iturburu_taula
Ezabatu jatorrizko taula - jaregin taula iturri_taula
Sortu mahai nagusi bat barruti partizioarekin - sortu taula iturri_taula
Sortu atalak - sortu taula iturri_taula, sortu indizea
Inportatu 1. urratsean sortutako zabortegia - pg_restore

Partizioak egiteko scriptak

Sinpletasuna eta erosotasuna lortzeko, 2,3,4 urratsak script batean konbinatu dira.

Beraz:
Gorde iturburu-taularen iraulketa bat

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

Ezabatu iturburu-taula + Sortu taula nagusi bat barruti partizioarekin + Sortu partizioak

--create_partition_shipment.sql
do language plpgsql $$
declare 
rec_shipment_date RECORD ;
partition_name varchar;
index_name varchar;
current_year varchar ;
current_month varchar ;
begin_year varchar ;
begin_month varchar ;
next_year varchar ;
next_month varchar ;
first_flag boolean ;
i integer ;
begin
  RAISE NOTICE 'CREATE TEMPORARY TABLE FOR SHIPMENT_DATE';
  CREATE TEMP TABLE tmp_shipment_date as select distinct "SHIPMENT_DATE" from shipment order by "SHIPMENT_DATE" ;

  RAISE NOTICE 'DROP TABLE shipment';
  drop table shipment cascade ;
  
  CREATE TABLE public.shipment
  (
    "SHIPMENT_ID" integer NOT NULL DEFAULT nextval('shipment_shipment_id_seq'::regclass),
    "SHIPMENT_NAME" character varying(30) COLLATE pg_catalog."default",
    "SHIPMENT_DATE" timestamp without time zone,
    "REPORT_NAME" character varying(40) COLLATE pg_catalog."default"
  )
  PARTITION BY RANGE ("SHIPMENT_DATE")
  WITH (
      OIDS = FALSE
  )
  TABLESPACE pg_default;

  RAISE NOTICE 'CREATE PARTITIONS FOR TABLE shipment';

  current_year:='0';
  current_month:='0';

  begin_year := '0' ;
  begin_month := '0'  ;
  next_year := '0' ;
  next_month := '0'  ;

  FOR rec_shipment_date IN SELECT * FROM tmp_shipment_date LOOP
      
      RAISE NOTICE 'SHIPMENT_DATE=%',rec_shipment_date."SHIPMENT_DATE";
      
      current_year := date_part('year' ,rec_shipment_date."SHIPMENT_DATE");
      current_month := date_part('month' ,rec_shipment_date."SHIPMENT_DATE") ; 

      IF to_number(current_month,'99') < 10 THEN
        current_month := '0'||current_month ; 
      END IF ;

      --Init borders
      IF   begin_year = '0' THEN
       first_flag := true ; --first time flag
       begin_year := current_year ;
       begin_month := current_month ;   
   
        IF current_month = '12' THEN
          next_year := date_part('year' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 year') ;
        ELSE
          next_year := current_year ;
        END IF;
     
       next_month := date_part('month' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 month') ;

      END IF;

      -- Check current date into borders NOT for First time
      IF to_date( current_year||'.'||current_month, 'YYYY.MM') >= to_date( begin_year||'.'||begin_month, 'YYYY.MM') AND 
         to_date( current_year||'.'||current_month, 'YYYY.MM') < to_date( next_year||'.'||next_month, 'YYYY.MM') AND 
         NOT first_flag 
      THEN
         CONTINUE ; 
      ELSE
       --NEW borders only for second and after time 
       begin_year := current_year ;
       begin_month := current_month ;   
   
        IF current_month = '12' THEN
          next_year := date_part('year' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 year') ;
        ELSE
          next_year := current_year ;
        END IF;
     
       next_month := date_part('month' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 month') ;

      END IF;      

      partition_name := 'shipment_shipment_date_'||begin_year||'-'||begin_month||'-01-'|| next_year||'-'||next_month||'-01'  ;
 
     EXECUTE format('CREATE TABLE ' || quote_ident(partition_name) || ' PARTITION OF shipment FOR VALUES FROM ( %L ) TO ( %L )  ' , current_year||'-'||current_month||'-01' , next_year||'-'||next_month||'-01'  ) ; 

      index_name := partition_name||'_shipment_id_idx';
      RAISE NOTICE 'INDEX NAME =%',index_name;
      EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_ID") TABLESPACE pg_default ' ) ; 

      --Drop first time flag
      first_flag := false ;
   
  END LOOP;

end
$$;

Zabortegia inportatzea

pg_restore -d postgres --data-only --format=c --table=shipment --verbose  shipment.dmp > /tmp/data_dump/shipment_restore.log 2>&1

Banaketa-emaitzak egiaztatzea

Zer dugu ondorioz? Exekuzio-planaren testu osoa handia eta aspergarria da, beraz, oso posible da azken zenbakietara mugatzea.

zen

Cost: 502 997.55
Exekuzio denbora: 505 segundotan.

Bihurtu da

Cost: 77 872.36
Exekuzio denbora: 79 segundotan.

Nahiko emaitza ona. Kostua eta exekuzio denbora murriztua. Horrela, partizioak erabiltzeak espero den efektua ematen du eta, oro har, ezustekorik ez.

Egin bezeroa pozik

Probaren emaitzak bezeroari aurkeztu zizkion berrikusteko. Eta berrikusi ostean, ustekabeko epaia eman zieten: «Ondo, zatitu «datuen» taula».

Bai, baina "bidalketa" taula guztiz desberdina aztertu dugu; "datuak" taulak ez du "SHIPMENT_DATE" eremua.

Ez dago arazorik, gehitu, aldatu. Gauza nagusia da bezeroa pozik egotea emaitzarekin; ezarpenaren xehetasunak ez dira bereziki garrantzitsuak.

Taula nagusia "datuak" zatitzea

Oro har, ez zen zailtasun berezirik sortu. Nahiz eta, noski, zatiketa algoritmoa zertxobait aldatu den.

"SHIPMENT_DATA" zutabea gehitzea "datuak" taulan

psql -h хост -U база -d юзер
=> ALTER TABLE data ADD COLUMN "SHIPMENT_DATE" timestamp without time zone ;

Bete "HIPMENT_DATA" zutabeko balioak "datuak" taulan "shipment" taulako izen bereko zutabearen balioekin

-----------------------------
--update_data.sql
--updating for altered table "data" to values of "shipment_data" from the table "shipment"
--version 1.0
do language plpgsql $$
declare 
rec_shipment_data RECORD ;
shipment_date timestamp without time zone ; 
row_count integer ;
total_rows integer ;
begin

  select count(*) into total_rows from shipment ; 
  RAISE NOTICE 'Total %',total_rows;
  row_count:= 0 ;

  FOR rec_shipment_data IN SELECT * FROM shipment LOOP

   update data set "SHIPMENT_DATE" = rec_shipment_data."SHIPMENT_DATE" where "SHIPMENT_ID" = rec_shipment_data."SHIPMENT_ID";
   
   row_count:=  row_count +1 ;
   RAISE NOTICE 'row count = % , from %',row_count,total_rows;
  END LOOP;

end
$$;

Gorde "datuen" taularen iraulketa

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

Sortu zatitutako taula "datuak"

--create_partition_data.sql
--create partitions for the table "wafer data" by range column "shipment_data" with one month duration
--version 1.0
do language plpgsql $$
declare 
rec_shipment_date RECORD ;
partition_name varchar;
index_name varchar;
current_year varchar ;
current_month varchar ;
begin_year varchar ;
begin_month varchar ;
next_year varchar ;
next_month varchar ;
first_flag boolean ;
i integer ;

begin

  RAISE NOTICE 'CREATE TEMPORARY TABLE FOR SHIPMENT_DATE';
  CREATE TEMP TABLE tmp_shipment_date as select distinct "SHIPMENT_DATE" from shipment order by "SHIPMENT_DATE" ;


  RAISE NOTICE 'DROP TABLE data';
  drop table data cascade ;


  RAISE NOTICE 'CREATE PARTITIONED TABLE data';
  
  CREATE TABLE public.data
  (
    "RUN_ID" integer,
    "LASERMARK" character varying(20) COLLATE pg_catalog."default" NOT NULL,
    "LOTID" character varying(80) COLLATE pg_catalog."default",
    "SHIPMENT_ID" integer NOT NULL,
    "PARAMETER_ID" integer NOT NULL,
    "INTERNAL_VALUE" character varying(75) COLLATE pg_catalog."default",
    "REPORTED_VALUE" character varying(75) COLLATE pg_catalog."default",
    "LOWER_SPEC_LIMIT" numeric,
    "UPPER_SPEC_LIMIT" numeric , 
    "SHIPMENT_DATE" timestamp without time zone
  )
  PARTITION BY RANGE ("SHIPMENT_DATE")
  WITH (
    OIDS = FALSE
  )
  TABLESPACE pg_default ;


  RAISE NOTICE 'CREATE PARTITIONS FOR TABLE data';

  current_year:='0';
  current_month:='0';

  begin_year := '0' ;
  begin_month := '0'  ;
  next_year := '0' ;
  next_month := '0'  ;
  i := 1;

  FOR rec_shipment_date IN SELECT * FROM tmp_shipment_date LOOP
      
      RAISE NOTICE 'SHIPMENT_DATE=%',rec_shipment_date."SHIPMENT_DATE";
      
      current_year := date_part('year' ,rec_shipment_date."SHIPMENT_DATE");
      current_month := date_part('month' ,rec_shipment_date."SHIPMENT_DATE") ; 

      --Init borders
      IF   begin_year = '0' THEN
       RAISE NOTICE '***Init borders';
       first_flag := true ; --first time flag
       begin_year := current_year ;
       begin_month := current_month ;   
   
        IF current_month = '12' THEN
          next_year := date_part('year' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 year') ;
        ELSE
          next_year := current_year ;
        END IF;
     
       next_month := date_part('month' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 month') ;

      END IF;

--      RAISE NOTICE 'current_year=% , current_month=% ',current_year,current_month;
--      RAISE NOTICE 'begin_year=% , begin_month=% ',begin_year,begin_month;
--      RAISE NOTICE 'next_year=% , next_month=% ',next_year,next_month;

      -- Check current date into borders NOT for First time

      RAISE NOTICE 'Current data = %',to_char( to_date( current_year||'.'||current_month, 'YYYY.MM'), 'YYYY.MM');
      RAISE NOTICE 'Begin data = %',to_char( to_date( begin_year||'.'||begin_month, 'YYYY.MM'), 'YYYY.MM');
      RAISE NOTICE 'Next data = %',to_char( to_date( next_year||'.'||next_month, 'YYYY.MM'), 'YYYY.MM');

      IF to_date( current_year||'.'||current_month, 'YYYY.MM') >= to_date( begin_year||'.'||begin_month, 'YYYY.MM') AND 
         to_date( current_year||'.'||current_month, 'YYYY.MM') < to_date( next_year||'.'||next_month, 'YYYY.MM') AND 
         NOT first_flag 
      THEN
         RAISE NOTICE '***CONTINUE';
         CONTINUE ; 
      ELSE
       --NEW borders only for second and after time 
       RAISE NOTICE '***NEW BORDERS';
       begin_year := current_year ;
       begin_month := current_month ;   
   
        IF current_month = '12' THEN
          next_year := date_part('year' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 year') ;
        ELSE
          next_year := current_year ;
        END IF;
     
       next_month := date_part('month' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 month') ;


      END IF;      

      IF to_number(current_month,'99') < 10 THEN
        current_month := '0'||current_month ; 
      END IF ;

      IF to_number(begin_month,'99') < 10 THEN
        begin_month := '0'||begin_month ; 
      END IF ;

      IF to_number(next_month,'99') < 10 THEN
        next_month := '0'||next_month ; 
      END IF ;

      RAISE NOTICE 'current_year=% , current_month=% ',current_year,current_month;
      RAISE NOTICE 'begin_year=% , begin_month=% ',begin_year,begin_month;
      RAISE NOTICE 'next_year=% , next_month=% ',next_year,next_month;

      partition_name := 'data_'||begin_year||begin_month||'01_'||next_year||next_month||'01'  ;

      RAISE NOTICE 'PARTITION NUMBER % , TABLE NAME =%',i , partition_name;
      
      EXECUTE format('CREATE TABLE ' || quote_ident(partition_name) || ' PARTITION OF data FOR VALUES FROM ( %L ) TO ( %L )  ' , begin_year||'-'||begin_month||'-01' , next_year||'-'||next_month||'-01'  ) ; 

      index_name := partition_name||'_shipment_id_parameter_id_idx';
      RAISE NOTICE 'INDEX NAME =%',index_name;
      EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_ID", "PARAMETER_ID") TABLESPACE pg_default ' ) ; 

      index_name := partition_name||'_lasermark_idx';
      RAISE NOTICE 'INDEX NAME =%',index_name;
      EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("LASERMARK" COLLATE pg_catalog."default") TABLESPACE pg_default ' ) ; 

      index_name := partition_name||'_shipment_id_idx';
      RAISE NOTICE 'INDEX NAME =%',index_name;
      EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_ID") TABLESPACE pg_default ' ) ; 

      index_name := partition_name||'_parameter_id_idx';
      RAISE NOTICE 'INDEX NAME =%',index_name;
      EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("PARAMETER_ID") TABLESPACE pg_default ' ) ; 

      index_name := partition_name||'_shipment_date_idx';
      RAISE NOTICE 'INDEX NAME =%',index_name;
      EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_DATE") TABLESPACE pg_default ' ) ; 

      --Drop first time flag
      first_flag := false ;

  END LOOP;
end
$$;

Kargatu 3. urratsean sortutako zabortegia.

pg_restore -h хост -юзер -d база --data-only --format=c --table=data --verbose  data.dmp > data_restore.log 2>&1

Sortu atal bereizi bat datu zaharretarako

---------------------------------------------------
--create_partition_for_old_dates.sql
--create partitions for keeping old dates 
--version 1.0
do language plpgsql $$
declare 
rec_shipment_date RECORD ;
partition_name varchar;
index_name varchar;

begin

      SELECT min("SHIPMENT_DATE") AS min_date INTO rec_shipment_date from data ;

      RAISE NOTICE 'Old date is %',rec_shipment_date.min_date ;

      partition_name := 'data_old_dates'  ;

      RAISE NOTICE 'PARTITION NAME IS %',partition_name;

      EXECUTE format('CREATE TABLE ' || quote_ident(partition_name) || ' PARTITION OF data FOR VALUES FROM ( %L ) TO ( %L )  ' , '1900-01-01' , 
              to_char( rec_shipment_date.min_date,'YYYY')||'-'||to_char(rec_shipment_date.min_date,'MM')||'-01'  ) ; 

      index_name := partition_name||'_shipment_id_parameter_id_idx';
      EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_ID", "PARAMETER_ID") TABLESPACE pg_default ' ) ; 

      index_name := partition_name||'_lasermark_idx';
      EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("LASERMARK" COLLATE pg_catalog."default") TABLESPACE pg_default ' ) ; 

      index_name := partition_name||'_shipment_id_idx';
      EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_ID") TABLESPACE pg_default ' ) ; 

      index_name := partition_name||'_parameter_id_idx';
      EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("PARAMETER_ID") TABLESPACE pg_default ' ) ; 

      index_name := partition_name||'_shipment_date_idx';
      EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_DATE") TABLESPACE pg_default ' ) ; 

end
$$;

Azken emaitzak:

zen
Cost: 502 997.55
Exekuzio denbora: 505 segundo.

Bihurtu da
Cost: 68 533.70
Exekuzio denbora: 69 segundotan

Merezi, nahiko duina. Eta kontuan hartuta bidean PostgreSQL 10-en partizio-mekanismoa gutxi gorabehera menperatzea lortu dugula - emaitza bikaina.

Digresio lirikoa

Posible al da oraindik hobeto egitea - BAI, DEZAKEZU!Horretarako MATERIALIZED VIEW erabili behar duzu.
SORTU IKUSPEGI MATERIALIZATUA LASERMARK_VIEW

CREATE MATERIALIZED VIEW LASERMARK_VIEW 
AS
SELECT w."LASERMARK" , MAX(s."SHIPMENT_DATE") AS "SHIPMENT_DATE"
FROM shipment s INNER JOIN data w ON s."SHIPMENT_ID" = w."SHIPMENT_ID" 
GROUP BY w."LASERMARK" ;

CREATE INDEX lasermark_vw_shipment_date_ind on lasermark_view USING btree ("SHIPMENT_DATE") TABLESPACE pg_default;
analyze lasermark_view ;

Berriro idazten dugu eskaera:
Kontsultatu ikuspegi materializatua erabiliz

SELECT
            p."PARAMETER_ID" as  parameter_id,
            pc."PC_NAME" AS pc_name,
            pc."CUSTOMER_PARTNUMBER" AS customer_partnumber,
            w."LASERMARK" AS lasermark,
            w."LOTID" AS lotid,
            w."REPORTED_VALUE" AS reported_value,
            w."LOWER_SPEC_LIMIT" AS lower_spec_limit,
            w."UPPER_SPEC_LIMIT" AS upper_spec_limit,
            p."TYPE_CALCUL" AS type_calcul,
            s."SHIPMENT_NAME" AS shipment_name,
            s."SHIPMENT_DATE" AS shipment_date,
            extract(year from s."SHIPMENT_DATE") AS year,
            extract(month from s."SHIPMENT_DATE") as month,
            s."REPORT_NAME" AS report_name,
            p."STC_NAME" AS STC_name,
            p."CUSTOMERPARAM_NAME" AS customerparam_name
        FROM data w INNER JOIN shipment s ON s."SHIPMENT_ID" = w."SHIPMENT_ID"
             INNER JOIN parameters p ON p."PARAMETER_ID" = w."PARAMETER_ID"
             INNER JOIN shipment_pc sp ON s."SHIPMENT_ID" = sp."SHIPMENT_ID"
             INNER JOIN pc pc ON pc."PC_ID" = sp."PC_ID"
             INNER JOIN LASERMARK_VIEW md ON md."SHIPMENT_DATE" = s."SHIPMENT_DATE" AND md."LASERMARK" = w."LASERMARK"
        WHERE 
              s."SHIPMENT_DATE" >= '2018-07-01' AND s."SHIPMENT_DATE" <= '2018-09-30';

Eta beste emaitza bat lortzen dugu:
zen
Cost: 502 997.55
Exekuzio denbora: 505 segundo

Bihurtu da
Cost: 42 481.16
Exekuzio denbora: 43 segundotan.

Nahiz eta, noski, halako emaitza itxaropentsu bat engainagarria den; ideiak freskatu behar dira. Beraz, datuak jasotzeko denbora osoa ez du asko lagunduko. Baina esperimentu gisa nahiko interesgarria da.

Egia esan, ondorioztatu zenez, eskerrik asko berriro asmm eta Habru!- Kontsulta gehiago hobetu daiteke.

afterword

Beraz, bezeroa pozik dago. ETA behar egoeraz baliatu.

Zeregin berria: Zer asma dezakezu sakontzeko eta zabaltzeko?

Eta orduan gogoan dut - mutilak, ez dugu gure PostgreSQL datu-baseen jarraipena.

Bihotzean, oraindik ere jarraipena dago Cloud Watch-en AWS-n. Baina zer onura du jarraipen honek DBArentzat? Oro har, ia bat ere ez.

Zuretzako zerbait erabilgarria eta interesgarria egiteko aukera baduzu, ezin duzu aukera hau aprobetxatu...
EGITEKO

Honela iritsiko gara zatirik interesgarrienera:

3ko abenduaren 2018a.
PostgreSQL kontsulten errendimendua kontrolatzeko erabilgarri dauden gaitasunen ikerketak hasteko erabakia hartzea.

Baina hori guztiz bestelako istorio bat da.

Jarraituko du…

Iturria: www.habr.com

Happy Party edo PostgreSQL10-en partizioa ezagutzeari buruzko oroitzapen lerro pare bat