--------------- fastcomp -------------

drop table if exists lotsofints;
DROP TABLE
drop table if exists lotsoftext;
DROP TABLE
drop table if exists lotsofitext;
DROP TABLE
create table lotsofints(i integer);
CREATE TABLE
create table lotsoftext(i text);
CREATE TABLE
create table lotsofitext(i text, j text, w text, z integer, z2 bigint);
CREATE TABLE
insert into lotsofints select random() * 1000000000.0 from generate_series(1, 10000000);
INSERT 0 10000000
insert into lotsoftext select cast(random() * 1000000000.0 as text) || 'blablablawiiiiblabla' from generate_series(1, 10000000);
INSERT 0 10000000
insert into lotsofitext select cast(random() * 1000000000.0 as text) || 'blablablawiiiiblabla', cast(random() * 1000000000.0 as text) || 'blablablawjjjblabla', cast(random() * 1000000000.0 as text) || 'blablablawwwabla', random() * 1000000000.0, random() * 1000000000000.0 from generate_series(1, 10000000);
INSERT 0 10000000
vacuum freeze;
VACUUM
select relname, pg_size_pretty(pg_total_relation_size(relname::regclass)) from pg_class where relname in ('lotsofints', 'lotsoftext', 'lotsofitext');
   relname   | pg_size_pretty 
-------------+----------------
 lotsofints  | 346 MB
 lotsofitext | 1421 MB
 lotsoftext  | 651 MB
(3 rows)

Timing is on.
show work_mem;
 work_mem 
----------
 4MB
(1 row)

Time: 0.345 ms
show maintenance_work_mem;
 maintenance_work_mem 
----------------------
 4MB
(1 row)

Time: 0.125 ms
select count(*) FROM (select * from lotsofints order by i) t;
  count   
----------
 10000000
(1 row)

Time: 8247.819 ms
select count(*) FROM (select * from lotsofints order by i) t;
  count   
----------
 10000000
(1 row)

Time: 7271.497 ms
select count(*) FROM (select * from lotsofints order by i) t;
  count   
----------
 10000000
(1 row)

Time: 7483.717 ms
select count(*) FROM (select * from lotsofints order by i) t;
  count   
----------
 10000000
(1 row)

Time: 7402.999 ms
select count(*) FROM (select * from lotsoftext order by i) t;
  count   
----------
 10000000
(1 row)

Time: 88055.918 ms
select count(*) FROM (select * from lotsoftext order by i) t;
  count   
----------
 10000000
(1 row)

Time: 85790.311 ms
select count(*) FROM (select * from lotsoftext order by i) t;
  count   
----------
 10000000
(1 row)

Time: 86631.399 ms
select count(*) FROM (select * from lotsoftext order by i) t;
  count   
----------
 10000000
(1 row)

Time: 88225.412 ms
select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 100562.362 ms
select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 101264.557 ms
select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 100217.564 ms
select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 100648.903 ms
select count(*) FROM (select * from lotsofitext order by i, j) t;
  count   
----------
 10000000
(1 row)

Time: 93581.302 ms
select count(*) FROM (select * from lotsofitext order by i, j) t;
  count   
----------
 10000000
(1 row)

Time: 93901.188 ms
select count(*) FROM (select * from lotsofitext order by i, j) t;
  count   
----------
 10000000
(1 row)

Time: 93518.708 ms
select count(*) FROM (select * from lotsofitext order by i, j) t;
  count   
----------
 10000000
(1 row)

Time: 94696.845 ms
select count(*) FROM (select * from lotsofitext order by z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 9619.633 ms
select count(*) FROM (select * from lotsofitext order by z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 10200.492 ms
select count(*) FROM (select * from lotsofitext order by z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 9670.765 ms
select count(*) FROM (select * from lotsofitext order by z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 11217.425 ms
select count(*) FROM (select * from lotsofitext order by i, z) t;
  count   
----------
 10000000
(1 row)

Time: 87931.169 ms
select count(*) FROM (select * from lotsofitext order by i, z) t;
  count   
----------
 10000000
(1 row)

Time: 91079.913 ms
select count(*) FROM (select * from lotsofitext order by i, z) t;
  count   
----------
 10000000
(1 row)

Time: 90111.878 ms
select count(*) FROM (select * from lotsofitext order by i, z) t;
  count   
----------
 10000000
(1 row)

Time: 89602.678 ms
select count(*) FROM lotsofints;
  count   
----------
 10000000
(1 row)

Time: 285.544 ms
CREATE INDEX ix_lotsofints ON lotsofints (i);
CREATE INDEX
Time: 8379.929 ms
DROP INDEX ix_lotsofints;
DROP INDEX
Time: 52.152 ms
CREATE INDEX ix_lotsofints ON lotsofints (i);
CREATE INDEX
Time: 8080.292 ms
DROP INDEX ix_lotsofints;
DROP INDEX
Time: 23.166 ms
CREATE INDEX ix_lotsofints ON lotsofints (i);
CREATE INDEX
Time: 8035.267 ms
DROP INDEX ix_lotsofints;
DROP INDEX
Time: 23.234 ms
CREATE INDEX ix_lotsofints ON lotsofints (i);
CREATE INDEX
Time: 8343.562 ms
DROP INDEX ix_lotsofints;
DROP INDEX
Time: 26.176 ms
select count(*) FROM lotsoftext;
  count   
----------
 10000000
(1 row)

Time: 334.882 ms
CREATE INDEX ix_lotsoftext ON lotsoftext (i);
CREATE INDEX
Time: 87211.606 ms
DROP INDEX ix_lotsoftext;
DROP INDEX
Time: 1231.606 ms
CREATE INDEX ix_lotsoftext ON lotsoftext (i);
CREATE INDEX
Time: 97248.906 ms
DROP INDEX ix_lotsoftext;
DROP INDEX
Time: 55.456 ms
CREATE INDEX ix_lotsoftext ON lotsoftext (i);
CREATE INDEX
Time: 86236.154 ms
DROP INDEX ix_lotsoftext;
DROP INDEX
Time: 56.363 ms
CREATE INDEX ix_lotsoftext ON lotsoftext (i);
CREATE INDEX
Time: 87240.065 ms
DROP INDEX ix_lotsoftext;
DROP INDEX
Time: 54.159 ms
select count(*) FROM lotsofitext;
  count   
----------
 10000000
(1 row)

Time: 357.192 ms
CREATE INDEX ix_lotsofitext ON lotsofitext (i);
CREATE INDEX
Time: 87135.964 ms
DROP INDEX ix_lotsofitext;
DROP INDEX
Time: 55.294 ms
CREATE INDEX ix_lotsofitext ON lotsofitext (i);
CREATE INDEX
Time: 86178.536 ms
DROP INDEX ix_lotsofitext;
DROP INDEX
Time: 57.629 ms
CREATE INDEX ix_lotsofitext ON lotsofitext (i);
CREATE INDEX
Time: 88941.996 ms
DROP INDEX ix_lotsofitext;
DROP INDEX
Time: 61.721 ms
CREATE INDEX ix_lotsofitext ON lotsofitext (i);
CREATE INDEX
Time: 87040.692 ms
DROP INDEX ix_lotsofitext;
DROP INDEX
Time: 56.118 ms
CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2);
CREATE INDEX
Time: 99010.482 ms
DROP INDEX ix_lotsofitext_ijwzz2;
DROP INDEX
Time: 2362.977 ms
CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2);
CREATE INDEX
Time: 96910.425 ms
DROP INDEX ix_lotsofitext_ijwzz2;
DROP INDEX
Time: 1400.053 ms
CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2);
CREATE INDEX
Time: 96679.436 ms
DROP INDEX ix_lotsofitext_ijwzz2;
DROP INDEX
Time: 629.087 ms
CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2);
CREATE INDEX
Time: 102430.816 ms
DROP INDEX ix_lotsofitext_ijwzz2;
DROP INDEX
Time: 3221.402 ms
CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w);
CREATE INDEX
Time: 36328.470 ms
DROP INDEX ix_lotsofitext_zz2ijw;
DROP INDEX
Time: 1560.923 ms
CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w);
CREATE INDEX
Time: 36576.948 ms
DROP INDEX ix_lotsofitext_zz2ijw;
DROP INDEX
Time: 1167.035 ms
CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w);
CREATE INDEX
Time: 36280.781 ms
DROP INDEX ix_lotsofitext_zz2ijw;
DROP INDEX
Time: 1340.001 ms
CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w);
CREATE INDEX
Time: 40800.810 ms
DROP INDEX ix_lotsofitext_zz2ijw;
DROP INDEX
Time: 2114.063 ms
CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2);
CREATE INDEX
Time: 9861.137 ms
DROP INDEX ix_lotsofitext_zz2;
DROP INDEX
Time: 31.622 ms
CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2);
CREATE INDEX
Time: 9688.734 ms
DROP INDEX ix_lotsofitext_zz2;
DROP INDEX
Time: 31.957 ms
CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2);
CREATE INDEX
Time: 9549.317 ms
DROP INDEX ix_lotsofitext_zz2;
DROP INDEX
Time: 31.831 ms
CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2);
CREATE INDEX
Time: 9444.227 ms
DROP INDEX ix_lotsofitext_zz2;
DROP INDEX
Time: 32.289 ms
Timing is on.
show work_mem;
 work_mem 
----------
 64MB
(1 row)

Time: 0.230 ms
show maintenance_work_mem;
 maintenance_work_mem 
----------------------
 64MB
(1 row)

Time: 0.096 ms
select count(*) FROM (select * from lotsofints order by i) t;
  count   
----------
 10000000
(1 row)

Time: 5962.549 ms
select count(*) FROM (select * from lotsofints order by i) t;
  count   
----------
 10000000
(1 row)

Time: 5929.345 ms
select count(*) FROM (select * from lotsofints order by i) t;
  count   
----------
 10000000
(1 row)

Time: 5946.179 ms
select count(*) FROM (select * from lotsofints order by i) t;
  count   
----------
 10000000
(1 row)

Time: 5950.689 ms
select count(*) FROM (select * from lotsoftext order by i) t;
  count   
----------
 10000000
(1 row)

Time: 65954.917 ms
select count(*) FROM (select * from lotsoftext order by i) t;
  count   
----------
 10000000
(1 row)

Time: 66195.161 ms
select count(*) FROM (select * from lotsoftext order by i) t;
  count   
----------
 10000000
(1 row)

Time: 64990.200 ms
select count(*) FROM (select * from lotsoftext order by i) t;
  count   
----------
 10000000
(1 row)

Time: 65542.570 ms
select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 70873.189 ms
select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 90972.217 ms
select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 100791.006 ms
select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 109060.252 ms
select count(*) FROM (select * from lotsofitext order by i, j) t;
  count   
----------
 10000000
(1 row)

Time: 108256.018 ms
select count(*) FROM (select * from lotsofitext order by i, j) t;
  count   
----------
 10000000
(1 row)

Time: 68634.753 ms
select count(*) FROM (select * from lotsofitext order by i, j) t;
  count   
----------
 10000000
(1 row)

Time: 72354.132 ms
select count(*) FROM (select * from lotsofitext order by i, j) t;
  count   
----------
 10000000
(1 row)

Time: 68454.483 ms
select count(*) FROM (select * from lotsofitext order by z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 8011.955 ms
select count(*) FROM (select * from lotsofitext order by z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 8120.676 ms
select count(*) FROM (select * from lotsofitext order by z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 8480.225 ms
select count(*) FROM (select * from lotsofitext order by z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 8102.559 ms
select count(*) FROM (select * from lotsofitext order by i, z) t;
  count   
----------
 10000000
(1 row)

Time: 69423.973 ms
select count(*) FROM (select * from lotsofitext order by i, z) t;
  count   
----------
 10000000
(1 row)

Time: 68896.534 ms
select count(*) FROM (select * from lotsofitext order by i, z) t;
  count   
----------
 10000000
(1 row)

Time: 67974.966 ms
select count(*) FROM (select * from lotsofitext order by i, z) t;
  count   
----------
 10000000
(1 row)

Time: 70004.143 ms
select count(*) FROM lotsofints;
  count   
----------
 10000000
(1 row)

Time: 5451.165 ms
CREATE INDEX ix_lotsofints ON lotsofints (i);
CREATE INDEX
Time: 8190.733 ms
DROP INDEX ix_lotsofints;
DROP INDEX
Time: 114.406 ms
CREATE INDEX ix_lotsofints ON lotsofints (i);
CREATE INDEX
Time: 7036.590 ms
DROP INDEX ix_lotsofints;
DROP INDEX
Time: 20.844 ms
CREATE INDEX ix_lotsofints ON lotsofints (i);
CREATE INDEX
Time: 6925.997 ms
DROP INDEX ix_lotsofints;
DROP INDEX
Time: 20.125 ms
CREATE INDEX ix_lotsofints ON lotsofints (i);
CREATE INDEX
Time: 7017.871 ms
DROP INDEX ix_lotsofints;
DROP INDEX
Time: 20.099 ms
select count(*) FROM lotsoftext;
  count   
----------
 10000000
(1 row)

Time: 9647.404 ms
CREATE INDEX ix_lotsoftext ON lotsoftext (i);
CREATE INDEX
Time: 70072.885 ms
DROP INDEX ix_lotsoftext;
DROP INDEX
Time: 57.559 ms
CREATE INDEX ix_lotsoftext ON lotsoftext (i);
CREATE INDEX
Time: 67613.662 ms
DROP INDEX ix_lotsoftext;
DROP INDEX
Time: 60.449 ms
CREATE INDEX ix_lotsoftext ON lotsoftext (i);
CREATE INDEX
Time: 69372.045 ms
DROP INDEX ix_lotsoftext;
DROP INDEX
Time: 1178.412 ms
CREATE INDEX ix_lotsoftext ON lotsoftext (i);
CREATE INDEX
Time: 67929.874 ms
DROP INDEX ix_lotsoftext;
DROP INDEX
Time: 54.765 ms
select count(*) FROM lotsofitext;
  count   
----------
 10000000
(1 row)

Time: 524.805 ms
CREATE INDEX ix_lotsofitext ON lotsofitext (i);
CREATE INDEX
Time: 69636.585 ms
DROP INDEX ix_lotsofitext;
DROP INDEX
Time: 53.792 ms
CREATE INDEX ix_lotsofitext ON lotsofitext (i);
CREATE INDEX
Time: 67977.737 ms
DROP INDEX ix_lotsofitext;
DROP INDEX
Time: 53.724 ms
CREATE INDEX ix_lotsofitext ON lotsofitext (i);
CREATE INDEX
Time: 68896.157 ms
DROP INDEX ix_lotsofitext;
DROP INDEX
Time: 53.954 ms
CREATE INDEX ix_lotsofitext ON lotsofitext (i);
CREATE INDEX
Time: 68263.394 ms
DROP INDEX ix_lotsofitext;
DROP INDEX
Time: 55.291 ms
CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2);
CREATE INDEX
Time: 71911.165 ms
DROP INDEX ix_lotsofitext_ijwzz2;
DROP INDEX
Time: 1631.522 ms
CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2);
CREATE INDEX
Time: 69574.277 ms
DROP INDEX ix_lotsofitext_ijwzz2;
DROP INDEX
Time: 1546.059 ms
CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2);
CREATE INDEX
Time: 69616.902 ms
DROP INDEX ix_lotsofitext_ijwzz2;
DROP INDEX
Time: 1923.577 ms
CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2);
CREATE INDEX
Time: 69789.578 ms
DROP INDEX ix_lotsofitext_ijwzz2;
DROP INDEX
Time: 1536.743 ms
CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w);
CREATE INDEX
Time: 27836.475 ms
DROP INDEX ix_lotsofitext_zz2ijw;
DROP INDEX
Time: 1210.661 ms
CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w);
CREATE INDEX
Time: 27204.272 ms
DROP INDEX ix_lotsofitext_zz2ijw;
DROP INDEX
Time: 1259.375 ms
CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w);
CREATE INDEX
Time: 26344.778 ms
DROP INDEX ix_lotsofitext_zz2ijw;
DROP INDEX
Time: 1567.841 ms
CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w);
CREATE INDEX
Time: 26156.501 ms
DROP INDEX ix_lotsofitext_zz2ijw;
DROP INDEX
Time: 1618.874 ms
CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2);
CREATE INDEX
Time: 8299.010 ms
DROP INDEX ix_lotsofitext_zz2;
DROP INDEX
Time: 33.906 ms
CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2);
CREATE INDEX
Time: 7909.547 ms
DROP INDEX ix_lotsofitext_zz2;
DROP INDEX
Time: 35.863 ms
CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2);
CREATE INDEX
Time: 8074.864 ms
DROP INDEX ix_lotsofitext_zz2;
DROP INDEX
Time: 34.646 ms
CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2);
CREATE INDEX
Time: 8067.240 ms
DROP INDEX ix_lotsofitext_zz2;
DROP INDEX
Time: 32.697 ms
Timing is on.
show work_mem;
 work_mem 
----------
 256MB
(1 row)

Time: 0.532 ms
show maintenance_work_mem;
 maintenance_work_mem 
----------------------
 256MB
(1 row)

Time: 0.157 ms
select count(*) FROM (select * from lotsofints order by i) t;
  count   
----------
 10000000
(1 row)

Time: 5238.402 ms
select count(*) FROM (select * from lotsofints order by i) t;
  count   
----------
 10000000
(1 row)

Time: 4927.063 ms
select count(*) FROM (select * from lotsofints order by i) t;
  count   
----------
 10000000
(1 row)

Time: 4914.207 ms
select count(*) FROM (select * from lotsofints order by i) t;
  count   
----------
 10000000
(1 row)

Time: 4928.470 ms
select count(*) FROM (select * from lotsoftext order by i) t;
  count   
----------
 10000000
(1 row)

Time: 64043.866 ms
select count(*) FROM (select * from lotsoftext order by i) t;
  count   
----------
 10000000
(1 row)

Time: 64115.283 ms
select count(*) FROM (select * from lotsoftext order by i) t;
  count   
----------
 10000000
(1 row)

Time: 62210.085 ms
select count(*) FROM (select * from lotsoftext order by i) t;
  count   
----------
 10000000
(1 row)

Time: 62566.898 ms
select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 70403.252 ms
select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 70086.981 ms
select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 69836.254 ms
select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 69191.581 ms
select count(*) FROM (select * from lotsofitext order by i, j) t;
  count   
----------
 10000000
(1 row)

Time: 66113.535 ms
select count(*) FROM (select * from lotsofitext order by i, j) t;
  count   
----------
 10000000
(1 row)

Time: 67118.360 ms
select count(*) FROM (select * from lotsofitext order by i, j) t;
  count   
----------
 10000000
(1 row)

Time: 66369.433 ms
select count(*) FROM (select * from lotsofitext order by i, j) t;
  count   
----------
 10000000
(1 row)

Time: 65394.115 ms
select count(*) FROM (select * from lotsofitext order by z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 7116.634 ms
select count(*) FROM (select * from lotsofitext order by z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 7113.597 ms
select count(*) FROM (select * from lotsofitext order by z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 7243.779 ms
select count(*) FROM (select * from lotsofitext order by z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 7109.149 ms
select count(*) FROM (select * from lotsofitext order by i, z) t;
  count   
----------
 10000000
(1 row)

Time: 65076.924 ms
select count(*) FROM (select * from lotsofitext order by i, z) t;
  count   
----------
 10000000
(1 row)

Time: 65166.504 ms
select count(*) FROM (select * from lotsofitext order by i, z) t;
  count   
----------
 10000000
(1 row)

Time: 64883.934 ms
select count(*) FROM (select * from lotsofitext order by i, z) t;
  count   
----------
 10000000
(1 row)

Time: 65308.198 ms
select count(*) FROM lotsofints;
  count   
----------
 10000000
(1 row)

Time: 344.651 ms
CREATE INDEX ix_lotsofints ON lotsofints (i);
CREATE INDEX
Time: 6021.399 ms
DROP INDEX ix_lotsofints;
DROP INDEX
Time: 74.954 ms
CREATE INDEX ix_lotsofints ON lotsofints (i);
CREATE INDEX
Time: 5907.484 ms
DROP INDEX ix_lotsofints;
DROP INDEX
Time: 24.178 ms
CREATE INDEX ix_lotsofints ON lotsofints (i);
CREATE INDEX
Time: 5952.605 ms
DROP INDEX ix_lotsofints;
DROP INDEX
Time: 23.934 ms
CREATE INDEX ix_lotsofints ON lotsofints (i);
CREATE INDEX
Time: 5988.847 ms
DROP INDEX ix_lotsofints;
DROP INDEX
Time: 24.103 ms
select count(*) FROM lotsoftext;
  count   
----------
 10000000
(1 row)

Time: 282.042 ms
CREATE INDEX ix_lotsoftext ON lotsoftext (i);
CREATE INDEX
Time: 63078.639 ms
DROP INDEX ix_lotsoftext;
DROP INDEX
Time: 60.191 ms
CREATE INDEX ix_lotsoftext ON lotsoftext (i);
CREATE INDEX
Time: 63290.772 ms
DROP INDEX ix_lotsoftext;
DROP INDEX
Time: 56.212 ms
CREATE INDEX ix_lotsoftext ON lotsoftext (i);
CREATE INDEX
Time: 63585.448 ms
DROP INDEX ix_lotsoftext;
DROP INDEX
Time: 61.405 ms
CREATE INDEX ix_lotsoftext ON lotsoftext (i);
CREATE INDEX
Time: 65618.446 ms
DROP INDEX ix_lotsoftext;
DROP INDEX
Time: 61.216 ms
select count(*) FROM lotsofitext;
  count   
----------
 10000000
(1 row)

Time: 416.813 ms
CREATE INDEX ix_lotsofitext ON lotsofitext (i);
CREATE INDEX
Time: 64194.769 ms
DROP INDEX ix_lotsofitext;
DROP INDEX
Time: 62.747 ms
CREATE INDEX ix_lotsofitext ON lotsofitext (i);
CREATE INDEX
Time: 65855.928 ms
DROP INDEX ix_lotsofitext;
DROP INDEX
Time: 61.806 ms
CREATE INDEX ix_lotsofitext ON lotsofitext (i);
CREATE INDEX
Time: 65479.808 ms
DROP INDEX ix_lotsofitext;
DROP INDEX
Time: 61.944 ms
CREATE INDEX ix_lotsofitext ON lotsofitext (i);
CREATE INDEX
Time: 64829.681 ms
DROP INDEX ix_lotsofitext;
DROP INDEX
Time: 62.017 ms
CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2);
CREATE INDEX
Time: 67885.561 ms
DROP INDEX ix_lotsofitext_ijwzz2;
DROP INDEX
Time: 1071.526 ms
CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2);
CREATE INDEX
Time: 67600.318 ms
DROP INDEX ix_lotsofitext_ijwzz2;
DROP INDEX
Time: 1401.400 ms
CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2);
CREATE INDEX
Time: 74365.909 ms
DROP INDEX ix_lotsofitext_ijwzz2;
DROP INDEX
Time: 1490.252 ms
CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2);
CREATE INDEX
Time: 71848.919 ms
DROP INDEX ix_lotsofitext_ijwzz2;
DROP INDEX
Time: 1541.932 ms
CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w);
CREATE INDEX
Time: 29548.021 ms
DROP INDEX ix_lotsofitext_zz2ijw;
DROP INDEX
Time: 1459.663 ms
CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w);
CREATE INDEX
Time: 29006.774 ms
DROP INDEX ix_lotsofitext_zz2ijw;
DROP INDEX
Time: 1446.781 ms
CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w);
CREATE INDEX
Time: 26782.222 ms
DROP INDEX ix_lotsofitext_zz2ijw;
DROP INDEX
Time: 1616.228 ms
CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w);
CREATE INDEX
Time: 27652.577 ms
DROP INDEX ix_lotsofitext_zz2ijw;
DROP INDEX
Time: 1454.439 ms
CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2);
CREATE INDEX
Time: 7250.859 ms
DROP INDEX ix_lotsofitext_zz2;
DROP INDEX
Time: 33.923 ms
CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2);
CREATE INDEX
Time: 7087.343 ms
DROP INDEX ix_lotsofitext_zz2;
DROP INDEX
Time: 34.815 ms
CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2);
CREATE INDEX
Time: 7119.874 ms
DROP INDEX ix_lotsofitext_zz2;
DROP INDEX
Time: 32.648 ms
CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2);
CREATE INDEX
Time: 7810.047 ms
DROP INDEX ix_lotsofitext_zz2;
DROP INDEX
Time: 35.061 ms
Timing is on.
show work_mem;
 work_mem 
----------
 1GB
(1 row)

Time: 0.269 ms
show maintenance_work_mem;
 maintenance_work_mem 
----------------------
 1GB
(1 row)

Time: 0.164 ms
select count(*) FROM (select * from lotsofints order by i) t;
  count   
----------
 10000000
(1 row)

Time: 4163.714 ms
select count(*) FROM (select * from lotsofints order by i) t;
  count   
----------
 10000000
(1 row)

Time: 4068.018 ms
select count(*) FROM (select * from lotsofints order by i) t;
  count   
----------
 10000000
(1 row)

Time: 4117.622 ms
select count(*) FROM (select * from lotsofints order by i) t;
  count   
----------
 10000000
(1 row)

Time: 4114.336 ms
select count(*) FROM (select * from lotsoftext order by i) t;
  count   
----------
 10000000
(1 row)

Time: 66081.411 ms
select count(*) FROM (select * from lotsoftext order by i) t;
  count   
----------
 10000000
(1 row)

Time: 65799.298 ms
select count(*) FROM (select * from lotsoftext order by i) t;
  count   
----------
 10000000
(1 row)

Time: 64801.901 ms
select count(*) FROM (select * from lotsoftext order by i) t;
  count   
----------
 10000000
(1 row)

Time: 64388.724 ms
select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 68758.177 ms
select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 69130.821 ms
select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 68602.217 ms
select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 67409.244 ms
select count(*) FROM (select * from lotsofitext order by i, j) t;
  count   
----------
 10000000
(1 row)

Time: 65667.452 ms
select count(*) FROM (select * from lotsofitext order by i, j) t;
  count   
----------
 10000000
(1 row)

Time: 65976.491 ms
select count(*) FROM (select * from lotsofitext order by i, j) t;
  count   
----------
 10000000
(1 row)

Time: 66503.125 ms
select count(*) FROM (select * from lotsofitext order by i, j) t;
  count   
----------
 10000000
(1 row)

Time: 67272.697 ms
select count(*) FROM (select * from lotsofitext order by z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 6087.756 ms
select count(*) FROM (select * from lotsofitext order by z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 6115.796 ms
select count(*) FROM (select * from lotsofitext order by z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 5950.558 ms
select count(*) FROM (select * from lotsofitext order by z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 5953.959 ms
select count(*) FROM (select * from lotsofitext order by i, z) t;
  count   
----------
 10000000
(1 row)

Time: 65079.381 ms
select count(*) FROM (select * from lotsofitext order by i, z) t;
  count   
----------
 10000000
(1 row)

Time: 65282.253 ms
select count(*) FROM (select * from lotsofitext order by i, z) t;
  count   
----------
 10000000
(1 row)

Time: 65699.186 ms
select count(*) FROM (select * from lotsofitext order by i, z) t;
  count   
----------
 10000000
(1 row)

Time: 65742.909 ms
select count(*) FROM lotsofints;
  count   
----------
 10000000
(1 row)

Time: 251.947 ms
CREATE INDEX ix_lotsofints ON lotsofints (i);
CREATE INDEX
Time: 5331.786 ms
DROP INDEX ix_lotsofints;
DROP INDEX
Time: 25.474 ms
CREATE INDEX ix_lotsofints ON lotsofints (i);
CREATE INDEX
Time: 5198.076 ms
DROP INDEX ix_lotsofints;
DROP INDEX
Time: 24.309 ms
CREATE INDEX ix_lotsofints ON lotsofints (i);
CREATE INDEX
Time: 5194.433 ms
DROP INDEX ix_lotsofints;
DROP INDEX
Time: 25.779 ms
CREATE INDEX ix_lotsofints ON lotsofints (i);
CREATE INDEX
Time: 5247.728 ms
DROP INDEX ix_lotsofints;
DROP INDEX
Time: 24.949 ms
select count(*) FROM lotsoftext;
  count   
----------
 10000000
(1 row)

Time: 281.871 ms
CREATE INDEX ix_lotsoftext ON lotsoftext (i);
CREATE INDEX
Time: 67415.078 ms
DROP INDEX ix_lotsoftext;
DROP INDEX
Time: 62.459 ms
CREATE INDEX ix_lotsoftext ON lotsoftext (i);
CREATE INDEX
Time: 68658.329 ms
DROP INDEX ix_lotsoftext;
DROP INDEX
Time: 332.561 ms
CREATE INDEX ix_lotsoftext ON lotsoftext (i);
CREATE INDEX
Time: 68463.136 ms
DROP INDEX ix_lotsoftext;
DROP INDEX
Time: 59.470 ms
CREATE INDEX ix_lotsoftext ON lotsoftext (i);
CREATE INDEX
Time: 69241.483 ms
DROP INDEX ix_lotsoftext;
DROP INDEX
Time: 62.692 ms
select count(*) FROM lotsofitext;
  count   
----------
 10000000
(1 row)

Time: 394.959 ms
CREATE INDEX ix_lotsofitext ON lotsofitext (i);
CREATE INDEX
Time: 69419.207 ms
DROP INDEX ix_lotsofitext;
DROP INDEX
Time: 60.489 ms
CREATE INDEX ix_lotsofitext ON lotsofitext (i);
CREATE INDEX
Time: 67546.444 ms
DROP INDEX ix_lotsofitext;
DROP INDEX
Time: 61.377 ms
CREATE INDEX ix_lotsofitext ON lotsofitext (i);
CREATE INDEX
Time: 68043.065 ms
DROP INDEX ix_lotsofitext;
DROP INDEX
Time: 493.303 ms
CREATE INDEX ix_lotsofitext ON lotsofitext (i);
CREATE INDEX
Time: 68459.294 ms
DROP INDEX ix_lotsofitext;
DROP INDEX
Time: 71.969 ms
CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2);
CREATE INDEX
Time: 78851.296 ms
DROP INDEX ix_lotsofitext_ijwzz2;
DROP INDEX
Time: 1447.856 ms
CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2);
CREATE INDEX
Time: 75358.464 ms
DROP INDEX ix_lotsofitext_ijwzz2;
DROP INDEX
Time: 1456.660 ms
CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2);
CREATE INDEX
Time: 75431.096 ms
DROP INDEX ix_lotsofitext_ijwzz2;
DROP INDEX
Time: 1648.962 ms
CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2);
CREATE INDEX
Time: 79749.501 ms
DROP INDEX ix_lotsofitext_ijwzz2;
DROP INDEX
Time: 1556.506 ms
CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w);
CREATE INDEX
Time: 33877.126 ms
DROP INDEX ix_lotsofitext_zz2ijw;
DROP INDEX
Time: 393.079 ms
CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w);
CREATE INDEX
Time: 30728.549 ms
DROP INDEX ix_lotsofitext_zz2ijw;
DROP INDEX
Time: 1412.385 ms
CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w);
CREATE INDEX
Time: 34972.826 ms
DROP INDEX ix_lotsofitext_zz2ijw;
DROP INDEX
Time: 1429.257 ms
CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w);
CREATE INDEX
Time: 32046.205 ms
DROP INDEX ix_lotsofitext_zz2ijw;
DROP INDEX
Time: 1782.396 ms
CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2);
CREATE INDEX
Time: 5750.313 ms
DROP INDEX ix_lotsofitext_zz2;
DROP INDEX
Time: 33.812 ms
CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2);
CREATE INDEX
Time: 5754.009 ms
DROP INDEX ix_lotsofitext_zz2;
DROP INDEX
Time: 34.067 ms
CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2);
CREATE INDEX
Time: 5763.431 ms
DROP INDEX ix_lotsofitext_zz2;
DROP INDEX
Time: 31.628 ms
CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2);
CREATE INDEX
Time: 5840.520 ms
DROP INDEX ix_lotsofitext_zz2;
DROP INDEX
Time: 32.641 ms
Timing is on.
show work_mem;
 work_mem 
----------
 4GB
(1 row)

Time: 0.259 ms
show maintenance_work_mem;
 maintenance_work_mem 
----------------------
 4GB
(1 row)

Time: 0.147 ms
select count(*) FROM (select * from lotsofints order by i) t;
  count   
----------
 10000000
(1 row)

Time: 3746.583 ms
select count(*) FROM (select * from lotsofints order by i) t;
  count   
----------
 10000000
(1 row)

Time: 3691.104 ms
select count(*) FROM (select * from lotsofints order by i) t;
  count   
----------
 10000000
(1 row)

Time: 3647.575 ms
select count(*) FROM (select * from lotsofints order by i) t;
  count   
----------
 10000000
(1 row)

Time: 3617.354 ms
select count(*) FROM (select * from lotsoftext order by i) t;
  count   
----------
 10000000
(1 row)

Time: 57971.271 ms
select count(*) FROM (select * from lotsoftext order by i) t;
  count   
----------
 10000000
(1 row)

Time: 57507.092 ms
select count(*) FROM (select * from lotsoftext order by i) t;
  count   
----------
 10000000
(1 row)

Time: 58493.787 ms
select count(*) FROM (select * from lotsoftext order by i) t;
  count   
----------
 10000000
(1 row)

Time: 58300.204 ms
select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 66683.475 ms
select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 65877.024 ms
select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 65430.827 ms
select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 65410.048 ms
select count(*) FROM (select * from lotsofitext order by i, j) t;
  count   
----------
 10000000
(1 row)

Time: 62531.416 ms
select count(*) FROM (select * from lotsofitext order by i, j) t;
  count   
----------
 10000000
(1 row)

Time: 62039.553 ms
select count(*) FROM (select * from lotsofitext order by i, j) t;
  count   
----------
 10000000
(1 row)

Time: 62041.034 ms
select count(*) FROM (select * from lotsofitext order by i, j) t;
  count   
----------
 10000000
(1 row)

Time: 62326.909 ms
select count(*) FROM (select * from lotsofitext order by z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 5607.796 ms
select count(*) FROM (select * from lotsofitext order by z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 5593.637 ms
select count(*) FROM (select * from lotsofitext order by z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 5554.769 ms
select count(*) FROM (select * from lotsofitext order by z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 5574.554 ms
select count(*) FROM (select * from lotsofitext order by i, z) t;
  count   
----------
 10000000
(1 row)

Time: 60051.943 ms
select count(*) FROM (select * from lotsofitext order by i, z) t;
  count   
----------
 10000000
(1 row)

Time: 60202.738 ms
select count(*) FROM (select * from lotsofitext order by i, z) t;
  count   
----------
 10000000
(1 row)

Time: 59551.232 ms
select count(*) FROM (select * from lotsofitext order by i, z) t;
  count   
----------
 10000000
(1 row)

Time: 59528.217 ms
select count(*) FROM lotsofints;
  count   
----------
 10000000
(1 row)

Time: 250.242 ms
CREATE INDEX ix_lotsofints ON lotsofints (i);
CREATE INDEX
Time: 4916.211 ms
DROP INDEX ix_lotsofints;
DROP INDEX
Time: 23.188 ms
CREATE INDEX ix_lotsofints ON lotsofints (i);
CREATE INDEX
Time: 4919.690 ms
DROP INDEX ix_lotsofints;
DROP INDEX
Time: 23.596 ms
CREATE INDEX ix_lotsofints ON lotsofints (i);
CREATE INDEX
Time: 4953.093 ms
DROP INDEX ix_lotsofints;
DROP INDEX
Time: 22.875 ms
CREATE INDEX ix_lotsofints ON lotsofints (i);
CREATE INDEX
Time: 4934.998 ms
DROP INDEX ix_lotsofints;
DROP INDEX
Time: 23.109 ms
select count(*) FROM lotsoftext;
  count   
----------
 10000000
(1 row)

Time: 283.439 ms
CREATE INDEX ix_lotsoftext ON lotsoftext (i);
CREATE INDEX
Time: 59834.684 ms
DROP INDEX ix_lotsoftext;
DROP INDEX
Time: 58.511 ms
CREATE INDEX ix_lotsoftext ON lotsoftext (i);
CREATE INDEX
Time: 59536.328 ms
DROP INDEX ix_lotsoftext;
DROP INDEX
Time: 60.796 ms
CREATE INDEX ix_lotsoftext ON lotsoftext (i);
CREATE INDEX
Time: 59405.331 ms
DROP INDEX ix_lotsoftext;
DROP INDEX
Time: 60.110 ms
CREATE INDEX ix_lotsoftext ON lotsoftext (i);
CREATE INDEX
Time: 59281.638 ms
DROP INDEX ix_lotsoftext;
DROP INDEX
Time: 60.149 ms
select count(*) FROM lotsofitext;
  count   
----------
 10000000
(1 row)

Time: 476.852 ms
CREATE INDEX ix_lotsofitext ON lotsofitext (i);
CREATE INDEX
Time: 59674.111 ms
DROP INDEX ix_lotsofitext;
DROP INDEX
Time: 59.068 ms
CREATE INDEX ix_lotsofitext ON lotsofitext (i);
CREATE INDEX
Time: 60072.982 ms
DROP INDEX ix_lotsofitext;
DROP INDEX
Time: 59.719 ms
CREATE INDEX ix_lotsofitext ON lotsofitext (i);
CREATE INDEX
Time: 60207.984 ms
DROP INDEX ix_lotsofitext;
DROP INDEX
Time: 61.767 ms
CREATE INDEX ix_lotsofitext ON lotsofitext (i);
CREATE INDEX
Time: 60369.762 ms
DROP INDEX ix_lotsofitext;
DROP INDEX
Time: 59.171 ms
CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2);
CREATE INDEX
Time: 66896.460 ms
DROP INDEX ix_lotsofitext_ijwzz2;
DROP INDEX
Time: 1803.917 ms
CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2);
CREATE INDEX
Time: 66376.962 ms
DROP INDEX ix_lotsofitext_ijwzz2;
DROP INDEX
Time: 1204.945 ms
CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2);
CREATE INDEX
Time: 67402.141 ms
DROP INDEX ix_lotsofitext_ijwzz2;
DROP INDEX
Time: 1898.129 ms
CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2);
CREATE INDEX
Time: 65998.864 ms
DROP INDEX ix_lotsofitext_ijwzz2;
DROP INDEX
Time: 1652.295 ms
CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w);
CREATE INDEX
Time: 14584.528 ms
DROP INDEX ix_lotsofitext_zz2ijw;
DROP INDEX
Time: 1331.099 ms
CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w);
CREATE INDEX
Time: 14452.833 ms
DROP INDEX ix_lotsofitext_zz2ijw;
DROP INDEX
Time: 1465.980 ms
CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w);
CREATE INDEX
Time: 13789.807 ms
DROP INDEX ix_lotsofitext_zz2ijw;
DROP INDEX
Time: 1099.254 ms
CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w);
CREATE INDEX
Time: 14044.249 ms
DROP INDEX ix_lotsofitext_zz2ijw;
DROP INDEX
Time: 1100.874 ms
CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2);
CREATE INDEX
Time: 5739.802 ms
DROP INDEX ix_lotsofitext_zz2;
DROP INDEX
Time: 31.502 ms
CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2);
CREATE INDEX
Time: 5754.301 ms
DROP INDEX ix_lotsofitext_zz2;
DROP INDEX
Time: 32.180 ms
CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2);
CREATE INDEX
Time: 5747.349 ms
DROP INDEX ix_lotsofitext_zz2;
DROP INDEX
Time: 33.351 ms
CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2);
CREATE INDEX
Time: 5745.416 ms
DROP INDEX ix_lotsofitext_zz2;
DROP INDEX
Time: 32.589 ms



--------------- slowcomp -------------

drop table if exists lotsofints;
DROP TABLE
drop table if exists lotsoftext;
DROP TABLE
drop table if exists lotsofitext;
DROP TABLE
create table lotsofints(i integer);
CREATE TABLE
create table lotsoftext(i text);
CREATE TABLE
create table lotsofitext(i text, j text, w text, z integer, z2 bigint);
CREATE TABLE
insert into lotsofints select random() * 1000000000.0 from generate_series(1, 10000000);
INSERT 0 10000000
insert into lotsoftext select 'blablablablawiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii' || cast(random() * 1000000000.0 as text) || 'blablablawiiiiblabla' from generate_series(1, 10000000);
INSERT 0 10000000
insert into lotsofitext select 'blablablablawiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii' || cast(random() * 1000000000.0 as text) || 'blablablawiiiiblabla', 'blablablablawiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii' || cast(random() * 1000000000.0 as text) || 'blablablawjjjblabla', 'blablablablawiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii' || cast(random() * 1000000000.0 as text) || 'blablablawwwabla', random() * 1000000000.0, random() * 1000000000000.0 from generate_series(1, 10000000);
INSERT 0 10000000
vacuum freeze;
VACUUM
select relname, pg_size_pretty(pg_total_relation_size(relname::regclass)) from pg_class where relname in ('lotsofints', 'lotsoftext', 'lotsofitext');
   relname   | pg_size_pretty 
-------------+----------------
 lotsofints  | 346 MB
 lotsoftext  | 1116 MB
 lotsofitext | 2791 MB
(3 rows)

Timing is on.
show work_mem;
 work_mem 
----------
 4MB
(1 row)

Time: 0.167 ms
show maintenance_work_mem;
 maintenance_work_mem 
----------------------
 4MB
(1 row)

Time: 0.093 ms
select count(*) FROM (select * from lotsofints order by i) t;
  count   
----------
 10000000
(1 row)

Time: 8179.727 ms
select count(*) FROM (select * from lotsofints order by i) t;
  count   
----------
 10000000
(1 row)

Time: 7310.838 ms
select count(*) FROM (select * from lotsofints order by i) t;
  count   
----------
 10000000
(1 row)

Time: 7231.359 ms
select count(*) FROM (select * from lotsofints order by i) t;
  count   
----------
 10000000
(1 row)

Time: 7263.329 ms
select count(*) FROM (select * from lotsoftext order by i) t;
  count   
----------
 10000000
(1 row)

Time: 588431.960 ms
select count(*) FROM (select * from lotsoftext order by i) t;
  count   
----------
 10000000
(1 row)

Time: 602037.203 ms
select count(*) FROM (select * from lotsoftext order by i) t;
  count   
----------
 10000000
(1 row)

Time: 596758.144 ms
select count(*) FROM (select * from lotsoftext order by i) t;
  count   
----------
 10000000
(1 row)

Time: 594627.541 ms
select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 657807.717 ms
select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 698636.256 ms
select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 696403.304 ms
select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 694699.122 ms
select count(*) FROM (select * from lotsofitext order by i, j) t;
  count   
----------
 10000000
(1 row)

Time: 629934.984 ms
select count(*) FROM (select * from lotsofitext order by i, j) t;
  count   
----------
 10000000
(1 row)

Time: 635128.744 ms
select count(*) FROM (select * from lotsofitext order by i, j) t;
  count   
----------
 10000000
(1 row)

Time: 645163.812 ms
select count(*) FROM (select * from lotsofitext order by i, j) t;
  count   
----------
 10000000
(1 row)

Time: 645939.444 ms
select count(*) FROM (select * from lotsofitext order by z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 11494.266 ms
select count(*) FROM (select * from lotsofitext order by z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 12691.888 ms
select count(*) FROM (select * from lotsofitext order by z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 11400.494 ms
select count(*) FROM (select * from lotsofitext order by z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 10215.874 ms
select count(*) FROM (select * from lotsofitext order by i, z) t;
  count   
----------
 10000000
(1 row)

Time: 609940.224 ms
select count(*) FROM (select * from lotsofitext order by i, z) t;
  count   
----------
 10000000
(1 row)

Time: 613977.760 ms
select count(*) FROM (select * from lotsofitext order by i, z) t;
  count   
----------
 10000000
(1 row)

Time: 600881.448 ms
select count(*) FROM (select * from lotsofitext order by i, z) t;
  count   
----------
 10000000
(1 row)

Time: 603271.421 ms
select count(*) FROM lotsofints;
  count   
----------
 10000000
(1 row)

Time: 260.249 ms
CREATE INDEX ix_lotsofints ON lotsofints (i);
CREATE INDEX
Time: 8903.969 ms
DROP INDEX ix_lotsofints;
DROP INDEX
Time: 24.848 ms
CREATE INDEX ix_lotsofints ON lotsofints (i);
CREATE INDEX
Time: 9226.380 ms
DROP INDEX ix_lotsofints;
DROP INDEX
Time: 24.827 ms
CREATE INDEX ix_lotsofints ON lotsofints (i);
CREATE INDEX
Time: 9071.135 ms
DROP INDEX ix_lotsofints;
DROP INDEX
Time: 24.273 ms
CREATE INDEX ix_lotsofints ON lotsofints (i);
CREATE INDEX
Time: 8757.258 ms
DROP INDEX ix_lotsofints;
DROP INDEX
Time: 25.070 ms
select count(*) FROM lotsoftext;
  count   
----------
 10000000
(1 row)

Time: 333.406 ms
CREATE INDEX ix_lotsoftext ON lotsoftext (i);
CREATE INDEX
Time: 606380.272 ms
DROP INDEX ix_lotsoftext;
DROP INDEX
Time: 99.338 ms
CREATE INDEX ix_lotsoftext ON lotsoftext (i);
CREATE INDEX
Time: 608427.522 ms
DROP INDEX ix_lotsoftext;
DROP INDEX
Time: 95.864 ms
CREATE INDEX ix_lotsoftext ON lotsoftext (i);
CREATE INDEX
Time: 607359.630 ms
DROP INDEX ix_lotsoftext;
DROP INDEX
Time: 94.870 ms
CREATE INDEX ix_lotsoftext ON lotsoftext (i);
CREATE INDEX
Time: 610969.514 ms
DROP INDEX ix_lotsoftext;
DROP INDEX
Time: 98.139 ms
select count(*) FROM lotsofitext;
  count   
----------
 10000000
(1 row)

Time: 502.616 ms
CREATE INDEX ix_lotsofitext ON lotsofitext (i);
CREATE INDEX
Time: 606686.967 ms
DROP INDEX ix_lotsofitext;
DROP INDEX
Time: 95.667 ms
CREATE INDEX ix_lotsofitext ON lotsofitext (i);
CREATE INDEX
Time: 634917.225 ms
DROP INDEX ix_lotsofitext;
DROP INDEX
Time: 100.844 ms
CREATE INDEX ix_lotsofitext ON lotsofitext (i);
CREATE INDEX
Time: 615230.351 ms
DROP INDEX ix_lotsofitext;
DROP INDEX
Time: 122.822 ms
CREATE INDEX ix_lotsofitext ON lotsofitext (i);
CREATE INDEX
Time: 649157.339 ms
DROP INDEX ix_lotsofitext;
DROP INDEX
Time: 109.121 ms
CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2);
CREATE INDEX
Time: 727170.733 ms
DROP INDEX ix_lotsofitext_ijwzz2;
DROP INDEX
Time: 1728.723 ms
CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2);
CREATE INDEX
Time: 778431.894 ms
DROP INDEX ix_lotsofitext_ijwzz2;
DROP INDEX
Time: 1240.473 ms
CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2);
CREATE INDEX
Time: 760502.630 ms
DROP INDEX ix_lotsofitext_ijwzz2;
DROP INDEX
Time: 1866.304 ms
CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2);
CREATE INDEX
Time: 760403.434 ms
DROP INDEX ix_lotsofitext_ijwzz2;
DROP INDEX
Time: 1575.543 ms
CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w);
CREATE INDEX
Time: 205438.842 ms
DROP INDEX ix_lotsofitext_zz2ijw;
DROP INDEX
Time: 2836.080 ms
CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w);
CREATE INDEX
Time: 195651.539 ms
DROP INDEX ix_lotsofitext_zz2ijw;
DROP INDEX
Time: 3692.697 ms
CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w);
CREATE INDEX
Time: 196261.882 ms
DROP INDEX ix_lotsofitext_zz2ijw;
DROP INDEX
Time: 2658.865 ms
CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w);
CREATE INDEX
Time: 208435.360 ms
DROP INDEX ix_lotsofitext_zz2ijw;
DROP INDEX
Time: 2077.348 ms
CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2);
CREATE INDEX
Time: 10817.425 ms
DROP INDEX ix_lotsofitext_zz2;
DROP INDEX
Time: 33.183 ms
CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2);
CREATE INDEX
Time: 10884.055 ms
DROP INDEX ix_lotsofitext_zz2;
DROP INDEX
Time: 33.320 ms
CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2);
CREATE INDEX
Time: 11172.756 ms
DROP INDEX ix_lotsofitext_zz2;
DROP INDEX
Time: 33.102 ms
CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2);
CREATE INDEX
Time: 10753.717 ms
DROP INDEX ix_lotsofitext_zz2;
DROP INDEX
Time: 32.085 ms
Timing is on.
show work_mem;
 work_mem 
----------
 64MB
(1 row)

Time: 53.895 ms
show maintenance_work_mem;
 maintenance_work_mem 
----------------------
 64MB
(1 row)

Time: 0.158 ms
select count(*) FROM (select * from lotsofints order by i) t;
  count   
----------
 10000000
(1 row)

Time: 10222.928 ms
select count(*) FROM (select * from lotsofints order by i) t;
  count   
----------
 10000000
(1 row)

Time: 6585.456 ms
select count(*) FROM (select * from lotsofints order by i) t;
  count   
----------
 10000000
(1 row)

Time: 6484.574 ms
select count(*) FROM (select * from lotsofints order by i) t;
  count   
----------
 10000000
(1 row)

Time: 6429.910 ms
select count(*) FROM (select * from lotsoftext order by i) t;
  count   
----------
 10000000
(1 row)

Time: 486557.633 ms
select count(*) FROM (select * from lotsoftext order by i) t;
  count   
----------
 10000000
(1 row)

Time: 482311.844 ms
select count(*) FROM (select * from lotsoftext order by i) t;
  count   
----------
 10000000
(1 row)

Time: 484613.084 ms
select count(*) FROM (select * from lotsoftext order by i) t;
  count   
----------
 10000000
(1 row)

Time: 478727.331 ms
select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 508150.983 ms
select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 485299.027 ms
select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 503563.432 ms
select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 486561.560 ms
select count(*) FROM (select * from lotsofitext order by i, j) t;
  count   
----------
 10000000
(1 row)

Time: 467515.517 ms
select count(*) FROM (select * from lotsofitext order by i, j) t;
  count   
----------
 10000000
(1 row)

Time: 469314.326 ms
select count(*) FROM (select * from lotsofitext order by i, j) t;
  count   
----------
 10000000
(1 row)

Time: 469282.486 ms
select count(*) FROM (select * from lotsofitext order by i, j) t;
  count   
----------
 10000000
(1 row)

Time: 465916.822 ms
select count(*) FROM (select * from lotsofitext order by z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 8767.130 ms
select count(*) FROM (select * from lotsofitext order by z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 8577.093 ms
select count(*) FROM (select * from lotsofitext order by z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 8682.204 ms
select count(*) FROM (select * from lotsofitext order by z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 8520.205 ms
select count(*) FROM (select * from lotsofitext order by i, z) t;
  count   
----------
 10000000
(1 row)

Time: 449236.798 ms
select count(*) FROM (select * from lotsofitext order by i, z) t;
  count   
----------
 10000000
(1 row)

Time: 444967.466 ms
select count(*) FROM (select * from lotsofitext order by i, z) t;
  count   
----------
 10000000
(1 row)

Time: 464610.598 ms
select count(*) FROM (select * from lotsofitext order by i, z) t;
  count   
----------
 10000000
(1 row)

Time: 474921.871 ms
select count(*) FROM lotsofints;
  count   
----------
 10000000
(1 row)

Time: 387.258 ms
CREATE INDEX ix_lotsofints ON lotsofints (i);
CREATE INDEX
Time: 7587.100 ms
DROP INDEX ix_lotsofints;
DROP INDEX
Time: 91.222 ms
CREATE INDEX ix_lotsofints ON lotsofints (i);
CREATE INDEX
Time: 8235.522 ms
DROP INDEX ix_lotsofints;
DROP INDEX
Time: 25.000 ms
CREATE INDEX ix_lotsofints ON lotsofints (i);
CREATE INDEX
Time: 7379.890 ms
DROP INDEX ix_lotsofints;
DROP INDEX
Time: 24.742 ms
CREATE INDEX ix_lotsofints ON lotsofints (i);
CREATE INDEX
Time: 7339.589 ms
DROP INDEX ix_lotsofints;
DROP INDEX
Time: 26.765 ms
select count(*) FROM lotsoftext;
  count   
----------
 10000000
(1 row)

Time: 375.271 ms
CREATE INDEX ix_lotsoftext ON lotsoftext (i);
CREATE INDEX
Time: 477774.434 ms
DROP INDEX ix_lotsoftext;
DROP INDEX
Time: 96.915 ms
CREATE INDEX ix_lotsoftext ON lotsoftext (i);
CREATE INDEX
Time: 453100.301 ms
DROP INDEX ix_lotsoftext;
DROP INDEX
Time: 100.388 ms
CREATE INDEX ix_lotsoftext ON lotsoftext (i);
CREATE INDEX
Time: 445330.413 ms
DROP INDEX ix_lotsoftext;
DROP INDEX
Time: 97.716 ms
CREATE INDEX ix_lotsoftext ON lotsoftext (i);
CREATE INDEX
Time: 444586.904 ms
DROP INDEX ix_lotsoftext;
DROP INDEX
Time: 99.147 ms
select count(*) FROM lotsofitext;
  count   
----------
 10000000
(1 row)

Time: 600.014 ms
CREATE INDEX ix_lotsofitext ON lotsofitext (i);
CREATE INDEX
Time: 449801.053 ms
DROP INDEX ix_lotsofitext;
DROP INDEX
Time: 100.014 ms
CREATE INDEX ix_lotsofitext ON lotsofitext (i);
CREATE INDEX
Time: 456478.346 ms
DROP INDEX ix_lotsofitext;
DROP INDEX
Time: 106.706 ms
CREATE INDEX ix_lotsofitext ON lotsofitext (i);
CREATE INDEX
Time: 468985.027 ms
DROP INDEX ix_lotsofitext;
DROP INDEX
Time: 104.715 ms
CREATE INDEX ix_lotsofitext ON lotsofitext (i);
CREATE INDEX
Time: 453759.097 ms
DROP INDEX ix_lotsofitext;
DROP INDEX
Time: 100.659 ms
CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2);
CREATE INDEX
Time: 513175.597 ms
DROP INDEX ix_lotsofitext_ijwzz2;
DROP INDEX
Time: 201.914 ms
CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2);
CREATE INDEX
Time: 502138.900 ms
DROP INDEX ix_lotsofitext_ijwzz2;
DROP INDEX
Time: 248.277 ms
CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2);
CREATE INDEX
Time: 471150.897 ms
DROP INDEX ix_lotsofitext_ijwzz2;
DROP INDEX
Time: 249.473 ms
CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2);
CREATE INDEX
Time: 472289.728 ms
DROP INDEX ix_lotsofitext_ijwzz2;
DROP INDEX
Time: 247.743 ms
CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w);
CREATE INDEX
Time: 64409.144 ms
DROP INDEX ix_lotsofitext_zz2ijw;
DROP INDEX
Time: 1992.907 ms
CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w);
CREATE INDEX
Time: 63759.192 ms
DROP INDEX ix_lotsofitext_zz2ijw;
DROP INDEX
Time: 1927.159 ms
CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w);
CREATE INDEX
Time: 63766.884 ms
DROP INDEX ix_lotsofitext_zz2ijw;
DROP INDEX
Time: 1792.878 ms
CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w);
CREATE INDEX
Time: 63749.537 ms
DROP INDEX ix_lotsofitext_zz2ijw;
DROP INDEX
Time: 2088.264 ms
CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2);
CREATE INDEX
Time: 8017.687 ms
DROP INDEX ix_lotsofitext_zz2;
DROP INDEX
Time: 31.791 ms
CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2);
CREATE INDEX
Time: 8006.932 ms
DROP INDEX ix_lotsofitext_zz2;
DROP INDEX
Time: 33.264 ms
CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2);
CREATE INDEX
Time: 8059.861 ms
DROP INDEX ix_lotsofitext_zz2;
DROP INDEX
Time: 32.988 ms
CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2);
CREATE INDEX
Time: 8152.659 ms
DROP INDEX ix_lotsofitext_zz2;
DROP INDEX
Time: 31.378 ms
Timing is on.
show work_mem;
 work_mem 
----------
 256MB
(1 row)

Time: 0.244 ms
show maintenance_work_mem;
 maintenance_work_mem 
----------------------
 256MB
(1 row)

Time: 0.152 ms
select count(*) FROM (select * from lotsofints order by i) t;
  count   
----------
 10000000
(1 row)

Time: 8751.968 ms
select count(*) FROM (select * from lotsofints order by i) t;
  count   
----------
 10000000
(1 row)

Time: 4806.475 ms
select count(*) FROM (select * from lotsofints order by i) t;
  count   
----------
 10000000
(1 row)

Time: 4826.267 ms
select count(*) FROM (select * from lotsofints order by i) t;
  count   
----------
 10000000
(1 row)

Time: 4772.199 ms
select count(*) FROM (select * from lotsoftext order by i) t;
  count   
----------
 10000000
(1 row)

Time: 402847.072 ms
select count(*) FROM (select * from lotsoftext order by i) t;
  count   
----------
 10000000
(1 row)

Time: 405171.897 ms
select count(*) FROM (select * from lotsoftext order by i) t;
  count   
----------
 10000000
(1 row)

Time: 406056.366 ms
select count(*) FROM (select * from lotsoftext order by i) t;
  count   
----------
 10000000
(1 row)

Time: 408985.343 ms
select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 434070.233 ms
select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 435056.160 ms
select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 435035.402 ms
select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 437236.753 ms
select count(*) FROM (select * from lotsofitext order by i, j) t;
  count   
----------
 10000000
(1 row)

Time: 427488.053 ms
select count(*) FROM (select * from lotsofitext order by i, j) t;
  count   
----------
 10000000
(1 row)

Time: 424454.282 ms
select count(*) FROM (select * from lotsofitext order by i, j) t;
  count   
----------
 10000000
(1 row)

Time: 425145.982 ms
select count(*) FROM (select * from lotsofitext order by i, j) t;
  count   
----------
 10000000
(1 row)

Time: 424071.089 ms
select count(*) FROM (select * from lotsofitext order by z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 7322.956 ms
select count(*) FROM (select * from lotsofitext order by z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 7159.616 ms
select count(*) FROM (select * from lotsofitext order by z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 7157.538 ms
select count(*) FROM (select * from lotsofitext order by z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 7153.679 ms
select count(*) FROM (select * from lotsofitext order by i, z) t;
  count   
----------
 10000000
(1 row)

Time: 404776.429 ms
select count(*) FROM (select * from lotsofitext order by i, z) t;
  count   
----------
 10000000
(1 row)

Time: 404657.093 ms
select count(*) FROM (select * from lotsofitext order by i, z) t;
  count   
----------
 10000000
(1 row)

Time: 403627.212 ms
select count(*) FROM (select * from lotsofitext order by i, z) t;
  count   
----------
 10000000
(1 row)

Time: 404990.630 ms
select count(*) FROM lotsofints;
  count   
----------
 10000000
(1 row)

Time: 752.995 ms
CREATE INDEX ix_lotsofints ON lotsofints (i);
CREATE INDEX
Time: 6008.570 ms
DROP INDEX ix_lotsofints;
DROP INDEX
Time: 47.413 ms
CREATE INDEX ix_lotsofints ON lotsofints (i);
CREATE INDEX
Time: 5826.746 ms
DROP INDEX ix_lotsofints;
DROP INDEX
Time: 22.644 ms
CREATE INDEX ix_lotsofints ON lotsofints (i);
CREATE INDEX
Time: 5852.789 ms
DROP INDEX ix_lotsofints;
DROP INDEX
Time: 23.903 ms
CREATE INDEX ix_lotsofints ON lotsofints (i);
CREATE INDEX
Time: 5855.710 ms
DROP INDEX ix_lotsofints;
DROP INDEX
Time: 22.628 ms
select count(*) FROM lotsoftext;
  count   
----------
 10000000
(1 row)

Time: 329.138 ms
CREATE INDEX ix_lotsoftext ON lotsoftext (i);
CREATE INDEX
Time: 411766.016 ms
DROP INDEX ix_lotsoftext;
DROP INDEX
Time: 97.938 ms
CREATE INDEX ix_lotsoftext ON lotsoftext (i);
CREATE INDEX
Time: 409816.275 ms
DROP INDEX ix_lotsoftext;
DROP INDEX
Time: 389.201 ms
CREATE INDEX ix_lotsoftext ON lotsoftext (i);
CREATE INDEX
Time: 406256.278 ms
DROP INDEX ix_lotsoftext;
DROP INDEX
Time: 826.664 ms
CREATE INDEX ix_lotsoftext ON lotsoftext (i);
CREATE INDEX
Time: 407313.384 ms
DROP INDEX ix_lotsoftext;
DROP INDEX
Time: 257.675 ms
select count(*) FROM lotsofitext;
  count   
----------
 10000000
(1 row)

Time: 611.884 ms
CREATE INDEX ix_lotsofitext ON lotsofitext (i);
CREATE INDEX
Time: 410456.796 ms
DROP INDEX ix_lotsofitext;
DROP INDEX
Time: 133.747 ms
CREATE INDEX ix_lotsofitext ON lotsofitext (i);
CREATE INDEX
Time: 416067.075 ms
DROP INDEX ix_lotsofitext;
DROP INDEX
Time: 96.601 ms
CREATE INDEX ix_lotsofitext ON lotsofitext (i);
CREATE INDEX
Time: 409800.300 ms
DROP INDEX ix_lotsofitext;
DROP INDEX
Time: 94.719 ms
CREATE INDEX ix_lotsofitext ON lotsofitext (i);
CREATE INDEX
Time: 413309.180 ms
DROP INDEX ix_lotsofitext;
DROP INDEX
Time: 94.371 ms
CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2);
CREATE INDEX
Time: 450771.264 ms
DROP INDEX ix_lotsofitext_ijwzz2;
DROP INDEX
Time: 754.625 ms
CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2);
CREATE INDEX
Time: 445105.168 ms
DROP INDEX ix_lotsofitext_ijwzz2;
DROP INDEX
Time: 247.152 ms
CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2);
CREATE INDEX
Time: 445474.275 ms
DROP INDEX ix_lotsofitext_ijwzz2;
DROP INDEX
Time: 1899.777 ms
CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2);
CREATE INDEX
Time: 445565.300 ms
DROP INDEX ix_lotsofitext_ijwzz2;
DROP INDEX
Time: 249.933 ms
CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w);
CREATE INDEX
Time: 64614.576 ms
DROP INDEX ix_lotsofitext_zz2ijw;
DROP INDEX
Time: 2094.182 ms
CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w);
CREATE INDEX
Time: 63673.873 ms
DROP INDEX ix_lotsofitext_zz2ijw;
DROP INDEX
Time: 1724.110 ms
CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w);
CREATE INDEX
Time: 66023.283 ms
DROP INDEX ix_lotsofitext_zz2ijw;
DROP INDEX
Time: 2617.325 ms
CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w);
CREATE INDEX
Time: 64042.958 ms
DROP INDEX ix_lotsofitext_zz2ijw;
DROP INDEX
Time: 2242.008 ms
CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2);
CREATE INDEX
Time: 7222.440 ms
DROP INDEX ix_lotsofitext_zz2;
DROP INDEX
Time: 32.539 ms
CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2);
CREATE INDEX
Time: 7192.768 ms
DROP INDEX ix_lotsofitext_zz2;
DROP INDEX
Time: 33.728 ms
CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2);
CREATE INDEX
Time: 7176.251 ms
DROP INDEX ix_lotsofitext_zz2;
DROP INDEX
Time: 31.801 ms
CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2);
CREATE INDEX
Time: 7149.232 ms
DROP INDEX ix_lotsofitext_zz2;
DROP INDEX
Time: 33.654 ms
Timing is on.
show work_mem;
 work_mem 
----------
 1GB
(1 row)

Time: 0.265 ms
show maintenance_work_mem;
 maintenance_work_mem 
----------------------
 1GB
(1 row)

Time: 0.157 ms
select count(*) FROM (select * from lotsofints order by i) t;
  count   
----------
 10000000
(1 row)

Time: 7282.254 ms
select count(*) FROM (select * from lotsofints order by i) t;
  count   
----------
 10000000
(1 row)

Time: 3620.481 ms
select count(*) FROM (select * from lotsofints order by i) t;
  count   
----------
 10000000
(1 row)

Time: 3577.785 ms
select count(*) FROM (select * from lotsofints order by i) t;
  count   
----------
 10000000
(1 row)

Time: 3607.773 ms
select count(*) FROM (select * from lotsoftext order by i) t;
  count   
----------
 10000000
(1 row)

Time: 390548.928 ms
select count(*) FROM (select * from lotsoftext order by i) t;
  count   
----------
 10000000
(1 row)

Time: 380144.410 ms
select count(*) FROM (select * from lotsoftext order by i) t;
  count   
----------
 10000000
(1 row)

Time: 380826.507 ms
select count(*) FROM (select * from lotsoftext order by i) t;
  count   
----------
 10000000
(1 row)

Time: 378739.273 ms
select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 406718.184 ms
select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 406443.720 ms
select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 406207.427 ms
select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 406837.786 ms
select count(*) FROM (select * from lotsofitext order by i, j) t;
  count   
----------
 10000000
(1 row)

Time: 385870.562 ms
select count(*) FROM (select * from lotsofitext order by i, j) t;
  count   
----------
 10000000
(1 row)

Time: 387495.265 ms
select count(*) FROM (select * from lotsofitext order by i, j) t;
  count   
----------
 10000000
(1 row)

Time: 386602.777 ms
select count(*) FROM (select * from lotsofitext order by i, j) t;
  count   
----------
 10000000
(1 row)

Time: 387292.101 ms
select count(*) FROM (select * from lotsofitext order by z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 5922.239 ms
select count(*) FROM (select * from lotsofitext order by z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 5886.576 ms
select count(*) FROM (select * from lotsofitext order by z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 5836.505 ms
select count(*) FROM (select * from lotsofitext order by z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 5866.659 ms
select count(*) FROM (select * from lotsofitext order by i, z) t;
  count   
----------
 10000000
(1 row)

Time: 379826.162 ms
select count(*) FROM (select * from lotsofitext order by i, z) t;
  count   
----------
 10000000
(1 row)

Time: 378908.266 ms
select count(*) FROM (select * from lotsofitext order by i, z) t;
  count   
----------
 10000000
(1 row)

Time: 377784.183 ms
select count(*) FROM (select * from lotsofitext order by i, z) t;
  count   
----------
 10000000
(1 row)

Time: 378125.974 ms
select count(*) FROM lotsofints;
  count   
----------
 10000000
(1 row)

Time: 368.545 ms
CREATE INDEX ix_lotsofints ON lotsofints (i);
CREATE INDEX
Time: 5093.162 ms
DROP INDEX ix_lotsofints;
DROP INDEX
Time: 70.311 ms
CREATE INDEX ix_lotsofints ON lotsofints (i);
CREATE INDEX
Time: 5035.474 ms
DROP INDEX ix_lotsofints;
DROP INDEX
Time: 24.872 ms
CREATE INDEX ix_lotsofints ON lotsofints (i);
CREATE INDEX
Time: 5036.418 ms
DROP INDEX ix_lotsofints;
DROP INDEX
Time: 24.211 ms
CREATE INDEX ix_lotsofints ON lotsofints (i);
CREATE INDEX
Time: 4993.555 ms
DROP INDEX ix_lotsofints;
DROP INDEX
Time: 23.352 ms
select count(*) FROM lotsoftext;
  count   
----------
 10000000
(1 row)

Time: 330.081 ms
CREATE INDEX ix_lotsoftext ON lotsoftext (i);
CREATE INDEX
Time: 385437.149 ms
DROP INDEX ix_lotsoftext;
DROP INDEX
Time: 1447.833 ms
CREATE INDEX ix_lotsoftext ON lotsoftext (i);
CREATE INDEX
Time: 384266.389 ms
DROP INDEX ix_lotsoftext;
DROP INDEX
Time: 1731.008 ms
CREATE INDEX ix_lotsoftext ON lotsoftext (i);
CREATE INDEX
Time: 382504.851 ms
DROP INDEX ix_lotsoftext;
DROP INDEX
Time: 1377.608 ms
CREATE INDEX ix_lotsoftext ON lotsoftext (i);
CREATE INDEX
Time: 382758.304 ms
DROP INDEX ix_lotsoftext;
DROP INDEX
Time: 1538.357 ms
select count(*) FROM lotsofitext;
  count   
----------
 10000000
(1 row)

Time: 646.474 ms
CREATE INDEX ix_lotsofitext ON lotsofitext (i);
CREATE INDEX
Time: 385459.017 ms
DROP INDEX ix_lotsofitext;
DROP INDEX
Time: 1093.333 ms
CREATE INDEX ix_lotsofitext ON lotsofitext (i);
CREATE INDEX
Time: 385716.657 ms
DROP INDEX ix_lotsofitext;
DROP INDEX
Time: 1589.176 ms
CREATE INDEX ix_lotsofitext ON lotsofitext (i);
CREATE INDEX
Time: 385224.543 ms
DROP INDEX ix_lotsofitext;
DROP INDEX
Time: 1322.800 ms
CREATE INDEX ix_lotsofitext ON lotsofitext (i);
CREATE INDEX
Time: 386048.883 ms
DROP INDEX ix_lotsofitext;
DROP INDEX
Time: 1307.617 ms
CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2);
CREATE INDEX
Time: 424472.540 ms
DROP INDEX ix_lotsofitext_ijwzz2;
DROP INDEX
Time: 200.518 ms
CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2);
CREATE INDEX
Time: 415650.127 ms
DROP INDEX ix_lotsofitext_ijwzz2;
DROP INDEX
Time: 233.313 ms
CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2);
CREATE INDEX
Time: 414192.311 ms
DROP INDEX ix_lotsofitext_ijwzz2;
DROP INDEX
Time: 257.337 ms
CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2);
CREATE INDEX
Time: 412866.907 ms
DROP INDEX ix_lotsofitext_ijwzz2;
DROP INDEX
Time: 251.010 ms
CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w);
CREATE INDEX
Time: 65777.189 ms
DROP INDEX ix_lotsofitext_zz2ijw;
DROP INDEX
Time: 2615.154 ms
CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w);
CREATE INDEX
Time: 65700.794 ms
DROP INDEX ix_lotsofitext_zz2ijw;
DROP INDEX
Time: 2631.710 ms
CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w);
CREATE INDEX
Time: 66903.107 ms
DROP INDEX ix_lotsofitext_zz2ijw;
DROP INDEX
Time: 1598.593 ms
CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w);
CREATE INDEX
Time: 65863.337 ms
DROP INDEX ix_lotsofitext_zz2ijw;
DROP INDEX
Time: 2772.266 ms
CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2);
CREATE INDEX
Time: 6074.502 ms
DROP INDEX ix_lotsofitext_zz2;
DROP INDEX
Time: 34.774 ms
CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2);
CREATE INDEX
Time: 6039.660 ms
DROP INDEX ix_lotsofitext_zz2;
DROP INDEX
Time: 33.867 ms
CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2);
CREATE INDEX
Time: 6039.048 ms
DROP INDEX ix_lotsofitext_zz2;
DROP INDEX
Time: 32.576 ms
CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2);
CREATE INDEX
Time: 5932.548 ms
DROP INDEX ix_lotsofitext_zz2;
DROP INDEX
Time: 31.909 ms
Timing is on.
show work_mem;
 work_mem 
----------
 4GB
(1 row)

Time: 0.259 ms
show maintenance_work_mem;
 maintenance_work_mem 
----------------------
 4GB
(1 row)

Time: 0.133 ms
select count(*) FROM (select * from lotsofints order by i) t;
  count   
----------
 10000000
(1 row)

Time: 7154.671 ms
select count(*) FROM (select * from lotsofints order by i) t;
  count   
----------
 10000000
(1 row)

Time: 3581.755 ms
select count(*) FROM (select * from lotsofints order by i) t;
  count   
----------
 10000000
(1 row)

Time: 3564.180 ms
select count(*) FROM (select * from lotsofints order by i) t;
  count   
----------
 10000000
(1 row)

Time: 3565.711 ms
select count(*) FROM (select * from lotsoftext order by i) t;
  count   
----------
 10000000
(1 row)

Time: 386735.964 ms
select count(*) FROM (select * from lotsoftext order by i) t;
  count   
----------
 10000000
(1 row)

Time: 377147.347 ms
select count(*) FROM (select * from lotsoftext order by i) t;
  count   
----------
 10000000
(1 row)

Time: 377428.596 ms
select count(*) FROM (select * from lotsoftext order by i) t;
  count   
----------
 10000000
(1 row)

Time: 376121.329 ms
select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 406779.931 ms
select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 421277.385 ms
select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 414041.377 ms
select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 405848.571 ms
select count(*) FROM (select * from lotsofitext order by i, j) t;
  count   
----------
 10000000
(1 row)

Time: 382834.855 ms
select count(*) FROM (select * from lotsofitext order by i, j) t;
  count   
----------
 10000000
(1 row)

Time: 383784.431 ms
select count(*) FROM (select * from lotsofitext order by i, j) t;
  count   
----------
 10000000
(1 row)

Time: 382283.215 ms
select count(*) FROM (select * from lotsofitext order by i, j) t;
  count   
----------
 10000000
(1 row)

Time: 382535.140 ms
select count(*) FROM (select * from lotsofitext order by z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 5869.984 ms
select count(*) FROM (select * from lotsofitext order by z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 5867.940 ms
select count(*) FROM (select * from lotsofitext order by z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 5959.330 ms
select count(*) FROM (select * from lotsofitext order by z, z2) t;
  count   
----------
 10000000
(1 row)

Time: 5872.158 ms
select count(*) FROM (select * from lotsofitext order by i, z) t;
  count   
----------
 10000000
(1 row)

Time: 378808.310 ms
select count(*) FROM (select * from lotsofitext order by i, z) t;
  count   
----------
 10000000
(1 row)

Time: 378747.989 ms
select count(*) FROM (select * from lotsofitext order by i, z) t;
  count   
----------
 10000000
(1 row)

Time: 379879.718 ms
select count(*) FROM (select * from lotsofitext order by i, z) t;
  count   
----------
 10000000
(1 row)

Time: 381853.072 ms
select count(*) FROM lotsofints;
  count   
----------
 10000000
(1 row)

Time: 4567.157 ms
CREATE INDEX ix_lotsofints ON lotsofints (i);
CREATE INDEX
Time: 5089.163 ms
DROP INDEX ix_lotsofints;
DROP INDEX
Time: 73.381 ms
CREATE INDEX ix_lotsofints ON lotsofints (i);
CREATE INDEX
Time: 4953.096 ms
DROP INDEX ix_lotsofints;
DROP INDEX
Time: 22.880 ms
CREATE INDEX ix_lotsofints ON lotsofints (i);
CREATE INDEX
Time: 4924.762 ms
DROP INDEX ix_lotsofints;
DROP INDEX
Time: 22.962 ms
CREATE INDEX ix_lotsofints ON lotsofints (i);
CREATE INDEX
Time: 5025.088 ms
DROP INDEX ix_lotsofints;
DROP INDEX
Time: 24.999 ms
select count(*) FROM lotsoftext;
  count   
----------
 10000000
(1 row)

Time: 12232.296 ms
CREATE INDEX ix_lotsoftext ON lotsoftext (i);
CREATE INDEX
Time: 380739.808 ms
DROP INDEX ix_lotsoftext;
DROP INDEX
Time: 1119.093 ms
CREATE INDEX ix_lotsoftext ON lotsoftext (i);
CREATE INDEX
Time: 378443.746 ms
DROP INDEX ix_lotsoftext;
DROP INDEX
Time: 1246.464 ms
CREATE INDEX ix_lotsoftext ON lotsoftext (i);
CREATE INDEX
Time: 375652.769 ms
DROP INDEX ix_lotsoftext;
DROP INDEX
Time: 1039.495 ms
CREATE INDEX ix_lotsoftext ON lotsoftext (i);
CREATE INDEX
Time: 376209.402 ms
DROP INDEX ix_lotsoftext;
DROP INDEX
Time: 1349.518 ms
select count(*) FROM lotsofitext;
  count   
----------
 10000000
(1 row)

Time: 663.797 ms
CREATE INDEX ix_lotsofitext ON lotsofitext (i);
CREATE INDEX
Time: 381448.856 ms
DROP INDEX ix_lotsofitext;
DROP INDEX
Time: 989.580 ms
CREATE INDEX ix_lotsofitext ON lotsofitext (i);
CREATE INDEX
Time: 380041.496 ms
DROP INDEX ix_lotsofitext;
DROP INDEX
Time: 1362.910 ms
CREATE INDEX ix_lotsofitext ON lotsofitext (i);
CREATE INDEX
Time: 380935.048 ms
DROP INDEX ix_lotsofitext;
DROP INDEX
Time: 1338.140 ms
CREATE INDEX ix_lotsofitext ON lotsofitext (i);
CREATE INDEX
Time: 381078.207 ms
DROP INDEX ix_lotsofitext;
DROP INDEX
Time: 1357.131 ms
CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2);
CREATE INDEX
Time: 456262.837 ms
DROP INDEX ix_lotsofitext_ijwzz2;
DROP INDEX
Time: 213.700 ms
CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2);
CREATE INDEX
Time: 462432.823 ms
DROP INDEX ix_lotsofitext_ijwzz2;
DROP INDEX
Time: 208.126 ms
CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2);
CREATE INDEX
Time: 455237.746 ms
DROP INDEX ix_lotsofitext_ijwzz2;
DROP INDEX
Time: 826.067 ms
CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2);
CREATE INDEX
Time: 460179.616 ms
DROP INDEX ix_lotsofitext_ijwzz2;
DROP INDEX
Time: 224.130 ms
CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w);
CREATE INDEX
Time: 109778.625 ms
DROP INDEX ix_lotsofitext_zz2ijw;
DROP INDEX
Time: 189.958 ms
CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w);
CREATE INDEX
Time: 109112.588 ms
DROP INDEX ix_lotsofitext_zz2ijw;
DROP INDEX
Time: 181.868 ms
CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w);
CREATE INDEX
Time: 106969.483 ms
DROP INDEX ix_lotsofitext_zz2ijw;
DROP INDEX
Time: 176.895 ms
CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w);
CREATE INDEX
Time: 105499.259 ms
DROP INDEX ix_lotsofitext_zz2ijw;
DROP INDEX
Time: 204.527 ms
CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2);
CREATE INDEX
Time: 29946.771 ms
DROP INDEX ix_lotsofitext_zz2;
DROP INDEX
Time: 31.410 ms
CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2);
CREATE INDEX
Time: 6046.815 ms
DROP INDEX ix_lotsofitext_zz2;
DROP INDEX
Time: 30.986 ms
CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2);
CREATE INDEX
Time: 5905.594 ms
DROP INDEX ix_lotsofitext_zz2;
DROP INDEX
Time: 30.832 ms
CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2);
CREATE INDEX
Time: 5911.046 ms
DROP INDEX ix_lotsofitext_zz2;
DROP INDEX
Time: 29.668 ms