\timing
Timing is on.
\pset pager always
Pager is always used.
DROP TABLE IF EXISTS deliverynotes CASCADE;
psql:testA_prepare_full_gin.sql:1: NOTICE:  table "deliverynotes" does not exist, skipping
DROP TABLE
Time: 0,271 ms
CREATE TEMP TABLE deliverynotes AS (SELECT * FROM albaranescli ORDER BY idalbaran);
SELECT 333694
Time: 547,531 ms
ALTER TABLE deliverynotes SET (fillfactor = 60);
ALTER TABLE
Time: 0,409 ms
ALTER TABLE deliverynotes ADD CONSTRAINT deliverynotes_pkey 
	PRIMARY KEY(idalbaran)  WITH (FILLFACTOR=60);
ALTER TABLE
Time: 964,609 ms
CLUSTER deliverynotes USING 	deliverynotes_pkey;
CLUSTER
Time: 1659,349 ms
CREATE INDEX deliverynotes_codagencia_m1_idx
  ON deliverynotes
  USING gin
  (codagencia );
CREATE INDEX
Time: 628,412 ms
CREATE INDEX deliverynotes_codagenciaup_m1_idx
  ON deliverynotes
  USING gin
  (upper(codagencia::text) );
CREATE INDEX
Time: 709,137 ms
CREATE INDEX deliverynotes_codagente2_m1_idx
  ON deliverynotes
  USING gin
  (codagente2 );
CREATE INDEX
Time: 192,485 ms
CREATE INDEX deliverynotes_codagente2up_m1_idx
  ON deliverynotes
  USING gin
  (upper(codagente2::text) );
CREATE INDEX
Time: 200,533 ms
CREATE INDEX deliverynotes_codagente3_m1_idx
  ON deliverynotes
  USING gin
  (codagente3 );
CREATE INDEX
Time: 185,215 ms
CREATE INDEX deliverynotes_codagente3up_m1_idx
  ON deliverynotes
  USING gin
  (upper(codagente3::text) );
CREATE INDEX
Time: 194,154 ms
CREATE INDEX deliverynotes_codagente_m1_idx
  ON deliverynotes
  USING gin
  (codagente );
CREATE INDEX
Time: 410,778 ms
CREATE INDEX deliverynotes_codagenteup_m1_idx
  ON deliverynotes
  USING gin
  (upper(codagente::text) );
CREATE INDEX
Time: 533,059 ms
CREATE INDEX deliverynotes_codalmacen_m1_idx
  ON deliverynotes
  USING gin
  (codalmacen );
CREATE INDEX
Time: 272,636 ms
CREATE INDEX deliverynotes_codalmacenup_m1_idx
  ON deliverynotes
  USING gin
  (upper(codalmacen::text) );
CREATE INDEX
Time: 398,598 ms
CREATE INDEX deliverynotes_codcentro_m1_idx
  ON deliverynotes
  USING gin
  (codcentro );
CREATE INDEX
Time: 191,483 ms
CREATE INDEX deliverynotes_codcentroup_m1_idx
  ON deliverynotes
  USING gin
  (upper(codcentro::text) );
CREATE INDEX
Time: 200,312 ms
CREATE INDEX deliverynotes_codcliente_m1_idx
  ON deliverynotes
  USING gin
  (codcliente );
CREATE INDEX
Time: 1592,800 ms
CREATE INDEX deliverynotes_codclienteup_m1_idx
  ON deliverynotes
  USING gin
  (upper(codcliente::text) );
CREATE INDEX
Time: 1753,209 ms
CREATE INDEX deliverynotes_codcondicionentrega_m1_idx
  ON deliverynotes
  USING gin
  (codcondicionentrega );
CREATE INDEX
Time: 248,545 ms
CREATE INDEX deliverynotes_codcondicionentregaup_m1_idx
  ON deliverynotes
  USING gin
  (upper(codcondicionentrega::text) );
CREATE INDEX
Time: 286,910 ms
CREATE INDEX deliverynotes_coddir_cobro_m1_idx
  ON deliverynotes
  USING gin
  (coddir_cobro);
CREATE INDEX
Time: 265,401 ms
CREATE INDEX deliverynotes_coddir_envio_m1_idx
  ON deliverynotes
  USING gin
  (coddir_envio);
CREATE INDEX
Time: 298,685 ms
CREATE INDEX deliverynotes_coddir_m1_idx
  ON deliverynotes
  USING gin
  (coddir);
CREATE INDEX
Time: 250,797 ms
CREATE INDEX deliverynotes_coddivisa_m1_idx
  ON deliverynotes
  USING gin
  (coddivisa );
CREATE INDEX
Time: 253,535 ms
CREATE INDEX deliverynotes_coddivisaup_m1_idx
  ON deliverynotes
  USING gin
  (upper(coddivisa::text) );
CREATE INDEX
Time: 404,468 ms
CREATE INDEX deliverynotes_codejercicio_m1_idx
  ON deliverynotes
  USING gin
  (codejercicio );
CREATE INDEX
Time: 415,712 ms
CREATE INDEX deliverynotes_codejercicioup_m1_idx
  ON deliverynotes
  USING gin
  (upper(codejercicio::text) );
CREATE INDEX
Time: 567,144 ms
CREATE INDEX deliverynotes_codimpuestoportes_m1_idx
  ON deliverynotes
  USING gin
  (codimpuestoportes );
CREATE INDEX
Time: 354,970 ms
CREATE INDEX deliverynotes_codimpuestoportesup_m1_idx
  ON deliverynotes
  USING gin
  (upper(codimpuestoportes::text) );
CREATE INDEX
Time: 483,727 ms
CREATE INDEX deliverynotes_codincoterm_m1_idx
  ON deliverynotes
  USING gin
  (codincoterm );
CREATE INDEX
Time: 350,646 ms
CREATE INDEX deliverynotes_codincotermup_m1_idx
  ON deliverynotes
  USING gin
  (upper(codincoterm::text) );
CREATE INDEX
Time: 495,749 ms
CREATE INDEX deliverynotes_codmodotransporte_m1_idx
  ON deliverynotes
  USING gin
  (codmodotransporte );
CREATE INDEX
Time: 242,813 ms
CREATE INDEX deliverynotes_codmodotransporteup_m1_idx
  ON deliverynotes
  USING gin
  (upper(codmodotransporte::text) );
CREATE INDEX
Time: 278,673 ms
CREATE INDEX deliverynotes_codnaturaleza_m1_idx
  ON deliverynotes
  USING gin
  (codnaturaleza );
CREATE INDEX
Time: 250,243 ms
CREATE INDEX deliverynotes_codnaturalezaup_m1_idx
  ON deliverynotes
  USING gin
  (upper(codnaturaleza::text) );
CREATE INDEX
Time: 284,908 ms
CREATE INDEX deliverynotes_codpacking_m1_idx
  ON deliverynotes
  USING gin
  (codpacking );
CREATE INDEX
Time: 1183,891 ms
CREATE INDEX deliverynotes_codpackingup_m1_idx
  ON deliverynotes
  USING gin
  (upper(codpacking::text) );
CREATE INDEX
Time: 1233,281 ms
CREATE INDEX deliverynotes_codpago_m1_idx
  ON deliverynotes
  USING gin
  (codpago );
CREATE INDEX
Time: 529,292 ms
CREATE INDEX deliverynotes_codpagoup_m1_idx
  ON deliverynotes
  USING gin
  (upper(codpago::text) );
CREATE INDEX
Time: 676,318 ms
CREATE INDEX deliverynotes_codpais_cobro_m1_idx
  ON deliverynotes
  USING gin
  (codpais_cobro );
CREATE INDEX
Time: 451,730 ms
CREATE INDEX deliverynotes_codpais_cobroup_m1_idx
  ON deliverynotes
  USING gin
  (upper(codpais_cobro::text) );
CREATE INDEX
Time: 576,712 ms
CREATE INDEX deliverynotes_codpais_envio_m1_idx
  ON deliverynotes
  USING gin
  (codpais_envio );
CREATE INDEX
Time: 463,102 ms
CREATE INDEX deliverynotes_codpais_envioup_m1_idx
  ON deliverynotes
  USING gin
  (upper(codpais_envio::text) );
CREATE INDEX
Time: 596,920 ms
CREATE INDEX deliverynotes_codpais_m1_idx
  ON deliverynotes
  USING gin
  (codpais );
CREATE INDEX
Time: 417,116 ms
CREATE INDEX deliverynotes_codpaisup_m1_idx
  ON deliverynotes
  USING gin
  (upper(codpais::text) );
CREATE INDEX
Time: 547,271 ms
CREATE INDEX deliverynotes_codpuerto_m1_idx
  ON deliverynotes
  USING gin
  (codpuerto );
CREATE INDEX
Time: 191,000 ms
CREATE INDEX deliverynotes_codpuertoup_m1_idx
  ON deliverynotes
  USING gin
  (upper(codpuerto::text) );
CREATE INDEX
Time: 198,810 ms
CREATE INDEX deliverynotes_codregimen_m1_idx
  ON deliverynotes
  USING gin
  (codregimen );
CREATE INDEX
Time: 246,803 ms
CREATE INDEX deliverynotes_codregimenup_m1_idx
  ON deliverynotes
  USING gin
  (upper(codregimen::text) );
CREATE INDEX
Time: 281,248 ms
CREATE INDEX deliverynotes_codserie_m1_idx
  ON deliverynotes
  USING gin
  (codserie );
CREATE INDEX
Time: 331,755 ms
CREATE INDEX deliverynotes_codserieup_m1_idx
  ON deliverynotes
  USING gin
  (upper(codserie::text) );
CREATE INDEX
Time: 478,904 ms
CREATE INDEX deliverynotes_codsubcentro_m1_idx
  ON deliverynotes
  USING gin
  (codsubcentro );
CREATE INDEX
Time: 189,570 ms
CREATE INDEX deliverynotes_codsubcentroup_m1_idx
  ON deliverynotes
  USING gin
  (upper(codsubcentro::text) );
CREATE INDEX
Time: 199,112 ms
CREATE INDEX deliverynotes_codtarifa2_m1_idx
  ON deliverynotes
  USING gin
  (codtarifa2 );
CREATE INDEX
Time: 281,227 ms
CREATE INDEX deliverynotes_codtarifa2up_m1_idx
  ON deliverynotes
  USING gin
  (upper(codtarifa2::text) );
CREATE INDEX
Time: 318,577 ms
CREATE INDEX deliverynotes_codtarifa_m1_idx
  ON deliverynotes
  USING gin
  (codtarifa );
CREATE INDEX
Time: 337,463 ms
CREATE INDEX deliverynotes_codtarifaup_m1_idx
  ON deliverynotes
  USING gin
  (upper(codtarifa::text) );
CREATE INDEX
Time: 380,406 ms
CREATE INDEX deliverynotes_codtemporada_m1_idx
  ON deliverynotes
  USING gin
  (codtemporada );
CREATE INDEX
Time: 188,658 ms
CREATE INDEX deliverynotes_codtemporadaup_m1_idx
  ON deliverynotes
  USING gin
  (upper(codtemporada::text) );
CREATE INDEX
Time: 196,708 ms
CREATE INDEX deliverynotes_fecha_idalbaran
  ON deliverynotes
  USING gin
  (fecha, idalbaran);
CREATE INDEX
Time: 855,472 ms
CREATE INDEX deliverynotes_idalbaran_m1_idx
  ON deliverynotes
  USING gin
  (idalbaran);
CREATE INDEX
Time: 664,629 ms
CREATE INDEX deliverynotes_idpedido_m1_idx
  ON deliverynotes
  USING gin
  (idpedido);
CREATE INDEX
Time: 228,131 ms
CREATE INDEX deliverynotes_idprealbaran_m1_idx
  ON deliverynotes
  USING gin
  (idprealbaran);
CREATE INDEX
Time: 189,014 ms
CREATE INDEX deliverynotes_idprovincia_cobro_m1_idx
  ON deliverynotes
  USING gin
  (idprovincia_cobro);
CREATE INDEX
Time: 208,139 ms
CREATE INDEX deliverynotes_idprovincia_envio_m1_idx
  ON deliverynotes
  USING gin
  (idprovincia_envio);
CREATE INDEX
Time: 205,376 ms
CREATE INDEX deliverynotes_idprovincia_m1_idx
  ON deliverynotes
  USING gin
  (idprovincia);
CREATE INDEX
Time: 213,441 ms
CREATE INDEX deliverynotes_numerocodseriecodejercicio_m1_idx
  ON deliverynotes
  USING gin
  (numero , codserie , codejercicio );
CREATE INDEX
Time: 3434,234 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                 | 648 kB
 pg_temp_2.deliverynotes_codagenciaup_m1_idx               | 648 kB
 pg_temp_2.deliverynotes_codagente2_m1_idx                 | 424 kB
 pg_temp_2.deliverynotes_codagente2up_m1_idx               | 424 kB
 pg_temp_2.deliverynotes_codagente3_m1_idx                 | 424 kB
 pg_temp_2.deliverynotes_codagente3up_m1_idx               | 424 kB
 pg_temp_2.deliverynotes_codagente_m1_idx                  | 712 kB
 pg_temp_2.deliverynotes_codagenteup_m1_idx                | 712 kB
 pg_temp_2.deliverynotes_codalmacen_m1_idx                 | 424 kB
 pg_temp_2.deliverynotes_codalmacenup_m1_idx               | 424 kB
 pg_temp_2.deliverynotes_codcentro_m1_idx                  | 424 kB
 pg_temp_2.deliverynotes_codcentroup_m1_idx                | 424 kB
 pg_temp_2.deliverynotes_codcliente_m1_idx                 | 1416 kB
 pg_temp_2.deliverynotes_codclienteup_m1_idx               | 1416 kB
 pg_temp_2.deliverynotes_codcondicionentrega_m1_idx        | 432 kB
 pg_temp_2.deliverynotes_codcondicionentregaup_m1_idx      | 432 kB
 pg_temp_2.deliverynotes_coddir_cobro_m1_idx               | 1080 kB
 pg_temp_2.deliverynotes_coddir_envio_m1_idx               | 1352 kB
 pg_temp_2.deliverynotes_coddir_m1_idx                     | 1064 kB
 pg_temp_2.deliverynotes_coddivisa_m1_idx                  | 424 kB
 pg_temp_2.deliverynotes_coddivisaup_m1_idx                | 424 kB
 pg_temp_2.deliverynotes_codejercicio_m1_idx               | 536 kB
 pg_temp_2.deliverynotes_codejercicioup_m1_idx             | 536 kB
 pg_temp_2.deliverynotes_codimpuestoportes_m1_idx          | 472 kB
 pg_temp_2.deliverynotes_codimpuestoportesup_m1_idx        | 472 kB
 pg_temp_2.deliverynotes_codincoterm_m1_idx                | 440 kB
 pg_temp_2.deliverynotes_codincotermup_m1_idx              | 440 kB
 pg_temp_2.deliverynotes_codmodotransporte_m1_idx          | 440 kB
 pg_temp_2.deliverynotes_codmodotransporteup_m1_idx        | 440 kB
 pg_temp_2.deliverynotes_codnaturaleza_m1_idx              | 432 kB
 pg_temp_2.deliverynotes_codnaturalezaup_m1_idx            | 432 kB
 pg_temp_2.deliverynotes_codpacking_m1_idx                 | 5016 kB
 pg_temp_2.deliverynotes_codpackingup_m1_idx               | 5016 kB
 pg_temp_2.deliverynotes_codpago_m1_idx                    | 712 kB
 pg_temp_2.deliverynotes_codpagoup_m1_idx                  | 712 kB
 pg_temp_2.deliverynotes_codpais_cobro_m1_idx              | 512 kB
 pg_temp_2.deliverynotes_codpais_cobroup_m1_idx            | 512 kB
 pg_temp_2.deliverynotes_codpais_envio_m1_idx              | 480 kB
 pg_temp_2.deliverynotes_codpais_envioup_m1_idx            | 480 kB
 pg_temp_2.deliverynotes_codpais_m1_idx                    | 472 kB
 pg_temp_2.deliverynotes_codpaisup_m1_idx                  | 472 kB
 pg_temp_2.deliverynotes_codpuerto_m1_idx                  | 424 kB
 pg_temp_2.deliverynotes_codpuertoup_m1_idx                | 424 kB
 pg_temp_2.deliverynotes_codregimen_m1_idx                 | 432 kB
 pg_temp_2.deliverynotes_codregimenup_m1_idx               | 432 kB
 pg_temp_2.deliverynotes_codserie_m1_idx                   | 504 kB
 pg_temp_2.deliverynotes_codserieup_m1_idx                 | 504 kB
 pg_temp_2.deliverynotes_codsubcentro_m1_idx               | 424 kB
 pg_temp_2.deliverynotes_codsubcentroup_m1_idx             | 424 kB
 pg_temp_2.deliverynotes_codtarifa2_m1_idx                 | 488 kB
 pg_temp_2.deliverynotes_codtarifa2up_m1_idx               | 488 kB
 pg_temp_2.deliverynotes_codtarifa_m1_idx                  | 608 kB
 pg_temp_2.deliverynotes_codtarifaup_m1_idx                | 608 kB
 pg_temp_2.deliverynotes_codtemporada_m1_idx               | 424 kB
 pg_temp_2.deliverynotes_codtemporadaup_m1_idx             | 424 kB
 pg_temp_2.deliverynotes_fecha_idalbaran                   | 19 MB
 pg_temp_2.deliverynotes_idalbaran_m1_idx                  | 18 MB
 pg_temp_2.deliverynotes_idpedido_m1_idx                   | 1128 kB
 pg_temp_2.deliverynotes_idprealbaran_m1_idx               | 424 kB
 pg_temp_2.deliverynotes_idprovincia_cobro_m1_idx          | 512 kB
 pg_temp_2.deliverynotes_idprovincia_envio_m1_idx          | 528 kB
 pg_temp_2.deliverynotes_idprovincia_m1_idx                | 608 kB
 pg_temp_2.deliverynotes_numerocodseriecodejercicio_m1_idx | 16 MB
 pg_temp_2.deliverynotes_pkey                              | 11 MB
(65 rows)

Time: 3,102 ms
  
DELETE FROM   deliverynotes;
DELETE 333694
Time: 1027,341 ms
INSERT INTO deliverynotes SELECT * FROM albaranescli ORDER BY idalbaran LIMIT 50000;
INSERT 0 50000
Time: 3940,139 ms
INSERT INTO deliverynotes SELECT * FROM albaranescli ORDER BY idalbaran LIMIT 50000 OFFSET 50000;
INSERT 0 50000
Time: 4672,872 ms
INSERT INTO deliverynotes SELECT * FROM albaranescli ORDER BY idalbaran LIMIT 50000 OFFSET 100000;
INSERT 0 50000
Time: 5773,981 ms
INSERT INTO deliverynotes SELECT * FROM albaranescli ORDER BY idalbaran LIMIT 50000 OFFSET 150000;
INSERT 0 50000
Time: 4984,205 ms
INSERT INTO deliverynotes SELECT * FROM albaranescli ORDER BY idalbaran LIMIT 50000 OFFSET 200000;
INSERT 0 50000
Time: 8985,361 ms
INSERT INTO deliverynotes SELECT * FROM albaranescli ORDER BY idalbaran LIMIT 50000 OFFSET 250000;
INSERT 0 50000
Time: 5839,914 ms
--VACUUM ANALYZE deliverynotes;
UPDATE deliverynotes SET codserie = '*' WHERE codserie = 'VALUE NOT IN TABLE';
UPDATE 0
Time: 16,419 ms
UPDATE deliverynotes SET codserie = '*' WHERE codserie IS NULL;
UPDATE 0
Time: 1293,573 ms
UPDATE deliverynotes SET codserie = '*' WHERE codserie = 'VALUE ALSO NOT IN TABLE';
UPDATE 0
Time: 16,837 ms
UPDATE deliverynotes SET codserie = '*4' WHERE codserie = '04';
UPDATE 1
Time: 20,446 ms
UPDATE deliverynotes SET codserie = '*3' WHERE codserie = '03';
UPDATE 2258
Time: 336,680 ms
UPDATE deliverynotes SET codserie = '*7' WHERE codserie = '07';
UPDATE 5260
Time: 552,374 ms
UPDATE deliverynotes SET codserie = '*2' WHERE codserie = '02';
UPDATE 43583
Time: 5511,350 ms
SELECT COUNT(*) FROM deliverynotes WHERE codserie = '01';
 count  
--------
 113615
(1 row)

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

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

Time: 359,096 ms