\timing
Timing is on.
\pset pager always
Pager is always used.
DROP TABLE IF EXISTS deliverynotes CASCADE;
psql:testA_prepare_full_brin.sql:1: NOTICE:  table "deliverynotes" does not exist, skipping
DROP TABLE
Time: 0,353 ms
CREATE TEMP TABLE deliverynotes AS (SELECT * FROM albaranescli ORDER BY idalbaran);
SELECT 333694
Time: 632,625 ms
ALTER TABLE deliverynotes SET (fillfactor = 60);
ALTER TABLE
Time: 0,398 ms
ALTER TABLE deliverynotes ADD CONSTRAINT deliverynotes_pkey 
	PRIMARY KEY(idalbaran)  WITH (FILLFACTOR=60);
ALTER TABLE
Time: 954,238 ms
	
CLUSTER deliverynotes USING 	deliverynotes_pkey;
CLUSTER
Time: 1674,489 ms
CREATE INDEX deliverynotes_codagencia_m1_idx
  ON deliverynotes
  USING brin
  (codagencia );
CREATE INDEX
Time: 482,985 ms
CREATE INDEX deliverynotes_codagenciaup_m1_idx
  ON deliverynotes
  USING brin
  (upper(codagencia::text) );
CREATE INDEX
Time: 611,131 ms
CREATE INDEX deliverynotes_codagente2_m1_idx
  ON deliverynotes
  USING brin
  (codagente2 );
CREATE INDEX
Time: 165,700 ms
CREATE INDEX deliverynotes_codagente2up_m1_idx
  ON deliverynotes
  USING brin
  (upper(codagente2::text) );
CREATE INDEX
Time: 173,112 ms
CREATE INDEX deliverynotes_codagente3_m1_idx
  ON deliverynotes
  USING brin
  (codagente3 );
CREATE INDEX
Time: 165,009 ms
CREATE INDEX deliverynotes_codagente3up_m1_idx
  ON deliverynotes
  USING brin
  (upper(codagente3::text) );
CREATE INDEX
Time: 170,268 ms
CREATE INDEX deliverynotes_codagente_m1_idx
  ON deliverynotes
  USING brin
  (codagente );
CREATE INDEX
Time: 267,536 ms
CREATE INDEX deliverynotes_codagenteup_m1_idx
  ON deliverynotes
  USING brin
  (upper(codagente::text) );
CREATE INDEX
Time: 360,547 ms
CREATE INDEX deliverynotes_codalmacen_m1_idx
  ON deliverynotes
  USING brin
  (codalmacen );
CREATE INDEX
Time: 254,597 ms
CREATE INDEX deliverynotes_codalmacenup_m1_idx
  ON deliverynotes
  USING brin
  (upper(codalmacen::text) );
CREATE INDEX
Time: 336,522 ms
CREATE INDEX deliverynotes_codcentro_m1_idx
  ON deliverynotes
  USING brin
  (codcentro );
CREATE INDEX
Time: 165,518 ms
CREATE INDEX deliverynotes_codcentroup_m1_idx
  ON deliverynotes
  USING brin
  (upper(codcentro::text) );
CREATE INDEX
Time: 186,292 ms
CREATE INDEX deliverynotes_codcliente_m1_idx
  ON deliverynotes
  USING brin
  (codcliente );
CREATE INDEX
Time: 295,474 ms
CREATE INDEX deliverynotes_codclienteup_m1_idx
  ON deliverynotes
  USING brin
  (upper(codcliente::text) );
CREATE INDEX
Time: 407,291 ms
CREATE INDEX deliverynotes_codcondicionentrega_m1_idx
  ON deliverynotes
  USING brin
  (codcondicionentrega );
CREATE INDEX
Time: 220,878 ms
CREATE INDEX deliverynotes_codcondicionentregaup_m1_idx
  ON deliverynotes
  USING brin
  (upper(codcondicionentrega::text) );
CREATE INDEX
Time: 250,695 ms
CREATE INDEX deliverynotes_coddir_cobro_m1_idx
  ON deliverynotes
  USING brin
  (coddir_cobro);
CREATE INDEX
Time: 200,364 ms
CREATE INDEX deliverynotes_coddir_envio_m1_idx
  ON deliverynotes
  USING brin
  (coddir_envio);
CREATE INDEX
Time: 198,032 ms
CREATE INDEX deliverynotes_coddir_m1_idx
  ON deliverynotes
  USING brin
  (coddir);
CREATE INDEX
Time: 188,122 ms
CREATE INDEX deliverynotes_coddivisa_m1_idx
  ON deliverynotes
  USING brin
  (coddivisa );
CREATE INDEX
Time: 219,364 ms
CREATE INDEX deliverynotes_coddivisaup_m1_idx
  ON deliverynotes
  USING brin
  (upper(coddivisa::text) );
CREATE INDEX
Time: 323,184 ms
CREATE INDEX deliverynotes_codejercicio_m1_idx
  ON deliverynotes
  USING brin
  (codejercicio );
CREATE INDEX
Time: 394,362 ms
CREATE INDEX deliverynotes_codejercicioup_m1_idx
  ON deliverynotes
  USING brin
  (upper(codejercicio::text) );
CREATE INDEX
Time: 497,430 ms
CREATE INDEX deliverynotes_codimpuestoportes_m1_idx
  ON deliverynotes
  USING brin
  (codimpuestoportes );
CREATE INDEX
Time: 326,516 ms
CREATE INDEX deliverynotes_codimpuestoportesup_m1_idx
  ON deliverynotes
  USING brin
  (upper(codimpuestoportes::text) );
CREATE INDEX
Time: 422,329 ms
CREATE INDEX deliverynotes_codincoterm_m1_idx
  ON deliverynotes
  USING brin
  (codincoterm );
CREATE INDEX
Time: 333,063 ms
CREATE INDEX deliverynotes_codincotermup_m1_idx
  ON deliverynotes
  USING brin
  (upper(codincoterm::text) );
CREATE INDEX
Time: 439,176 ms
CREATE INDEX deliverynotes_codmodotransporte_m1_idx
  ON deliverynotes
  USING brin
  (codmodotransporte );
CREATE INDEX
Time: 221,246 ms
CREATE INDEX deliverynotes_codmodotransporteup_m1_idx
  ON deliverynotes
  USING brin
  (upper(codmodotransporte::text) );
CREATE INDEX
Time: 251,489 ms
CREATE INDEX deliverynotes_codnaturaleza_m1_idx
  ON deliverynotes
  USING brin
  (codnaturaleza );
CREATE INDEX
Time: 226,385 ms
CREATE INDEX deliverynotes_codnaturalezaup_m1_idx
  ON deliverynotes
  USING brin
  (upper(codnaturaleza::text) );
CREATE INDEX
Time: 251,047 ms
CREATE INDEX deliverynotes_codpacking_m1_idx
  ON deliverynotes
  USING brin
  (codpacking );
CREATE INDEX
Time: 264,059 ms
CREATE INDEX deliverynotes_codpackingup_m1_idx
  ON deliverynotes
  USING brin
  (upper(codpacking::text) );
CREATE INDEX
Time: 296,287 ms
CREATE INDEX deliverynotes_codpago_m1_idx
  ON deliverynotes
  USING brin
  (codpago );
CREATE INDEX
Time: 326,544 ms
CREATE INDEX deliverynotes_codpagoup_m1_idx
  ON deliverynotes
  USING brin
  (upper(codpago::text) );
CREATE INDEX
Time: 442,909 ms
CREATE INDEX deliverynotes_codpais_cobro_m1_idx
  ON deliverynotes
  USING brin
  (codpais_cobro );
CREATE INDEX
Time: 306,701 ms
CREATE INDEX deliverynotes_codpais_cobroup_m1_idx
  ON deliverynotes
  USING brin
  (upper(codpais_cobro::text) );
CREATE INDEX
Time: 391,931 ms
CREATE INDEX deliverynotes_codpais_envio_m1_idx
  ON deliverynotes
  USING brin
  (codpais_envio );
CREATE INDEX
Time: 298,537 ms
CREATE INDEX deliverynotes_codpais_envioup_m1_idx
  ON deliverynotes
  USING brin
  (upper(codpais_envio::text) );
CREATE INDEX
Time: 385,441 ms
CREATE INDEX deliverynotes_codpais_m1_idx
  ON deliverynotes
  USING brin
  (codpais );
CREATE INDEX
Time: 278,879 ms
CREATE INDEX deliverynotes_codpaisup_m1_idx
  ON deliverynotes
  USING brin
  (upper(codpais::text) );
CREATE INDEX
Time: 369,924 ms
CREATE INDEX deliverynotes_codpuerto_m1_idx
  ON deliverynotes
  USING brin
  (codpuerto );
CREATE INDEX
Time: 162,772 ms
CREATE INDEX deliverynotes_codpuertoup_m1_idx
  ON deliverynotes
  USING brin
  (upper(codpuerto::text) );
CREATE INDEX
Time: 166,017 ms
CREATE INDEX deliverynotes_codregimen_m1_idx
  ON deliverynotes
  USING brin
  (codregimen );
CREATE INDEX
Time: 220,081 ms
CREATE INDEX deliverynotes_codregimenup_m1_idx
  ON deliverynotes
  USING brin
  (upper(codregimen::text) );
CREATE INDEX
Time: 249,365 ms
CREATE INDEX deliverynotes_codserie_m1_idx
  ON deliverynotes
  USING brin
  (codserie );
CREATE INDEX
Time: 262,052 ms
CREATE INDEX deliverynotes_codserieup_m1_idx
  ON deliverynotes
  USING brin
  (upper(codserie::text) );
CREATE INDEX
Time: 376,087 ms
CREATE INDEX deliverynotes_codsubcentro_m1_idx
  ON deliverynotes
  USING brin
  (codsubcentro );
CREATE INDEX
Time: 161,942 ms
CREATE INDEX deliverynotes_codsubcentroup_m1_idx
  ON deliverynotes
  USING brin
  (upper(codsubcentro::text) );
CREATE INDEX
Time: 167,716 ms
CREATE INDEX deliverynotes_codtarifa2_m1_idx
  ON deliverynotes
  USING brin
  (codtarifa2 );
CREATE INDEX
Time: 224,386 ms
CREATE INDEX deliverynotes_codtarifa2up_m1_idx
  ON deliverynotes
  USING brin
  (upper(codtarifa2::text) );
CREATE INDEX
Time: 253,596 ms
CREATE INDEX deliverynotes_codtarifa_m1_idx
  ON deliverynotes
  USING brin
  (codtarifa );
CREATE INDEX
Time: 235,501 ms
CREATE INDEX deliverynotes_codtarifaup_m1_idx
  ON deliverynotes
  USING brin
  (upper(codtarifa::text) );
CREATE INDEX
Time: 266,631 ms
CREATE INDEX deliverynotes_codtemporada_m1_idx
  ON deliverynotes
  USING brin
  (codtemporada );
CREATE INDEX
Time: 160,972 ms
CREATE INDEX deliverynotes_codtemporadaup_m1_idx
  ON deliverynotes
  USING brin
  (upper(codtemporada::text) );
CREATE INDEX
Time: 167,460 ms
CREATE INDEX deliverynotes_fecha_idalbaran
  ON deliverynotes
  USING brin
  (fecha, idalbaran);
CREATE INDEX
Time: 206,622 ms
CREATE INDEX deliverynotes_idalbaran_m1_idx
  ON deliverynotes
  USING brin
  (idalbaran);
CREATE INDEX
Time: 174,081 ms
CREATE INDEX deliverynotes_idpedido_m1_idx
  ON deliverynotes
  USING brin
  (idpedido);
CREATE INDEX
Time: 174,133 ms
CREATE INDEX deliverynotes_idprealbaran_m1_idx
  ON deliverynotes
  USING brin
  (idprealbaran);
CREATE INDEX
Time: 162,887 ms
CREATE INDEX deliverynotes_idprovincia_cobro_m1_idx
  ON deliverynotes
  USING brin
  (idprovincia_cobro);
CREATE INDEX
Time: 181,790 ms
CREATE INDEX deliverynotes_idprovincia_envio_m1_idx
  ON deliverynotes
  USING brin
  (idprovincia_envio);
CREATE INDEX
Time: 178,143 ms
CREATE INDEX deliverynotes_idprovincia_m1_idx
  ON deliverynotes
  USING brin
  (idprovincia);
CREATE INDEX
Time: 183,374 ms
CREATE INDEX deliverynotes_numerocodseriecodejercicio_m1_idx
  ON deliverynotes
  USING brin
  (numero , codserie , codejercicio );
CREATE INDEX
Time: 581,783 ms
  
  
--VACUUM ANALYZE deliverynotes;
SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_relation_size(C.oid)) AS "size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
	and relname LIKE 'deliverynote%'
  ORDER BY relname;
                         relation                          |  size  
-----------------------------------------------------------+--------
 pg_temp_2.deliverynotes                                   | 380 MB
 pg_temp_2.deliverynotes_codagencia_m1_idx                 | 32 kB
 pg_temp_2.deliverynotes_codagenciaup_m1_idx               | 32 kB
 pg_temp_2.deliverynotes_codagente2_m1_idx                 | 24 kB
 pg_temp_2.deliverynotes_codagente2up_m1_idx               | 24 kB
 pg_temp_2.deliverynotes_codagente3_m1_idx                 | 24 kB
 pg_temp_2.deliverynotes_codagente3up_m1_idx               | 24 kB
 pg_temp_2.deliverynotes_codagente_m1_idx                  | 24 kB
 pg_temp_2.deliverynotes_codagenteup_m1_idx                | 24 kB
 pg_temp_2.deliverynotes_codalmacen_m1_idx                 | 24 kB
 pg_temp_2.deliverynotes_codalmacenup_m1_idx               | 24 kB
 pg_temp_2.deliverynotes_codcentro_m1_idx                  | 24 kB
 pg_temp_2.deliverynotes_codcentroup_m1_idx                | 24 kB
 pg_temp_2.deliverynotes_codcliente_m1_idx                 | 32 kB
 pg_temp_2.deliverynotes_codclienteup_m1_idx               | 32 kB
 pg_temp_2.deliverynotes_codcondicionentrega_m1_idx        | 24 kB
 pg_temp_2.deliverynotes_codcondicionentregaup_m1_idx      | 24 kB
 pg_temp_2.deliverynotes_coddir_cobro_m1_idx               | 24 kB
 pg_temp_2.deliverynotes_coddir_envio_m1_idx               | 24 kB
 pg_temp_2.deliverynotes_coddir_m1_idx                     | 24 kB
 pg_temp_2.deliverynotes_coddivisa_m1_idx                  | 24 kB
 pg_temp_2.deliverynotes_coddivisaup_m1_idx                | 24 kB
 pg_temp_2.deliverynotes_codejercicio_m1_idx               | 32 kB
 pg_temp_2.deliverynotes_codejercicioup_m1_idx             | 32 kB
 pg_temp_2.deliverynotes_codimpuestoportes_m1_idx          | 24 kB
 pg_temp_2.deliverynotes_codimpuestoportesup_m1_idx        | 24 kB
 pg_temp_2.deliverynotes_codincoterm_m1_idx                | 24 kB
 pg_temp_2.deliverynotes_codincotermup_m1_idx              | 24 kB
 pg_temp_2.deliverynotes_codmodotransporte_m1_idx          | 24 kB
 pg_temp_2.deliverynotes_codmodotransporteup_m1_idx        | 24 kB
 pg_temp_2.deliverynotes_codnaturaleza_m1_idx              | 24 kB
 pg_temp_2.deliverynotes_codnaturalezaup_m1_idx            | 24 kB
 pg_temp_2.deliverynotes_codpacking_m1_idx                 | 24 kB
 pg_temp_2.deliverynotes_codpackingup_m1_idx               | 24 kB
 pg_temp_2.deliverynotes_codpago_m1_idx                    | 32 kB
 pg_temp_2.deliverynotes_codpagoup_m1_idx                  | 32 kB
 pg_temp_2.deliverynotes_codpais_cobro_m1_idx              | 24 kB
 pg_temp_2.deliverynotes_codpais_cobroup_m1_idx            | 24 kB
 pg_temp_2.deliverynotes_codpais_envio_m1_idx              | 24 kB
 pg_temp_2.deliverynotes_codpais_envioup_m1_idx            | 24 kB
 pg_temp_2.deliverynotes_codpais_m1_idx                    | 24 kB
 pg_temp_2.deliverynotes_codpaisup_m1_idx                  | 24 kB
 pg_temp_2.deliverynotes_codpuerto_m1_idx                  | 24 kB
 pg_temp_2.deliverynotes_codpuertoup_m1_idx                | 24 kB
 pg_temp_2.deliverynotes_codregimen_m1_idx                 | 24 kB
 pg_temp_2.deliverynotes_codregimenup_m1_idx               | 24 kB
 pg_temp_2.deliverynotes_codserie_m1_idx                   | 24 kB
 pg_temp_2.deliverynotes_codserieup_m1_idx                 | 24 kB
 pg_temp_2.deliverynotes_codsubcentro_m1_idx               | 24 kB
 pg_temp_2.deliverynotes_codsubcentroup_m1_idx             | 24 kB
 pg_temp_2.deliverynotes_codtarifa2_m1_idx                 | 24 kB
 pg_temp_2.deliverynotes_codtarifa2up_m1_idx               | 24 kB
 pg_temp_2.deliverynotes_codtarifa_m1_idx                  | 24 kB
 pg_temp_2.deliverynotes_codtarifaup_m1_idx                | 24 kB
 pg_temp_2.deliverynotes_codtemporada_m1_idx               | 24 kB
 pg_temp_2.deliverynotes_codtemporadaup_m1_idx             | 24 kB
 pg_temp_2.deliverynotes_fecha_idalbaran                   | 32 kB
 pg_temp_2.deliverynotes_idalbaran_m1_idx                  | 24 kB
 pg_temp_2.deliverynotes_idpedido_m1_idx                   | 24 kB
 pg_temp_2.deliverynotes_idprealbaran_m1_idx               | 24 kB
 pg_temp_2.deliverynotes_idprovincia_cobro_m1_idx          | 24 kB
 pg_temp_2.deliverynotes_idprovincia_envio_m1_idx          | 24 kB
 pg_temp_2.deliverynotes_idprovincia_m1_idx                | 24 kB
 pg_temp_2.deliverynotes_numerocodseriecodejercicio_m1_idx | 40 kB
 pg_temp_2.deliverynotes_pkey                              | 11 MB
(65 rows)

Time: 3,775 ms
  
DELETE FROM   deliverynotes;
DELETE 333694
Time: 1029,527 ms
INSERT INTO deliverynotes SELECT * FROM albaranescli ORDER BY idalbaran LIMIT 50000;
INSERT 0 50000
Time: 1521,616 ms
INSERT INTO deliverynotes SELECT * FROM albaranescli ORDER BY idalbaran LIMIT 50000 OFFSET 50000;
INSERT 0 50000
Time: 1426,818 ms
INSERT INTO deliverynotes SELECT * FROM albaranescli ORDER BY idalbaran LIMIT 50000 OFFSET 100000;
INSERT 0 50000
Time: 1420,816 ms
INSERT INTO deliverynotes SELECT * FROM albaranescli ORDER BY idalbaran LIMIT 50000 OFFSET 150000;
INSERT 0 50000
Time: 1430,287 ms
INSERT INTO deliverynotes SELECT * FROM albaranescli ORDER BY idalbaran LIMIT 50000 OFFSET 200000;
INSERT 0 50000
Time: 1441,990 ms
INSERT INTO deliverynotes SELECT * FROM albaranescli ORDER BY idalbaran LIMIT 50000 OFFSET 250000;
INSERT 0 50000
Time: 1564,492 ms
--VACUUM ANALYZE deliverynotes;
UPDATE deliverynotes SET codserie = '*' WHERE codserie = 'VALUE NOT IN TABLE';
UPDATE 0
Time: 1079,819 ms
UPDATE deliverynotes SET codserie = '*' WHERE codserie IS NULL;
UPDATE 0
Time: 244,676 ms
UPDATE deliverynotes SET codserie = '*' WHERE codserie = 'VALUE ALSO NOT IN TABLE';
UPDATE 0
Time: 193,590 ms
UPDATE deliverynotes SET codserie = '*4' WHERE codserie = '04';
UPDATE 1
Time: 359,328 ms
UPDATE deliverynotes SET codserie = '*3' WHERE codserie = '03';
UPDATE 2258
Time: 408,807 ms
UPDATE deliverynotes SET codserie = '*7' WHERE codserie = '07';
UPDATE 5260
Time: 541,037 ms
UPDATE deliverynotes SET codserie = '*2' WHERE codserie = '02';
UPDATE 43583
Time: 1680,213 ms
SELECT COUNT(*) FROM deliverynotes WHERE codserie = '01';
 count  
--------
 113615
(1 row)

Time: 378,013 ms
SELECT COUNT(*) FROM deliverynotes WHERE codserie = '999';
 count 
-------
     0
(1 row)

Time: 202,664 ms
SELECT COUNT(*) FROM deliverynotes WHERE codserie IS NULL;
 count 
-------
     0
(1 row)

Time: 184,994 ms