So I just ran a test case for hash, btree, gin_btree and brin indexes. Also
without indexes, and without primary keys.
* Testing "deliverynotes" table.
- Definition and use case:
It is a table contaning real delivery note headers of several years
It consists of 300k rows, 128 columns, 63 indexes, 243Mb of data
excluding indexes. Since is a table visible for users, almost every
column can be searched so we need lots of indexes. We do not need
searches to be the fastest possible, we only need to accelerate a
bit our user searches; without harming too much writes.
- Things to test:
- measure index creation times.
- measure index space.
- with indexes but without primary key
- with everything
- Create fully, delete everything and Insert again data in blocks
- Test updates for recent data
I attached the logs for every test, if anyone wants to see what i'm exactly
testing.
This was tested on my i5 laptop with 4Gb of RAM and a 120Gb SSD (OCZ
Agility 3). I'm trying to measure CPU time, not I/O time, so some
configurations and tests are specific to avoid as much as IO as I can.
I'm using a dev build for Postgresql 9.5 downloaded from git sources.
Conclusions:
- Gin_btree seems slower in almost every case. It's writes are marginally
better than regular btrees even when using work_mem=160MB. (May be 20%
faster than btree). They are smaller than I thought.
- BRIN indexes seem very fast for writes. For selects maybe is a blend
between having indexes and don't having them. They don't recognize that
some values are simply out of range of indexed values, and that's a pity.
If the values we want are packed together I guess I would get even better
results.
- Primary keys and uniqueness checks doesn't seem to make any difference
here.
- Having no indexes at all is faster than I imagined. (Sometimes it beats
BRIN or Btree) Maybe because the IO here is faster than usual.
- Hash indexes: i tried to do something, but they take too much time to
build and i don't know why. If creates are slow, updates should be slow
too. I'm not going to test them again.
And finally, don't know why but i couldn't vacuum or analyze tables. It
always get stalled without doing anything; so i had to comment every
vacuum. Maybe there is a bug in this dev version or i misconfigured
something.
El vie., 12 jun. 2015 a las 7:27, Simon Riggs (<si...@2ndquadrant.com>)
escribió:
> On 5 June 2015 at 18:07, deavid <deavidsed...@gmail.com> wrote:
>
>> There are several use cases where I see useful an index, but adding it
>> will slow too much inserts and updates.
>> For example, when we have 10 million rows on a table, and it's a table
>> which has frequent updates, we need several index to speed up selects, but
>> then we'll slow down updates a lot, specially when we have 10 or more
>> indexes.
>> Other cases involve indexes for text search, which are used only for user
>> search and aren't that important, so we want to have them, but we don't
>> want the overload they put whenever we write on the table.
>> I know different approaches that already solve some of those problems in
>> some ways (table partitioning, partial indexes, etc), but i don't feel they
>> are the solution to every problem of this kind.
>>
>> Some people already asked for "delayed write" indexes, but the idea gets
>> discarded because the index could get out of sync, so it can omit results
>> and this is unacceptable. But i think maybe that could be fixed in several
>> ways and we can have a fast and reliable index (but maybe not so fast on
>> selects).
>>
>
> This is exactly the use case and mechanism for BRIN indexes.
>
> --
> Simon Riggs http://www.2ndQuadrant.com/
> <http://www.2ndquadrant.com/>
>
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
\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
\timing
Timing is on.
\pset pager always
Pager is always used.
DROP TABLE IF EXISTS deliverynotes CASCADE;
DROP TABLE
Time: 186,182 ms
CREATE TEMP TABLE deliverynotes AS (SELECT * FROM albaranescli ORDER BY idalbaran);
SELECT 333694
Time: 1022,111 ms
ALTER TABLE deliverynotes SET (fillfactor = 60);
ALTER TABLE
Time: 0,426 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 | 219 MB
(1 row)
Time: 185,537 ms
DELETE FROM deliverynotes;
DELETE 333694
Time: 558,273 ms
INSERT INTO deliverynotes SELECT * FROM albaranescli ORDER BY idalbaran LIMIT 50000;
INSERT 0 50000
Time: 272,404 ms
INSERT INTO deliverynotes SELECT * FROM albaranescli ORDER BY idalbaran LIMIT 50000 OFFSET 50000;
INSERT 0 50000
Time: 234,166 ms
INSERT INTO deliverynotes SELECT * FROM albaranescli ORDER BY idalbaran LIMIT 50000 OFFSET 100000;
INSERT 0 50000
Time: 201,888 ms
INSERT INTO deliverynotes SELECT * FROM albaranescli ORDER BY idalbaran LIMIT 50000 OFFSET 150000;
INSERT 0 50000
Time: 223,916 ms
INSERT INTO deliverynotes SELECT * FROM albaranescli ORDER BY idalbaran LIMIT 50000 OFFSET 200000;
INSERT 0 50000
Time: 202,668 ms
INSERT INTO deliverynotes SELECT * FROM albaranescli ORDER BY idalbaran LIMIT 50000 OFFSET 250000;
INSERT 0 50000
Time: 260,133 ms
--VACUUM ANALYZE deliverynotes;
UPDATE deliverynotes SET codserie = '*' WHERE codserie = 'VALUE NOT IN TABLE';
UPDATE 0
Time: 2098,693 ms
UPDATE deliverynotes SET codserie = '*' WHERE codserie IS NULL;
UPDATE 0
Time: 319,783 ms
UPDATE deliverynotes SET codserie = '*' WHERE codserie = 'VALUE ALSO NOT IN TABLE';
UPDATE 0
Time: 265,806 ms
UPDATE deliverynotes SET codserie = '*4' WHERE codserie = '04';
UPDATE 1
Time: 259,346 ms
UPDATE deliverynotes SET codserie = '*3' WHERE codserie = '03';
UPDATE 2258
Time: 273,400 ms
UPDATE deliverynotes SET codserie = '*7' WHERE codserie = '07';
UPDATE 5260
Time: 296,703 ms
UPDATE deliverynotes SET codserie = '*2' WHERE codserie = '02';
UPDATE 43583
Time: 441,170 ms
SELECT COUNT(*) FROM deliverynotes WHERE codserie = '01';
count
--------
113615
(1 row)
Time: 348,997 ms
SELECT COUNT(*) FROM deliverynotes WHERE codserie = '999';
count
-------
0
(1 row)
Time: 286,985 ms
SELECT COUNT(*) FROM deliverynotes WHERE codserie IS NULL;
count
-------
0
(1 row)
Time: 255,898 ms
\timing
Timing is on.
\pset pager always
Pager is always used.
DROP TABLE IF EXISTS deliverynotes CASCADE;
psql:testA_prepare_full_hash.sql:1: NOTICE: table "deliverynotes" does not exist, skipping
DROP TABLE
Time: 0,439 ms
CREATE TABLE deliverynotes AS (SELECT * FROM albaranescli);
SELECT 333694
Time: 2397,988 ms
ALTER TABLE deliverynotes SET (fillfactor = 60);
ALTER TABLE
Time: 0,457 ms
ALTER TABLE deliverynotes ADD CONSTRAINT deliverynotes_pkey
PRIMARY KEY(idalbaran) WITH (FILLFACTOR=60);
ALTER TABLE
Time: 286,828 ms
CREATE INDEX deliverynotes_codagencia_m1_idx
ON deliverynotes
USING hash
(codagencia );
psql:testA_prepare_full_hash.sql:14: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 5493,989 ms
CREATE INDEX deliverynotes_codagenciaup_m1_idx
ON deliverynotes
USING hash
(upper(codagencia::text) );
psql:testA_prepare_full_hash.sql:19: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 5153,511 ms
CREATE INDEX deliverynotes_codagente2_m1_idx
ON deliverynotes
USING hash
(codagente2 );
psql:testA_prepare_full_hash.sql:24: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 120,368 ms
CREATE INDEX deliverynotes_codagente2up_m1_idx
ON deliverynotes
USING hash
(upper(codagente2::text) );
psql:testA_prepare_full_hash.sql:29: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 121,525 ms
CREATE INDEX deliverynotes_codagente3_m1_idx
ON deliverynotes
USING hash
(codagente3 );
psql:testA_prepare_full_hash.sql:34: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 110,318 ms
CREATE INDEX deliverynotes_codagente3up_m1_idx
ON deliverynotes
USING hash
(upper(codagente3::text) );
psql:testA_prepare_full_hash.sql:39: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 113,314 ms
CREATE INDEX deliverynotes_codagente_m1_idx
ON deliverynotes
USING hash
(codagente );
psql:testA_prepare_full_hash.sql:44: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 1703,009 ms
CREATE INDEX deliverynotes_codagenteup_m1_idx
ON deliverynotes
USING hash
(upper(codagente::text) );
psql:testA_prepare_full_hash.sql:49: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 1841,278 ms
CREATE INDEX deliverynotes_codalmacen_m1_idx
ON deliverynotes
USING hash
(codalmacen );
psql:testA_prepare_full_hash.sql:54: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 26513,901 ms
CREATE INDEX deliverynotes_codalmacenup_m1_idx
ON deliverynotes
USING hash
(upper(codalmacen::text) );
psql:testA_prepare_full_hash.sql:59: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 26551,112 ms
CREATE INDEX deliverynotes_codcentro_m1_idx
ON deliverynotes
USING hash
(codcentro );
psql:testA_prepare_full_hash.sql:64: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 104,839 ms
CREATE INDEX deliverynotes_codcentroup_m1_idx
ON deliverynotes
USING hash
(upper(codcentro::text) );
psql:testA_prepare_full_hash.sql:69: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 113,155 ms
CREATE INDEX deliverynotes_codcliente_m1_idx
ON deliverynotes
USING hash
(codcliente );
psql:testA_prepare_full_hash.sql:74: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 424,544 ms
CREATE INDEX deliverynotes_codclienteup_m1_idx
ON deliverynotes
USING hash
(upper(codcliente::text) );
psql:testA_prepare_full_hash.sql:79: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 609,373 ms
CREATE INDEX deliverynotes_codcondicionentrega_m1_idx
ON deliverynotes
USING hash
(codcondicionentrega );
psql:testA_prepare_full_hash.sql:84: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 1244,554 ms
CREATE INDEX deliverynotes_codcondicionentregaup_m1_idx
ON deliverynotes
USING hash
(upper(codcondicionentrega::text) );
psql:testA_prepare_full_hash.sql:89: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 1291,055 ms
CREATE INDEX deliverynotes_coddir_cobro_m1_idx
ON deliverynotes
USING hash
(coddir_cobro);
psql:testA_prepare_full_hash.sql:94: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 209,936 ms
CREATE INDEX deliverynotes_coddir_envio_m1_idx
ON deliverynotes
USING hash
(coddir_envio);
psql:testA_prepare_full_hash.sql:99: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 233,209 ms
CREATE INDEX deliverynotes_coddir_m1_idx
ON deliverynotes
USING hash
(coddir);
psql:testA_prepare_full_hash.sql:104: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 218,384 ms
CREATE INDEX deliverynotes_coddivisa_m1_idx
ON deliverynotes
USING hash
(coddivisa );
psql:testA_prepare_full_hash.sql:109: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 27176,841 ms
CREATE INDEX deliverynotes_coddivisaup_m1_idx
ON deliverynotes
USING hash
(upper(coddivisa::text) );
psql:testA_prepare_full_hash.sql:114: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 26571,434 ms
CREATE INDEX deliverynotes_codejercicio_m1_idx
ON deliverynotes
USING hash
(codejercicio );
psql:testA_prepare_full_hash.sql:119: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 6061,186 ms
CREATE INDEX deliverynotes_codejercicioup_m1_idx
ON deliverynotes
USING hash
(upper(codejercicio::text) );
psql:testA_prepare_full_hash.sql:128: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 6313,385 ms
CREATE INDEX deliverynotes_codimpuestoportes_m1_idx
ON deliverynotes
USING hash
(codimpuestoportes );
psql:testA_prepare_full_hash.sql:137: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 15946,097 ms
CREATE INDEX deliverynotes_codimpuestoportesup_m1_idx
ON deliverynotes
USING hash
(upper(codimpuestoportes::text) );
psql:testA_prepare_full_hash.sql:146: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 14694,843 ms
CREATE INDEX deliverynotes_codincoterm_m1_idx
ON deliverynotes
USING hash
(codincoterm );
psql:testA_prepare_full_hash.sql:155: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 28766,727 ms
CREATE INDEX deliverynotes_codincotermup_m1_idx
ON deliverynotes
USING hash
(upper(codincoterm::text) );
psql:testA_prepare_full_hash.sql:164: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 20671,843 ms
CREATE INDEX deliverynotes_codmodotransporte_m1_idx
ON deliverynotes
USING hash
(codmodotransporte );
psql:testA_prepare_full_hash.sql:173: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 1150,600 ms
CREATE INDEX deliverynotes_codmodotransporteup_m1_idx
ON deliverynotes
USING hash
(upper(codmodotransporte::text) );
psql:testA_prepare_full_hash.sql:182: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 1223,117 ms
CREATE INDEX deliverynotes_codnaturaleza_m1_idx
ON deliverynotes
USING hash
(codnaturaleza );
psql:testA_prepare_full_hash.sql:191: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 1320,027 ms
CREATE INDEX deliverynotes_codnaturalezaup_m1_idx
ON deliverynotes
USING hash
(upper(codnaturaleza::text) );
psql:testA_prepare_full_hash.sql:200: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 1289,292 ms
CREATE INDEX deliverynotes_codpacking_m1_idx
ON deliverynotes
USING hash
(codpacking );
psql:testA_prepare_full_hash.sql:209: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 216,403 ms
CREATE INDEX deliverynotes_codpackingup_m1_idx
ON deliverynotes
USING hash
(upper(codpacking::text) );
psql:testA_prepare_full_hash.sql:218: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 270,093 ms
CREATE INDEX deliverynotes_codpago_m1_idx
ON deliverynotes
USING hash
(codpago );
psql:testA_prepare_full_hash.sql:227: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 3162,579 ms
CREATE INDEX deliverynotes_codpagoup_m1_idx
ON deliverynotes
USING hash
(upper(codpago::text) );
psql:testA_prepare_full_hash.sql:236: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 3389,680 ms
CREATE INDEX deliverynotes_codpais_cobro_m1_idx
ON deliverynotes
USING hash
(codpais_cobro );
psql:testA_prepare_full_hash.sql:245: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 18024,466 ms
CREATE INDEX deliverynotes_codpais_cobroup_m1_idx
ON deliverynotes
USING hash
(upper(codpais_cobro::text) );
psql:testA_prepare_full_hash.sql:254: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 20802,489 ms
CREATE INDEX deliverynotes_codpais_envio_m1_idx
ON deliverynotes
USING hash
(codpais_envio );
psql:testA_prepare_full_hash.sql:263: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 20310,725 ms
CREATE INDEX deliverynotes_codpais_envioup_m1_idx
ON deliverynotes
USING hash
(upper(codpais_envio::text) );
psql:testA_prepare_full_hash.sql:272: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 19555,458 ms
CREATE INDEX deliverynotes_codpais_m1_idx
ON deliverynotes
USING hash
(codpais );
psql:testA_prepare_full_hash.sql:281: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 18464,918 ms
CREATE INDEX deliverynotes_codpaisup_m1_idx
ON deliverynotes
USING hash
(upper(codpais::text) );
psql:testA_prepare_full_hash.sql:290: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 18752,407 ms
CREATE INDEX deliverynotes_codpuerto_m1_idx
ON deliverynotes
USING hash
(codpuerto );
psql:testA_prepare_full_hash.sql:299: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 102,428 ms
CREATE INDEX deliverynotes_codpuertoup_m1_idx
ON deliverynotes
USING hash
(upper(codpuerto::text) );
psql:testA_prepare_full_hash.sql:308: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 107,179 ms
CREATE INDEX deliverynotes_codregimen_m1_idx
ON deliverynotes
USING hash
(codregimen );
psql:testA_prepare_full_hash.sql:317: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 1204,392 ms
CREATE INDEX deliverynotes_codregimenup_m1_idx
ON deliverynotes
USING hash
(upper(codregimen::text) );
psql:testA_prepare_full_hash.sql:326: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 1236,950 ms
CREATE INDEX deliverynotes_codserie_m1_idx
ON deliverynotes
USING hash
(codserie );
psql:testA_prepare_full_hash.sql:335: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 8478,570 ms
CREATE INDEX deliverynotes_codserieup_m1_idx
ON deliverynotes
USING hash
(upper(codserie::text) );
psql:testA_prepare_full_hash.sql:344: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 7953,012 ms
CREATE INDEX deliverynotes_codsubcentro_m1_idx
ON deliverynotes
USING hash
(codsubcentro );
psql:testA_prepare_full_hash.sql:353: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 102,854 ms
CREATE INDEX deliverynotes_codsubcentroup_m1_idx
ON deliverynotes
USING hash
(upper(codsubcentro::text) );
psql:testA_prepare_full_hash.sql:362: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 123,518 ms
CREATE INDEX deliverynotes_codtarifa2_m1_idx
ON deliverynotes
USING hash
(codtarifa2 );
psql:testA_prepare_full_hash.sql:371: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 434,853 ms
CREATE INDEX deliverynotes_codtarifa2up_m1_idx
ON deliverynotes
USING hash
(upper(codtarifa2::text) );
psql:testA_prepare_full_hash.sql:380: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 477,286 ms
CREATE INDEX deliverynotes_codtarifa_m1_idx
ON deliverynotes
USING hash
(codtarifa );
psql:testA_prepare_full_hash.sql:389: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 313,339 ms
CREATE INDEX deliverynotes_codtarifaup_m1_idx
ON deliverynotes
USING hash
(upper(codtarifa::text) );
psql:testA_prepare_full_hash.sql:398: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 366,565 ms
CREATE INDEX deliverynotes_codtemporada_m1_idx
ON deliverynotes
USING hash
(codtemporada );
psql:testA_prepare_full_hash.sql:407: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 109,940 ms
CREATE INDEX deliverynotes_codtemporadaup_m1_idx
ON deliverynotes
USING hash
(upper(codtemporada::text) );
psql:testA_prepare_full_hash.sql:416: WARNING: hash indexes are not WAL-logged and their use is discouraged
CREATE INDEX
Time: 118,194 ms
CREATE INDEX deliverynotes_fecha_idalbaran
ON deliverynotes
USING hash
(fecha, idalbaran);
psql:testA_prepare_full_hash.sql:425: WARNING: hash indexes are not WAL-logged and their use is discouraged
psql:testA_prepare_full_hash.sql:425: ERROR: access method "hash" does not support multicolumn indexes
Time: 0,248 ms
\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
\timing
Timing is on.
\pset pager always
Pager is always used.
DROP TABLE IF EXISTS deliverynotes CASCADE;
psql:testA_prepare_full_btree.sql:1: NOTICE: table "deliverynotes" does not exist, skipping
DROP TABLE
Time: 0,259 ms
CREATE TEMP TABLE deliverynotes AS (SELECT * FROM albaranescli ORDER BY idalbaran);
SELECT 333694
Time: 559,081 ms
ALTER TABLE deliverynotes SET (fillfactor = 60);
ALTER TABLE
Time: 0,393 ms
ALTER TABLE deliverynotes ADD CONSTRAINT deliverynotes_pkey
PRIMARY KEY(idalbaran) WITH (FILLFACTOR=60);
ALTER TABLE
Time: 925,882 ms
CLUSTER deliverynotes USING deliverynotes_pkey;
CLUSTER
Time: 1715,057 ms
CREATE INDEX deliverynotes_codagencia_m1_idx
ON deliverynotes
USING btree
(codagencia );
CREATE INDEX
Time: 680,499 ms
CREATE INDEX deliverynotes_codagenciaup_m1_idx
ON deliverynotes
USING btree
(upper(codagencia::text) );
CREATE INDEX
Time: 832,936 ms
CREATE INDEX deliverynotes_codagente2_m1_idx
ON deliverynotes
USING btree
(codagente2 );
CREATE INDEX
Time: 297,703 ms
CREATE INDEX deliverynotes_codagente2up_m1_idx
ON deliverynotes
USING btree
(upper(codagente2::text) );
CREATE INDEX
Time: 298,479 ms
CREATE INDEX deliverynotes_codagente3_m1_idx
ON deliverynotes
USING btree
(codagente3 );
CREATE INDEX
Time: 204,510 ms
CREATE INDEX deliverynotes_codagente3up_m1_idx
ON deliverynotes
USING btree
(upper(codagente3::text) );
CREATE INDEX
Time: 211,650 ms
CREATE INDEX deliverynotes_codagente_m1_idx
ON deliverynotes
USING btree
(codagente );
CREATE INDEX
Time: 492,391 ms
CREATE INDEX deliverynotes_codagenteup_m1_idx
ON deliverynotes
USING btree
(upper(codagente::text) );
CREATE INDEX
Time: 619,822 ms
CREATE INDEX deliverynotes_codalmacen_m1_idx
ON deliverynotes
USING btree
(codalmacen );
CREATE INDEX
Time: 507,410 ms
CREATE INDEX deliverynotes_codalmacenup_m1_idx
ON deliverynotes
USING btree
(upper(codalmacen::text) );
CREATE INDEX
Time: 632,033 ms
CREATE INDEX deliverynotes_codcentro_m1_idx
ON deliverynotes
USING btree
(codcentro );
CREATE INDEX
Time: 208,131 ms
CREATE INDEX deliverynotes_codcentroup_m1_idx
ON deliverynotes
USING btree
(upper(codcentro::text) );
CREATE INDEX
Time: 216,380 ms
CREATE INDEX deliverynotes_codcliente_m1_idx
ON deliverynotes
USING btree
(codcliente );
CREATE INDEX
Time: 485,892 ms
CREATE INDEX deliverynotes_codclienteup_m1_idx
ON deliverynotes
USING btree
(upper(codcliente::text) );
CREATE INDEX
Time: 628,484 ms
CREATE INDEX deliverynotes_codcondicionentrega_m1_idx
ON deliverynotes
USING btree
(codcondicionentrega );
CREATE INDEX
Time: 413,458 ms
CREATE INDEX deliverynotes_codcondicionentregaup_m1_idx
ON deliverynotes
USING btree
(upper(codcondicionentrega::text) );
CREATE INDEX
Time: 397,060 ms
CREATE INDEX deliverynotes_coddir_cobro_m1_idx
ON deliverynotes
USING btree
(coddir_cobro);
CREATE INDEX
Time: 304,521 ms
CREATE INDEX deliverynotes_coddir_envio_m1_idx
ON deliverynotes
USING btree
(coddir_envio);
CREATE INDEX
Time: 316,099 ms
CREATE INDEX deliverynotes_coddir_m1_idx
ON deliverynotes
USING btree
(coddir);
CREATE INDEX
Time: 288,719 ms
CREATE INDEX deliverynotes_coddivisa_m1_idx
ON deliverynotes
USING btree
(coddivisa );
CREATE INDEX
Time: 637,769 ms
CREATE INDEX deliverynotes_coddivisaup_m1_idx
ON deliverynotes
USING btree
(upper(coddivisa::text) );
CREATE INDEX
Time: 782,542 ms
CREATE INDEX deliverynotes_codejercicio_m1_idx
ON deliverynotes
USING btree
(codejercicio );
CREATE INDEX
Time: 640,637 ms
CREATE INDEX deliverynotes_codejercicioup_m1_idx
ON deliverynotes
USING btree
(upper(codejercicio::text) );
CREATE INDEX
Time: 783,519 ms
CREATE INDEX deliverynotes_codimpuestoportes_m1_idx
ON deliverynotes
USING btree
(codimpuestoportes );
CREATE INDEX
Time: 553,397 ms
CREATE INDEX deliverynotes_codimpuestoportesup_m1_idx
ON deliverynotes
USING btree
(upper(codimpuestoportes::text) );
CREATE INDEX
Time: 684,925 ms
CREATE INDEX deliverynotes_codincoterm_m1_idx
ON deliverynotes
USING btree
(codincoterm );
CREATE INDEX
Time: 585,545 ms
CREATE INDEX deliverynotes_codincotermup_m1_idx
ON deliverynotes
USING btree
(upper(codincoterm::text) );
CREATE INDEX
Time: 747,657 ms
CREATE INDEX deliverynotes_codmodotransporte_m1_idx
ON deliverynotes
USING btree
(codmodotransporte );
CREATE INDEX
Time: 359,766 ms
CREATE INDEX deliverynotes_codmodotransporteup_m1_idx
ON deliverynotes
USING btree
(upper(codmodotransporte::text) );
CREATE INDEX
Time: 394,125 ms
CREATE INDEX deliverynotes_codnaturaleza_m1_idx
ON deliverynotes
USING btree
(codnaturaleza );
CREATE INDEX
Time: 356,063 ms
CREATE INDEX deliverynotes_codnaturalezaup_m1_idx
ON deliverynotes
USING btree
(upper(codnaturaleza::text) );
CREATE INDEX
Time: 399,534 ms
CREATE INDEX deliverynotes_codpacking_m1_idx
ON deliverynotes
USING btree
(codpacking );
CREATE INDEX
Time: 511,441 ms
CREATE INDEX deliverynotes_codpackingup_m1_idx
ON deliverynotes
USING btree
(upper(codpacking::text) );
CREATE INDEX
Time: 553,930 ms
CREATE INDEX deliverynotes_codpago_m1_idx
ON deliverynotes
USING btree
(codpago );
CREATE INDEX
Time: 531,323 ms
CREATE INDEX deliverynotes_codpagoup_m1_idx
ON deliverynotes
USING btree
(upper(codpago::text) );
CREATE INDEX
Time: 690,944 ms
CREATE INDEX deliverynotes_codpais_cobro_m1_idx
ON deliverynotes
USING btree
(codpais_cobro );
CREATE INDEX
Time: 557,192 ms
CREATE INDEX deliverynotes_codpais_cobroup_m1_idx
ON deliverynotes
USING btree
(upper(codpais_cobro::text) );
CREATE INDEX
Time: 677,042 ms
CREATE INDEX deliverynotes_codpais_envio_m1_idx
ON deliverynotes
USING btree
(codpais_envio );
CREATE INDEX
Time: 576,360 ms
CREATE INDEX deliverynotes_codpais_envioup_m1_idx
ON deliverynotes
USING btree
(upper(codpais_envio::text) );
CREATE INDEX
Time: 715,659 ms
CREATE INDEX deliverynotes_codpais_m1_idx
ON deliverynotes
USING btree
(codpais );
CREATE INDEX
Time: 541,132 ms
CREATE INDEX deliverynotes_codpaisup_m1_idx
ON deliverynotes
USING btree
(upper(codpais::text) );
CREATE INDEX
Time: 675,340 ms
CREATE INDEX deliverynotes_codpuerto_m1_idx
ON deliverynotes
USING btree
(codpuerto );
CREATE INDEX
Time: 208,124 ms
CREATE INDEX deliverynotes_codpuertoup_m1_idx
ON deliverynotes
USING btree
(upper(codpuerto::text) );
CREATE INDEX
Time: 214,160 ms
CREATE INDEX deliverynotes_codregimen_m1_idx
ON deliverynotes
USING btree
(codregimen );
CREATE INDEX
Time: 359,536 ms
CREATE INDEX deliverynotes_codregimenup_m1_idx
ON deliverynotes
USING btree
(upper(codregimen::text) );
CREATE INDEX
Time: 400,221 ms
CREATE INDEX deliverynotes_codserie_m1_idx
ON deliverynotes
USING btree
(codserie );
CREATE INDEX
Time: 493,850 ms
CREATE INDEX deliverynotes_codserieup_m1_idx
ON deliverynotes
USING btree
(upper(codserie::text) );
CREATE INDEX
Time: 631,314 ms
CREATE INDEX deliverynotes_codsubcentro_m1_idx
ON deliverynotes
USING btree
(codsubcentro );
CREATE INDEX
Time: 205,429 ms
CREATE INDEX deliverynotes_codsubcentroup_m1_idx
ON deliverynotes
USING btree
(upper(codsubcentro::text) );
CREATE INDEX
Time: 213,844 ms
CREATE INDEX deliverynotes_codtarifa2_m1_idx
ON deliverynotes
USING btree
(codtarifa2 );
CREATE INDEX
Time: 349,594 ms
CREATE INDEX deliverynotes_codtarifa2up_m1_idx
ON deliverynotes
USING btree
(upper(codtarifa2::text) );
CREATE INDEX
Time: 386,811 ms
CREATE INDEX deliverynotes_codtarifa_m1_idx
ON deliverynotes
USING btree
(codtarifa );
CREATE INDEX
Time: 358,295 ms
CREATE INDEX deliverynotes_codtarifaup_m1_idx
ON deliverynotes
USING btree
(upper(codtarifa::text) );
CREATE INDEX
Time: 397,318 ms
CREATE INDEX deliverynotes_codtemporada_m1_idx
ON deliverynotes
USING btree
(codtemporada );
CREATE INDEX
Time: 204,975 ms
CREATE INDEX deliverynotes_codtemporadaup_m1_idx
ON deliverynotes
USING btree
(upper(codtemporada::text) );
CREATE INDEX
Time: 219,309 ms
CREATE INDEX deliverynotes_fecha_idalbaran
ON deliverynotes
USING btree
(fecha, idalbaran);
CREATE INDEX
Time: 357,924 ms
CREATE INDEX deliverynotes_idalbaran_m1_idx
ON deliverynotes
USING btree
(idalbaran);
CREATE INDEX
Time: 207,274 ms
CREATE INDEX deliverynotes_idpedido_m1_idx
ON deliverynotes
USING btree
(idpedido);
CREATE INDEX
Time: 272,304 ms
CREATE INDEX deliverynotes_idprealbaran_m1_idx
ON deliverynotes
USING btree
(idprealbaran);
CREATE INDEX
Time: 202,985 ms
CREATE INDEX deliverynotes_idprovincia_cobro_m1_idx
ON deliverynotes
USING btree
(idprovincia_cobro);
CREATE INDEX
Time: 283,924 ms
CREATE INDEX deliverynotes_idprovincia_envio_m1_idx
ON deliverynotes
USING btree
(idprovincia_envio);
CREATE INDEX
Time: 283,491 ms
CREATE INDEX deliverynotes_idprovincia_m1_idx
ON deliverynotes
USING btree
(idprovincia);
CREATE INDEX
Time: 307,080 ms
CREATE INDEX deliverynotes_numerocodseriecodejercicio_m1_idx
ON deliverynotes
USING btree
(numero , codserie , codejercicio );
CREATE INDEX
Time: 606,342 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_3.deliverynotes | 380 MB
pg_temp_3.deliverynotes_codagencia_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codagenciaup_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codagente2_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codagente2up_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codagente3_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codagente3up_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codagente_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codagenteup_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codalmacen_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codalmacenup_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codcentro_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codcentroup_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codcliente_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codclienteup_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codcondicionentrega_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codcondicionentregaup_m1_idx | 7344 kB
pg_temp_3.deliverynotes_coddir_cobro_m1_idx | 7344 kB
pg_temp_3.deliverynotes_coddir_envio_m1_idx | 7344 kB
pg_temp_3.deliverynotes_coddir_m1_idx | 7344 kB
pg_temp_3.deliverynotes_coddivisa_m1_idx | 7344 kB
pg_temp_3.deliverynotes_coddivisaup_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codejercicio_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codejercicioup_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codimpuestoportes_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codimpuestoportesup_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codincoterm_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codincotermup_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codmodotransporte_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codmodotransporteup_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codnaturaleza_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codnaturalezaup_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codpacking_m1_idx | 7928 kB
pg_temp_3.deliverynotes_codpackingup_m1_idx | 7928 kB
pg_temp_3.deliverynotes_codpago_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codpagoup_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codpais_cobro_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codpais_cobroup_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codpais_envio_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codpais_envioup_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codpais_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codpaisup_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codpuerto_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codpuertoup_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codregimen_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codregimenup_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codserie_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codserieup_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codsubcentro_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codsubcentroup_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codtarifa2_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codtarifa2up_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codtarifa_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codtarifaup_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codtemporada_m1_idx | 7344 kB
pg_temp_3.deliverynotes_codtemporadaup_m1_idx | 7344 kB
pg_temp_3.deliverynotes_fecha_idalbaran | 7344 kB
pg_temp_3.deliverynotes_idalbaran_m1_idx | 7344 kB
pg_temp_3.deliverynotes_idpedido_m1_idx | 7344 kB
pg_temp_3.deliverynotes_idprealbaran_m1_idx | 7344 kB
pg_temp_3.deliverynotes_idprovincia_cobro_m1_idx | 7344 kB
pg_temp_3.deliverynotes_idprovincia_envio_m1_idx | 7344 kB
pg_temp_3.deliverynotes_idprovincia_m1_idx | 7344 kB
pg_temp_3.deliverynotes_numerocodseriecodejercicio_m1_idx | 10 MB
pg_temp_3.deliverynotes_pkey | 11 MB
(65 rows)
Time: 3,246 ms
DELETE FROM deliverynotes;
DELETE 333694
Time: 1215,408 ms
INSERT INTO deliverynotes SELECT * FROM albaranescli ORDER BY idalbaran LIMIT 50000;
INSERT 0 50000
Time: 6850,466 ms
INSERT INTO deliverynotes SELECT * FROM albaranescli ORDER BY idalbaran LIMIT 50000 OFFSET 50000;
INSERT 0 50000
Time: 7106,388 ms
INSERT INTO deliverynotes SELECT * FROM albaranescli ORDER BY idalbaran LIMIT 50000 OFFSET 100000;
INSERT 0 50000
Time: 7032,721 ms
INSERT INTO deliverynotes SELECT * FROM albaranescli ORDER BY idalbaran LIMIT 50000 OFFSET 150000;
INSERT 0 50000
Time: 7118,923 ms
INSERT INTO deliverynotes SELECT * FROM albaranescli ORDER BY idalbaran LIMIT 50000 OFFSET 200000;
INSERT 0 50000
Time: 7641,104 ms
INSERT INTO deliverynotes SELECT * FROM albaranescli ORDER BY idalbaran LIMIT 50000 OFFSET 250000;
INSERT 0 50000
Time: 10490,760 ms
--VACUUM ANALYZE deliverynotes;
UPDATE deliverynotes SET codserie = '*' WHERE codserie = 'VALUE NOT IN TABLE';
UPDATE 0
Time: 1,265 ms
UPDATE deliverynotes SET codserie = '*' WHERE codserie IS NULL;
UPDATE 0
Time: 0,468 ms
UPDATE deliverynotes SET codserie = '*' WHERE codserie = 'VALUE ALSO NOT IN TABLE';
UPDATE 0
Time: 0,432 ms
UPDATE deliverynotes SET codserie = '*4' WHERE codserie = '04';
UPDATE 1
Time: 1,010 ms
UPDATE deliverynotes SET codserie = '*3' WHERE codserie = '03';
UPDATE 2258
Time: 403,827 ms
UPDATE deliverynotes SET codserie = '*7' WHERE codserie = '07';
UPDATE 5260
Time: 974,996 ms
UPDATE deliverynotes SET codserie = '*2' WHERE codserie = '02';
UPDATE 43583
Time: 7106,462 ms
SELECT COUNT(*) FROM deliverynotes WHERE codserie = '01';
count
--------
113615
(1 row)
Time: 602,056 ms
SELECT COUNT(*) FROM deliverynotes WHERE codserie = '999';
count
-------
0
(1 row)
Time: 0,498 ms
SELECT COUNT(*) FROM deliverynotes WHERE codserie IS NULL;
count
-------
0
(1 row)
Time: 0,201 ms
\timing
Timing is on.
\pset pager always
Pager is always used.
DROP TABLE IF EXISTS deliverynotes CASCADE;
psql:testA_prepare_nopkey_btree.sql:1: NOTICE: table "deliverynotes" does not exist, skipping
DROP TABLE
Time: 0,223 ms
CREATE TABLE deliverynotes AS SELECT * FROM albaranescli;
SELECT 333694
Time: 2150,532 ms
ALTER TABLE deliverynotes SET (fillfactor = 60);
ALTER TABLE
Time: 0,417 ms
CREATE INDEX deliverynotes_codagencia_m1_idx
ON deliverynotes
USING btree
(codagencia );
CREATE INDEX
Time: 769,121 ms
CREATE INDEX deliverynotes_codagenciaup_m1_idx
ON deliverynotes
USING btree
(upper(codagencia::text) );
CREATE INDEX
Time: 860,603 ms
CREATE INDEX deliverynotes_codagente2_m1_idx
ON deliverynotes
USING btree
(codagente2 );
CREATE INDEX
Time: 262,319 ms
CREATE INDEX deliverynotes_codagente2up_m1_idx
ON deliverynotes
USING btree
(upper(codagente2::text) );
CREATE INDEX
Time: 269,040 ms
CREATE INDEX deliverynotes_codagente3_m1_idx
ON deliverynotes
USING btree
(codagente3 );
CREATE INDEX
Time: 172,629 ms
CREATE INDEX deliverynotes_codagente3up_m1_idx
ON deliverynotes
USING btree
(upper(codagente3::text) );
CREATE INDEX
Time: 176,333 ms
CREATE INDEX deliverynotes_codagente_m1_idx
ON deliverynotes
USING btree
(codagente );
CREATE INDEX
Time: 485,535 ms
CREATE INDEX deliverynotes_codagenteup_m1_idx
ON deliverynotes
USING btree
(upper(codagente::text) );
CREATE INDEX
Time: 576,758 ms
CREATE INDEX deliverynotes_codalmacen_m1_idx
ON deliverynotes
USING btree
(codalmacen );
CREATE INDEX
Time: 471,998 ms
CREATE INDEX deliverynotes_codalmacenup_m1_idx
ON deliverynotes
USING btree
(upper(codalmacen::text) );
CREATE INDEX
Time: 588,948 ms
CREATE INDEX deliverynotes_codcentro_m1_idx
ON deliverynotes
USING btree
(codcentro );
CREATE INDEX
Time: 171,577 ms
CREATE INDEX deliverynotes_codcentroup_m1_idx
ON deliverynotes
USING btree
(upper(codcentro::text) );
CREATE INDEX
Time: 211,385 ms
CREATE INDEX deliverynotes_codcliente_m1_idx
ON deliverynotes
USING btree
(codcliente );
CREATE INDEX
Time: 430,421 ms
CREATE INDEX deliverynotes_codclienteup_m1_idx
ON deliverynotes
USING btree
(upper(codcliente::text) );
CREATE INDEX
Time: 582,916 ms
CREATE INDEX deliverynotes_codcondicionentrega_m1_idx
ON deliverynotes
USING btree
(codcondicionentrega );
CREATE INDEX
Time: 467,625 ms
CREATE INDEX deliverynotes_codcondicionentregaup_m1_idx
ON deliverynotes
USING btree
(upper(codcondicionentrega::text) );
CREATE INDEX
Time: 359,508 ms
CREATE INDEX deliverynotes_coddir_cobro_m1_idx
ON deliverynotes
USING btree
(coddir_cobro);
CREATE INDEX
Time: 297,575 ms
CREATE INDEX deliverynotes_coddir_envio_m1_idx
ON deliverynotes
USING btree
(coddir_envio);
CREATE INDEX
Time: 280,294 ms
CREATE INDEX deliverynotes_coddir_m1_idx
ON deliverynotes
USING btree
(coddir);
CREATE INDEX
Time: 257,795 ms
CREATE INDEX deliverynotes_coddivisa_m1_idx
ON deliverynotes
USING btree
(coddivisa );
CREATE INDEX
Time: 592,786 ms
CREATE INDEX deliverynotes_coddivisaup_m1_idx
ON deliverynotes
USING btree
(upper(coddivisa::text) );
CREATE INDEX
Time: 741,042 ms
CREATE INDEX deliverynotes_codejercicio_m1_idx
ON deliverynotes
USING btree
(codejercicio );
CREATE INDEX
Time: 593,067 ms
CREATE INDEX deliverynotes_codejercicioup_m1_idx
ON deliverynotes
USING btree
(upper(codejercicio::text) );
CREATE INDEX
Time: 739,136 ms
CREATE INDEX deliverynotes_codimpuestoportes_m1_idx
ON deliverynotes
USING btree
(codimpuestoportes );
CREATE INDEX
Time: 516,050 ms
CREATE INDEX deliverynotes_codimpuestoportesup_m1_idx
ON deliverynotes
USING btree
(upper(codimpuestoportes::text) );
CREATE INDEX
Time: 654,510 ms
CREATE INDEX deliverynotes_codincoterm_m1_idx
ON deliverynotes
USING btree
(codincoterm );
CREATE INDEX
Time: 536,325 ms
CREATE INDEX deliverynotes_codincotermup_m1_idx
ON deliverynotes
USING btree
(upper(codincoterm::text) );
CREATE INDEX
Time: 688,265 ms
CREATE INDEX deliverynotes_codmodotransporte_m1_idx
ON deliverynotes
USING btree
(codmodotransporte );
CREATE INDEX
Time: 321,644 ms
CREATE INDEX deliverynotes_codmodotransporteup_m1_idx
ON deliverynotes
USING btree
(upper(codmodotransporte::text) );
CREATE INDEX
Time: 353,245 ms
CREATE INDEX deliverynotes_codnaturaleza_m1_idx
ON deliverynotes
USING btree
(codnaturaleza );
CREATE INDEX
Time: 320,535 ms
CREATE INDEX deliverynotes_codnaturalezaup_m1_idx
ON deliverynotes
USING btree
(upper(codnaturaleza::text) );
CREATE INDEX
Time: 354,979 ms
CREATE INDEX deliverynotes_codpacking_m1_idx
ON deliverynotes
USING btree
(codpacking );
CREATE INDEX
Time: 476,384 ms
CREATE INDEX deliverynotes_codpackingup_m1_idx
ON deliverynotes
USING btree
(upper(codpacking::text) );
CREATE INDEX
Time: 512,284 ms
CREATE INDEX deliverynotes_codpago_m1_idx
ON deliverynotes
USING btree
(codpago );
CREATE INDEX
Time: 489,683 ms
CREATE INDEX deliverynotes_codpagoup_m1_idx
ON deliverynotes
USING btree
(upper(codpago::text) );
CREATE INDEX
Time: 629,443 ms
CREATE INDEX deliverynotes_codpais_cobro_m1_idx
ON deliverynotes
USING btree
(codpais_cobro );
CREATE INDEX
Time: 504,022 ms
CREATE INDEX deliverynotes_codpais_cobroup_m1_idx
ON deliverynotes
USING btree
(upper(codpais_cobro::text) );
CREATE INDEX
Time: 648,934 ms
CREATE INDEX deliverynotes_codpais_envio_m1_idx
ON deliverynotes
USING btree
(codpais_envio );
CREATE INDEX
Time: 504,089 ms
CREATE INDEX deliverynotes_codpais_envioup_m1_idx
ON deliverynotes
USING btree
(upper(codpais_envio::text) );
CREATE INDEX
Time: 634,368 ms
CREATE INDEX deliverynotes_codpais_m1_idx
ON deliverynotes
USING btree
(codpais );
CREATE INDEX
Time: 486,258 ms
CREATE INDEX deliverynotes_codpaisup_m1_idx
ON deliverynotes
USING btree
(upper(codpais::text) );
CREATE INDEX
Time: 629,375 ms
CREATE INDEX deliverynotes_codpuerto_m1_idx
ON deliverynotes
USING btree
(codpuerto );
CREATE INDEX
Time: 172,010 ms
CREATE INDEX deliverynotes_codpuertoup_m1_idx
ON deliverynotes
USING btree
(upper(codpuerto::text) );
CREATE INDEX
Time: 210,551 ms
CREATE INDEX deliverynotes_codregimen_m1_idx
ON deliverynotes
USING btree
(codregimen );
CREATE INDEX
Time: 322,403 ms
CREATE INDEX deliverynotes_codregimenup_m1_idx
ON deliverynotes
USING btree
(upper(codregimen::text) );
CREATE INDEX
Time: 360,240 ms
CREATE INDEX deliverynotes_codserie_m1_idx
ON deliverynotes
USING btree
(codserie );
CREATE INDEX
Time: 462,936 ms
CREATE INDEX deliverynotes_codserieup_m1_idx
ON deliverynotes
USING btree
(upper(codserie::text) );
CREATE INDEX
Time: 603,029 ms
CREATE INDEX deliverynotes_codsubcentro_m1_idx
ON deliverynotes
USING btree
(codsubcentro );
CREATE INDEX
Time: 200,422 ms
CREATE INDEX deliverynotes_codsubcentroup_m1_idx
ON deliverynotes
USING btree
(upper(codsubcentro::text) );
CREATE INDEX
Time: 180,771 ms
CREATE INDEX deliverynotes_codtarifa2_m1_idx
ON deliverynotes
USING btree
(codtarifa2 );
CREATE INDEX
Time: 322,750 ms
CREATE INDEX deliverynotes_codtarifa2up_m1_idx
ON deliverynotes
USING btree
(upper(codtarifa2::text) );
CREATE INDEX
Time: 348,211 ms
CREATE INDEX deliverynotes_codtarifa_m1_idx
ON deliverynotes
USING btree
(codtarifa );
CREATE INDEX
Time: 330,639 ms
CREATE INDEX deliverynotes_codtarifaup_m1_idx
ON deliverynotes
USING btree
(upper(codtarifa::text) );
CREATE INDEX
Time: 361,978 ms
CREATE INDEX deliverynotes_codtemporada_m1_idx
ON deliverynotes
USING btree
(codtemporada );
CREATE INDEX
Time: 176,283 ms
CREATE INDEX deliverynotes_codtemporadaup_m1_idx
ON deliverynotes
USING btree
(upper(codtemporada::text) );
CREATE INDEX
Time: 174,598 ms
CREATE INDEX deliverynotes_fecha_idalbaran
ON deliverynotes
USING btree
(fecha, idalbaran);
CREATE INDEX
Time: 318,028 ms
CREATE INDEX deliverynotes_idalbaran_m1_idx
ON deliverynotes
USING btree
(idalbaran);
CREATE INDEX
Time: 179,242 ms
CREATE INDEX deliverynotes_idpedido_m1_idx
ON deliverynotes
USING btree
(idpedido);
CREATE INDEX
Time: 275,244 ms
CREATE INDEX deliverynotes_idprealbaran_m1_idx
ON deliverynotes
USING btree
(idprealbaran);
CREATE INDEX
Time: 172,539 ms
CREATE INDEX deliverynotes_idprovincia_cobro_m1_idx
ON deliverynotes
USING btree
(idprovincia_cobro);
CREATE INDEX
Time: 288,095 ms
CREATE INDEX deliverynotes_idprovincia_envio_m1_idx
ON deliverynotes
USING btree
(idprovincia_envio);
CREATE INDEX
Time: 252,984 ms
CREATE INDEX deliverynotes_idprovincia_m1_idx
ON deliverynotes
USING btree
(idprovincia);
CREATE INDEX
Time: 266,216 ms
CREATE INDEX deliverynotes_numerocodseriecodejercicio_m1_idx
ON deliverynotes
USING btree
(numero , codserie , codejercicio );
CREATE INDEX
Time: 566,381 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
--------------------------------------------------------+---------
public.deliverynotes | 219 MB
public.deliverynotes_codagencia_m1_idx | 7344 kB
public.deliverynotes_codagenciaup_m1_idx | 7344 kB
public.deliverynotes_codagente2_m1_idx | 7344 kB
public.deliverynotes_codagente2up_m1_idx | 7344 kB
public.deliverynotes_codagente3_m1_idx | 7344 kB
public.deliverynotes_codagente3up_m1_idx | 7344 kB
public.deliverynotes_codagente_m1_idx | 7344 kB
public.deliverynotes_codagenteup_m1_idx | 7344 kB
public.deliverynotes_codalmacen_m1_idx | 7344 kB
public.deliverynotes_codalmacenup_m1_idx | 7344 kB
public.deliverynotes_codcentro_m1_idx | 7344 kB
public.deliverynotes_codcentroup_m1_idx | 7344 kB
public.deliverynotes_codcliente_m1_idx | 7344 kB
public.deliverynotes_codclienteup_m1_idx | 7344 kB
public.deliverynotes_codcondicionentrega_m1_idx | 7344 kB
public.deliverynotes_codcondicionentregaup_m1_idx | 7344 kB
public.deliverynotes_coddir_cobro_m1_idx | 7344 kB
public.deliverynotes_coddir_envio_m1_idx | 7344 kB
public.deliverynotes_coddir_m1_idx | 7344 kB
public.deliverynotes_coddivisa_m1_idx | 7344 kB
public.deliverynotes_coddivisaup_m1_idx | 7344 kB
public.deliverynotes_codejercicio_m1_idx | 7344 kB
public.deliverynotes_codejercicioup_m1_idx | 7344 kB
public.deliverynotes_codimpuestoportes_m1_idx | 7344 kB
public.deliverynotes_codimpuestoportesup_m1_idx | 7344 kB
public.deliverynotes_codincoterm_m1_idx | 7344 kB
public.deliverynotes_codincotermup_m1_idx | 7344 kB
public.deliverynotes_codmodotransporte_m1_idx | 7344 kB
public.deliverynotes_codmodotransporteup_m1_idx | 7344 kB
public.deliverynotes_codnaturaleza_m1_idx | 7344 kB
public.deliverynotes_codnaturalezaup_m1_idx | 7344 kB
public.deliverynotes_codpacking_m1_idx | 7928 kB
public.deliverynotes_codpackingup_m1_idx | 7928 kB
public.deliverynotes_codpago_m1_idx | 7344 kB
public.deliverynotes_codpagoup_m1_idx | 7344 kB
public.deliverynotes_codpais_cobro_m1_idx | 7344 kB
public.deliverynotes_codpais_cobroup_m1_idx | 7344 kB
public.deliverynotes_codpais_envio_m1_idx | 7344 kB
public.deliverynotes_codpais_envioup_m1_idx | 7344 kB
public.deliverynotes_codpais_m1_idx | 7344 kB
public.deliverynotes_codpaisup_m1_idx | 7344 kB
public.deliverynotes_codpuerto_m1_idx | 7344 kB
public.deliverynotes_codpuertoup_m1_idx | 7344 kB
public.deliverynotes_codregimen_m1_idx | 7344 kB
public.deliverynotes_codregimenup_m1_idx | 7344 kB
public.deliverynotes_codserie_m1_idx | 7344 kB
public.deliverynotes_codserieup_m1_idx | 7344 kB
public.deliverynotes_codsubcentro_m1_idx | 7344 kB
public.deliverynotes_codsubcentroup_m1_idx | 7344 kB
public.deliverynotes_codtarifa2_m1_idx | 7344 kB
public.deliverynotes_codtarifa2up_m1_idx | 7344 kB
public.deliverynotes_codtarifa_m1_idx | 7344 kB
public.deliverynotes_codtarifaup_m1_idx | 7344 kB
public.deliverynotes_codtemporada_m1_idx | 7344 kB
public.deliverynotes_codtemporadaup_m1_idx | 7344 kB
public.deliverynotes_fecha_idalbaran | 7344 kB
public.deliverynotes_idalbaran_m1_idx | 7344 kB
public.deliverynotes_idpedido_m1_idx | 7344 kB
public.deliverynotes_idprealbaran_m1_idx | 7344 kB
public.deliverynotes_idprovincia_cobro_m1_idx | 7344 kB
public.deliverynotes_idprovincia_envio_m1_idx | 7344 kB
public.deliverynotes_idprovincia_m1_idx | 7344 kB
public.deliverynotes_numerocodseriecodejercicio_m1_idx | 10 MB
(64 rows)
Time: 12,417 ms
DELETE FROM deliverynotes;
DELETE 333694
Time: 408,119 ms
INSERT INTO deliverynotes SELECT * FROM albaranescli ORDER BY idalbaran LIMIT 50000;
INSERT 0 50000
Time: 9805,186 ms
INSERT INTO deliverynotes SELECT * FROM albaranescli ORDER BY idalbaran LIMIT 50000 OFFSET 50000;
INSERT 0 50000
Time: 9712,850 ms
INSERT INTO deliverynotes SELECT * FROM albaranescli ORDER BY idalbaran LIMIT 50000 OFFSET 100000;
INSERT 0 50000
Time: 9761,828 ms
INSERT INTO deliverynotes SELECT * FROM albaranescli ORDER BY idalbaran LIMIT 50000 OFFSET 150000;
INSERT 0 50000
Time: 9262,881 ms
INSERT INTO deliverynotes SELECT * FROM albaranescli ORDER BY idalbaran LIMIT 50000 OFFSET 200000;
INSERT 0 50000
Time: 9786,689 ms
INSERT INTO deliverynotes SELECT * FROM albaranescli ORDER BY idalbaran LIMIT 50000 OFFSET 250000;
INSERT 0 50000
Time: 11998,232 ms
--VACUUM ANALYZE deliverynotes;
UPDATE deliverynotes SET codserie = '*' WHERE codserie = 'VALUE NOT IN TABLE';
UPDATE 0
Time: 11,156 ms
UPDATE deliverynotes SET codserie = '*4' WHERE codserie = '04';
UPDATE 1
Time: 1,275 ms
UPDATE deliverynotes SET codserie = '*3' WHERE codserie = '03';
UPDATE 2258
Time: 461,848 ms
UPDATE deliverynotes SET codserie = '*7' WHERE codserie = '07';
UPDATE 5260
Time: 1192,677 ms
UPDATE deliverynotes SET codserie = '*2' WHERE codserie = '02';
UPDATE 43583
Time: 9596,318 ms
UPDATE deliverynotes SET codserie = '*1' WHERE codserie = '01';
UPDATE 113615
Time: 24883,813 ms
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
