--------------- 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 lotsoftext | 651 MB lotsofitext | 1421 MB (3 rows) Timing is on. show work_mem; work_mem ---------- 4MB (1 row) Time: 0.147 ms show maintenance_work_mem; maintenance_work_mem ---------------------- 4MB (1 row) Time: 0.064 ms select count(*) FROM (select * from lotsofints order by i) t; count ---------- 10000000 (1 row) Time: 9263.957 ms select count(*) FROM (select * from lotsofints order by i) t; count ---------- 10000000 (1 row) Time: 7750.877 ms select count(*) FROM (select * from lotsofints order by i) t; count ---------- 10000000 (1 row) Time: 7139.172 ms select count(*) FROM (select * from lotsofints order by i) t; count ---------- 10000000 (1 row) Time: 6522.377 ms select count(*) FROM (select * from lotsoftext order by i) t; count ---------- 10000000 (1 row) Time: 65991.697 ms select count(*) FROM (select * from lotsoftext order by i) t; count ---------- 10000000 (1 row) Time: 66522.278 ms select count(*) FROM (select * from lotsoftext order by i) t; count ---------- 10000000 (1 row) Time: 65991.058 ms select count(*) FROM (select * from lotsoftext order by i) t; count ---------- 10000000 (1 row) Time: 66954.370 ms select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t; count ---------- 10000000 (1 row) Time: 75230.630 ms select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t; count ---------- 10000000 (1 row) Time: 75180.787 ms select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t; count ---------- 10000000 (1 row) Time: 75267.423 ms select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t; count ---------- 10000000 (1 row) Time: 78201.997 ms select count(*) FROM (select * from lotsofitext order by i, j) t; count ---------- 10000000 (1 row) Time: 71029.901 ms select count(*) FROM (select * from lotsofitext order by i, j) t; count ---------- 10000000 (1 row) Time: 71037.219 ms select count(*) FROM (select * from lotsofitext order by i, j) t; count ---------- 10000000 (1 row) Time: 71574.614 ms select count(*) FROM (select * from lotsofitext order by i, j) t; count ---------- 10000000 (1 row) Time: 71315.429 ms select count(*) FROM (select * from lotsofitext order by z, z2) t; count ---------- 10000000 (1 row) Time: 9320.216 ms select count(*) FROM (select * from lotsofitext order by z, z2) t; count ---------- 10000000 (1 row) Time: 9750.454 ms select count(*) FROM (select * from lotsofitext order by z, z2) t; count ---------- 10000000 (1 row) Time: 10265.251 ms select count(*) FROM (select * from lotsofitext order by z, z2) t; count ---------- 10000000 (1 row) Time: 9895.266 ms select count(*) FROM (select * from lotsofitext order by i, z) t; count ---------- 10000000 (1 row) Time: 68996.579 ms select count(*) FROM (select * from lotsofitext order by i, z) t; count ---------- 10000000 (1 row) Time: 68480.416 ms select count(*) FROM (select * from lotsofitext order by i, z) t; count ---------- 10000000 (1 row) Time: 69367.167 ms select count(*) FROM (select * from lotsofitext order by i, z) t; count ---------- 10000000 (1 row) Time: 67761.738 ms select count(*) FROM lotsofints; count ---------- 10000000 (1 row) Time: 260.152 ms CREATE INDEX ix_lotsofints ON lotsofints (i); CREATE INDEX Time: 7824.517 ms DROP INDEX ix_lotsofints; DROP INDEX Time: 21.308 ms CREATE INDEX ix_lotsofints ON lotsofints (i); CREATE INDEX Time: 7747.782 ms DROP INDEX ix_lotsofints; DROP INDEX Time: 22.196 ms CREATE INDEX ix_lotsofints ON lotsofints (i); CREATE INDEX Time: 7694.423 ms DROP INDEX ix_lotsofints; DROP INDEX Time: 22.240 ms CREATE INDEX ix_lotsofints ON lotsofints (i); CREATE INDEX Time: 8390.871 ms DROP INDEX ix_lotsofints; DROP INDEX Time: 20.974 ms select count(*) FROM lotsoftext; count ---------- 10000000 (1 row) Time: 293.374 ms CREATE INDEX ix_lotsoftext ON lotsoftext (i); CREATE INDEX Time: 70558.802 ms DROP INDEX ix_lotsoftext; DROP INDEX Time: 53.856 ms CREATE INDEX ix_lotsoftext ON lotsoftext (i); CREATE INDEX Time: 68399.752 ms DROP INDEX ix_lotsoftext; DROP INDEX Time: 54.007 ms CREATE INDEX ix_lotsoftext ON lotsoftext (i); CREATE INDEX Time: 67876.135 ms DROP INDEX ix_lotsoftext; DROP INDEX Time: 407.160 ms CREATE INDEX ix_lotsoftext ON lotsoftext (i); CREATE INDEX Time: 67212.792 ms DROP INDEX ix_lotsoftext; DROP INDEX Time: 52.912 ms select count(*) FROM lotsofitext; count ---------- 10000000 (1 row) Time: 362.333 ms CREATE INDEX ix_lotsofitext ON lotsofitext (i); CREATE INDEX Time: 67978.220 ms DROP INDEX ix_lotsofitext; DROP INDEX Time: 53.433 ms CREATE INDEX ix_lotsofitext ON lotsofitext (i); CREATE INDEX Time: 68060.334 ms DROP INDEX ix_lotsofitext; DROP INDEX Time: 56.905 ms CREATE INDEX ix_lotsofitext ON lotsofitext (i); CREATE INDEX Time: 71455.577 ms DROP INDEX ix_lotsofitext; DROP INDEX Time: 342.064 ms CREATE INDEX ix_lotsofitext ON lotsofitext (i); CREATE INDEX Time: 69737.511 ms DROP INDEX ix_lotsofitext; DROP INDEX Time: 53.991 ms CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2); CREATE INDEX Time: 78157.250 ms DROP INDEX ix_lotsofitext_ijwzz2; DROP INDEX Time: 1030.873 ms CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2); CREATE INDEX Time: 78483.316 ms DROP INDEX ix_lotsofitext_ijwzz2; DROP INDEX Time: 1381.365 ms CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2); CREATE INDEX Time: 75699.928 ms DROP INDEX ix_lotsofitext_ijwzz2; DROP INDEX Time: 1397.046 ms CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2); CREATE INDEX Time: 74322.749 ms DROP INDEX ix_lotsofitext_ijwzz2; DROP INDEX Time: 1447.581 ms CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w); CREATE INDEX Time: 36941.252 ms DROP INDEX ix_lotsofitext_zz2ijw; DROP INDEX Time: 675.793 ms CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w); CREATE INDEX Time: 35741.325 ms DROP INDEX ix_lotsofitext_zz2ijw; DROP INDEX Time: 1187.049 ms CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w); CREATE INDEX Time: 33274.592 ms DROP INDEX ix_lotsofitext_zz2ijw; DROP INDEX Time: 958.903 ms CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w); CREATE INDEX Time: 37368.521 ms DROP INDEX ix_lotsofitext_zz2ijw; DROP INDEX Time: 653.228 ms CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2); CREATE INDEX Time: 9049.003 ms DROP INDEX ix_lotsofitext_zz2; DROP INDEX Time: 29.904 ms CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2); CREATE INDEX Time: 9156.437 ms DROP INDEX ix_lotsofitext_zz2; DROP INDEX Time: 30.074 ms CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2); CREATE INDEX Time: 9105.328 ms DROP INDEX ix_lotsofitext_zz2; DROP INDEX Time: 32.744 ms CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2); CREATE INDEX Time: 9549.059 ms DROP INDEX ix_lotsofitext_zz2; DROP INDEX Time: 29.488 ms Timing is on. show work_mem; work_mem ---------- 64MB (1 row) Time: 0.466 ms show maintenance_work_mem; maintenance_work_mem ---------------------- 64MB (1 row) Time: 0.123 ms select count(*) FROM (select * from lotsofints order by i) t; count ---------- 10000000 (1 row) Time: 5889.438 ms select count(*) FROM (select * from lotsofints order by i) t; count ---------- 10000000 (1 row) Time: 5851.583 ms select count(*) FROM (select * from lotsofints order by i) t; count ---------- 10000000 (1 row) Time: 5813.059 ms select count(*) FROM (select * from lotsofints order by i) t; count ---------- 10000000 (1 row) Time: 5815.887 ms select count(*) FROM (select * from lotsoftext order by i) t; count ---------- 10000000 (1 row) Time: 59225.218 ms select count(*) FROM (select * from lotsoftext order by i) t; count ---------- 10000000 (1 row) Time: 63651.451 ms select count(*) FROM (select * from lotsoftext order by i) t; count ---------- 10000000 (1 row) Time: 61215.818 ms select count(*) FROM (select * from lotsoftext order by i) t; count ---------- 10000000 (1 row) Time: 60437.850 ms select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t; count ---------- 10000000 (1 row) Time: 67339.792 ms select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t; count ---------- 10000000 (1 row) Time: 66859.731 ms select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t; count ---------- 10000000 (1 row) Time: 65399.157 ms select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t; count ---------- 10000000 (1 row) Time: 66504.793 ms select count(*) FROM (select * from lotsofitext order by i, j) t; count ---------- 10000000 (1 row) Time: 64549.426 ms select count(*) FROM (select * from lotsofitext order by i, j) t; count ---------- 10000000 (1 row) Time: 63151.630 ms select count(*) FROM (select * from lotsofitext order by i, j) t; count ---------- 10000000 (1 row) Time: 64964.901 ms select count(*) FROM (select * from lotsofitext order by i, j) t; count ---------- 10000000 (1 row) Time: 62832.081 ms select count(*) FROM (select * from lotsofitext order by z, z2) t; count ---------- 10000000 (1 row) Time: 7935.624 ms select count(*) FROM (select * from lotsofitext order by z, z2) t; count ---------- 10000000 (1 row) Time: 7988.631 ms select count(*) FROM (select * from lotsofitext order by z, z2) t; count ---------- 10000000 (1 row) Time: 8024.243 ms select count(*) FROM (select * from lotsofitext order by z, z2) t; count ---------- 10000000 (1 row) Time: 8474.211 ms select count(*) FROM (select * from lotsofitext order by i, z) t; count ---------- 10000000 (1 row) Time: 62483.725 ms select count(*) FROM (select * from lotsofitext order by i, z) t; count ---------- 10000000 (1 row) Time: 63680.978 ms select count(*) FROM (select * from lotsofitext order by i, z) t; count ---------- 10000000 (1 row) Time: 62603.196 ms select count(*) FROM (select * from lotsofitext order by i, z) t; count ---------- 10000000 (1 row) Time: 62186.320 ms select count(*) FROM lotsofints; count ---------- 10000000 (1 row) Time: 255.687 ms CREATE INDEX ix_lotsofints ON lotsofints (i); CREATE INDEX Time: 6785.011 ms DROP INDEX ix_lotsofints; DROP INDEX Time: 22.127 ms CREATE INDEX ix_lotsofints ON lotsofints (i); CREATE INDEX Time: 6638.726 ms DROP INDEX ix_lotsofints; DROP INDEX Time: 21.908 ms CREATE INDEX ix_lotsofints ON lotsofints (i); CREATE INDEX Time: 6652.012 ms DROP INDEX ix_lotsofints; DROP INDEX Time: 21.942 ms CREATE INDEX ix_lotsofints ON lotsofints (i); CREATE INDEX Time: 6718.589 ms DROP INDEX ix_lotsofints; DROP INDEX Time: 22.103 ms select count(*) FROM lotsoftext; count ---------- 10000000 (1 row) Time: 282.209 ms CREATE INDEX ix_lotsoftext ON lotsoftext (i); CREATE INDEX Time: 62808.228 ms DROP INDEX ix_lotsoftext; DROP INDEX Time: 54.388 ms CREATE INDEX ix_lotsoftext ON lotsoftext (i); CREATE INDEX Time: 62719.971 ms DROP INDEX ix_lotsoftext; DROP INDEX Time: 54.673 ms CREATE INDEX ix_lotsoftext ON lotsoftext (i); CREATE INDEX Time: 64810.659 ms DROP INDEX ix_lotsoftext; DROP INDEX Time: 65.260 ms CREATE INDEX ix_lotsoftext ON lotsoftext (i); CREATE INDEX Time: 62071.884 ms DROP INDEX ix_lotsoftext; DROP INDEX Time: 53.709 ms select count(*) FROM lotsofitext; count ---------- 10000000 (1 row) Time: 356.601 ms CREATE INDEX ix_lotsofitext ON lotsofitext (i); CREATE INDEX Time: 63020.399 ms DROP INDEX ix_lotsofitext; DROP INDEX Time: 54.779 ms CREATE INDEX ix_lotsofitext ON lotsofitext (i); CREATE INDEX Time: 61545.036 ms DROP INDEX ix_lotsofitext; DROP INDEX Time: 56.359 ms CREATE INDEX ix_lotsofitext ON lotsofitext (i); CREATE INDEX Time: 61810.553 ms DROP INDEX ix_lotsofitext; DROP INDEX Time: 57.740 ms CREATE INDEX ix_lotsofitext ON lotsofitext (i); CREATE INDEX Time: 65276.589 ms DROP INDEX ix_lotsofitext; DROP INDEX Time: 61.014 ms CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2); CREATE INDEX Time: 67271.792 ms DROP INDEX ix_lotsofitext_ijwzz2; DROP INDEX Time: 1241.610 ms CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2); CREATE INDEX Time: 66136.882 ms DROP INDEX ix_lotsofitext_ijwzz2; DROP INDEX Time: 1496.091 ms CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2); CREATE INDEX Time: 68746.680 ms DROP INDEX ix_lotsofitext_ijwzz2; DROP INDEX Time: 1222.790 ms CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2); CREATE INDEX Time: 65612.063 ms DROP INDEX ix_lotsofitext_ijwzz2; DROP INDEX Time: 1413.974 ms CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w); CREATE INDEX Time: 29547.788 ms DROP INDEX ix_lotsofitext_zz2ijw; DROP INDEX Time: 1353.291 ms CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w); CREATE INDEX Time: 26768.734 ms DROP INDEX ix_lotsofitext_zz2ijw; DROP INDEX Time: 1660.773 ms CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w); CREATE INDEX Time: 26391.750 ms DROP INDEX ix_lotsofitext_zz2ijw; DROP INDEX Time: 1424.414 ms CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w); CREATE INDEX Time: 28170.533 ms DROP INDEX ix_lotsofitext_zz2ijw; DROP INDEX Time: 1355.874 ms CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2); CREATE INDEX Time: 7755.324 ms DROP INDEX ix_lotsofitext_zz2; DROP INDEX Time: 29.818 ms CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2); CREATE INDEX Time: 7796.725 ms DROP INDEX ix_lotsofitext_zz2; DROP INDEX Time: 32.084 ms CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2); CREATE INDEX Time: 7846.221 ms DROP INDEX ix_lotsofitext_zz2; DROP INDEX Time: 31.242 ms CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2); CREATE INDEX Time: 7765.321 ms DROP INDEX ix_lotsofitext_zz2; DROP INDEX Time: 29.956 ms Timing is on. show work_mem; work_mem ---------- 256MB (1 row) Time: 0.247 ms show maintenance_work_mem; maintenance_work_mem ---------------------- 256MB (1 row) Time: 0.138 ms select count(*) FROM (select * from lotsofints order by i) t; count ---------- 10000000 (1 row) Time: 4870.979 ms select count(*) FROM (select * from lotsofints order by i) t; count ---------- 10000000 (1 row) Time: 4833.273 ms select count(*) FROM (select * from lotsofints order by i) t; count ---------- 10000000 (1 row) Time: 4826.475 ms select count(*) FROM (select * from lotsofints order by i) t; count ---------- 10000000 (1 row) Time: 4805.589 ms select count(*) FROM (select * from lotsoftext order by i) t; count ---------- 10000000 (1 row) Time: 59130.884 ms select count(*) FROM (select * from lotsoftext order by i) t; count ---------- 10000000 (1 row) Time: 59331.742 ms select count(*) FROM (select * from lotsoftext order by i) t; count ---------- 10000000 (1 row) Time: 58513.627 ms select count(*) FROM (select * from lotsoftext order by i) t; count ---------- 10000000 (1 row) Time: 58619.083 ms select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t; count ---------- 10000000 (1 row) Time: 65374.412 ms select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t; count ---------- 10000000 (1 row) Time: 64394.051 ms select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t; count ---------- 10000000 (1 row) Time: 67471.550 ms select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t; count ---------- 10000000 (1 row) Time: 65906.730 ms select count(*) FROM (select * from lotsofitext order by i, j) t; count ---------- 10000000 (1 row) Time: 65178.446 ms select count(*) FROM (select * from lotsofitext order by i, j) t; count ---------- 10000000 (1 row) Time: 63763.940 ms select count(*) FROM (select * from lotsofitext order by i, j) t; count ---------- 10000000 (1 row) Time: 63856.839 ms select count(*) FROM (select * from lotsofitext order by i, j) t; count ---------- 10000000 (1 row) Time: 67146.585 ms select count(*) FROM (select * from lotsofitext order by z, z2) t; count ---------- 10000000 (1 row) Time: 7204.087 ms select count(*) FROM (select * from lotsofitext order by z, z2) t; count ---------- 10000000 (1 row) Time: 7020.989 ms select count(*) FROM (select * from lotsofitext order by z, z2) t; count ---------- 10000000 (1 row) Time: 7046.034 ms select count(*) FROM (select * from lotsofitext order by z, z2) t; count ---------- 10000000 (1 row) Time: 7010.291 ms select count(*) FROM (select * from lotsofitext order by i, z) t; count ---------- 10000000 (1 row) Time: 62446.520 ms select count(*) FROM (select * from lotsofitext order by i, z) t; count ---------- 10000000 (1 row) Time: 62785.267 ms select count(*) FROM (select * from lotsofitext order by i, z) t; count ---------- 10000000 (1 row) Time: 61903.708 ms select count(*) FROM (select * from lotsofitext order by i, z) t; count ---------- 10000000 (1 row) Time: 61829.476 ms select count(*) FROM lotsofints; count ---------- 10000000 (1 row) Time: 254.949 ms CREATE INDEX ix_lotsofints ON lotsofints (i); CREATE INDEX Time: 6036.751 ms DROP INDEX ix_lotsofints; DROP INDEX Time: 22.665 ms CREATE INDEX ix_lotsofints ON lotsofints (i); CREATE INDEX Time: 6042.316 ms DROP INDEX ix_lotsofints; DROP INDEX Time: 22.154 ms CREATE INDEX ix_lotsofints ON lotsofints (i); CREATE INDEX Time: 6048.902 ms DROP INDEX ix_lotsofints; DROP INDEX Time: 22.944 ms CREATE INDEX ix_lotsofints ON lotsofints (i); CREATE INDEX Time: 6063.490 ms DROP INDEX ix_lotsofints; DROP INDEX Time: 22.235 ms select count(*) FROM lotsoftext; count ---------- 10000000 (1 row) Time: 282.863 ms CREATE INDEX ix_lotsoftext ON lotsoftext (i); CREATE INDEX Time: 62940.196 ms DROP INDEX ix_lotsoftext; DROP INDEX Time: 53.762 ms CREATE INDEX ix_lotsoftext ON lotsoftext (i); CREATE INDEX Time: 62057.928 ms DROP INDEX ix_lotsoftext; DROP INDEX Time: 56.124 ms CREATE INDEX ix_lotsoftext ON lotsoftext (i); CREATE INDEX Time: 63534.627 ms DROP INDEX ix_lotsoftext; DROP INDEX Time: 56.112 ms CREATE INDEX ix_lotsoftext ON lotsoftext (i); CREATE INDEX Time: 62133.240 ms DROP INDEX ix_lotsoftext; DROP INDEX Time: 54.558 ms select count(*) FROM lotsofitext; count ---------- 10000000 (1 row) Time: 361.485 ms CREATE INDEX ix_lotsofitext ON lotsofitext (i); CREATE INDEX Time: 61623.507 ms DROP INDEX ix_lotsofitext; DROP INDEX Time: 59.777 ms CREATE INDEX ix_lotsofitext ON lotsofitext (i); CREATE INDEX Time: 61122.604 ms DROP INDEX ix_lotsofitext; DROP INDEX Time: 54.617 ms CREATE INDEX ix_lotsofitext ON lotsofitext (i); CREATE INDEX Time: 61054.442 ms DROP INDEX ix_lotsofitext; DROP INDEX Time: 53.178 ms CREATE INDEX ix_lotsofitext ON lotsofitext (i); CREATE INDEX Time: 60931.851 ms DROP INDEX ix_lotsofitext; DROP INDEX Time: 56.883 ms CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2); CREATE INDEX Time: 65019.892 ms DROP INDEX ix_lotsofitext_ijwzz2; DROP INDEX Time: 1463.553 ms CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2); CREATE INDEX Time: 71188.208 ms DROP INDEX ix_lotsofitext_ijwzz2; DROP INDEX Time: 971.374 ms CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2); CREATE INDEX Time: 70894.538 ms DROP INDEX ix_lotsofitext_ijwzz2; DROP INDEX Time: 1419.358 ms CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2); CREATE INDEX Time: 68005.203 ms DROP INDEX ix_lotsofitext_ijwzz2; DROP INDEX Time: 1355.396 ms CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w); CREATE INDEX Time: 28566.020 ms DROP INDEX ix_lotsofitext_zz2ijw; DROP INDEX Time: 1504.498 ms CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w); CREATE INDEX Time: 29294.361 ms DROP INDEX ix_lotsofitext_zz2ijw; DROP INDEX Time: 1722.436 ms CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w); CREATE INDEX Time: 28581.555 ms DROP INDEX ix_lotsofitext_zz2ijw; DROP INDEX Time: 1541.102 ms CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w); CREATE INDEX Time: 27371.843 ms DROP INDEX ix_lotsofitext_zz2ijw; DROP INDEX Time: 1287.124 ms CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2); CREATE INDEX Time: 7015.005 ms DROP INDEX ix_lotsofitext_zz2; DROP INDEX Time: 30.725 ms CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2); CREATE INDEX Time: 7363.067 ms DROP INDEX ix_lotsofitext_zz2; DROP INDEX Time: 31.886 ms CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2); CREATE INDEX Time: 6951.924 ms DROP INDEX ix_lotsofitext_zz2; DROP INDEX Time: 31.727 ms CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2); CREATE INDEX Time: 6816.750 ms DROP INDEX ix_lotsofitext_zz2; DROP INDEX Time: 30.354 ms Timing is on. show work_mem; work_mem ---------- 1GB (1 row) Time: 0.165 ms show maintenance_work_mem; maintenance_work_mem ---------------------- 1GB (1 row) Time: 0.073 ms select count(*) FROM (select * from lotsofints order by i) t; count ---------- 10000000 (1 row) Time: 3863.884 ms select count(*) FROM (select * from lotsofints order by i) t; count ---------- 10000000 (1 row) Time: 3799.446 ms select count(*) FROM (select * from lotsofints order by i) t; count ---------- 10000000 (1 row) Time: 3911.632 ms select count(*) FROM (select * from lotsofints order by i) t; count ---------- 10000000 (1 row) Time: 3815.157 ms select count(*) FROM (select * from lotsoftext order by i) t; count ---------- 10000000 (1 row) Time: 63548.973 ms select count(*) FROM (select * from lotsoftext order by i) t; count ---------- 10000000 (1 row) Time: 63452.637 ms select count(*) FROM (select * from lotsoftext order by i) t; count ---------- 10000000 (1 row) Time: 63481.833 ms select count(*) FROM (select * from lotsoftext order by i) t; count ---------- 10000000 (1 row) Time: 63636.266 ms select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t; count ---------- 10000000 (1 row) Time: 65821.244 ms select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t; count ---------- 10000000 (1 row) Time: 64450.999 ms select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t; count ---------- 10000000 (1 row) Time: 66181.960 ms select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t; count ---------- 10000000 (1 row) Time: 67526.196 ms select count(*) FROM (select * from lotsofitext order by i, j) t; count ---------- 10000000 (1 row) Time: 65216.475 ms select count(*) FROM (select * from lotsofitext order by i, j) t; count ---------- 10000000 (1 row) Time: 64314.125 ms select count(*) FROM (select * from lotsofitext order by i, j) t; count ---------- 10000000 (1 row) Time: 64321.638 ms select count(*) FROM (select * from lotsofitext order by i, j) t; count ---------- 10000000 (1 row) Time: 66237.606 ms select count(*) FROM (select * from lotsofitext order by z, z2) t; count ---------- 10000000 (1 row) Time: 6101.759 ms select count(*) FROM (select * from lotsofitext order by z, z2) t; count ---------- 10000000 (1 row) Time: 5973.436 ms select count(*) FROM (select * from lotsofitext order by z, z2) t; count ---------- 10000000 (1 row) Time: 6211.455 ms select count(*) FROM (select * from lotsofitext order by z, z2) t; count ---------- 10000000 (1 row) Time: 5734.812 ms select count(*) FROM (select * from lotsofitext order by i, z) t; count ---------- 10000000 (1 row) Time: 65467.998 ms select count(*) FROM (select * from lotsofitext order by i, z) t; count ---------- 10000000 (1 row) Time: 66801.116 ms select count(*) FROM (select * from lotsofitext order by i, z) t; count ---------- 10000000 (1 row) Time: 66963.100 ms select count(*) FROM (select * from lotsofitext order by i, z) t; count ---------- 10000000 (1 row) Time: 65109.597 ms select count(*) FROM lotsofints; count ---------- 10000000 (1 row) Time: 251.998 ms CREATE INDEX ix_lotsofints ON lotsofints (i); CREATE INDEX Time: 5131.334 ms DROP INDEX ix_lotsofints; DROP INDEX Time: 24.251 ms CREATE INDEX ix_lotsofints ON lotsofints (i); CREATE INDEX Time: 5165.154 ms DROP INDEX ix_lotsofints; DROP INDEX Time: 23.050 ms CREATE INDEX ix_lotsofints ON lotsofints (i); CREATE INDEX Time: 5059.564 ms DROP INDEX ix_lotsofints; DROP INDEX Time: 25.383 ms CREATE INDEX ix_lotsofints ON lotsofints (i); CREATE INDEX Time: 5084.904 ms DROP INDEX ix_lotsofints; DROP INDEX Time: 23.716 ms select count(*) FROM lotsoftext; count ---------- 10000000 (1 row) Time: 287.666 ms CREATE INDEX ix_lotsoftext ON lotsoftext (i); CREATE INDEX Time: 67916.794 ms DROP INDEX ix_lotsoftext; DROP INDEX Time: 54.073 ms CREATE INDEX ix_lotsoftext ON lotsoftext (i); CREATE INDEX Time: 67710.106 ms DROP INDEX ix_lotsoftext; DROP INDEX Time: 428.187 ms CREATE INDEX ix_lotsoftext ON lotsoftext (i); CREATE INDEX Time: 67835.735 ms DROP INDEX ix_lotsoftext; DROP INDEX Time: 56.055 ms CREATE INDEX ix_lotsoftext ON lotsoftext (i); CREATE INDEX Time: 68134.256 ms DROP INDEX ix_lotsoftext; DROP INDEX Time: 357.544 ms select count(*) FROM lotsofitext; count ---------- 10000000 (1 row) Time: 388.341 ms CREATE INDEX ix_lotsofitext ON lotsofitext (i); CREATE INDEX Time: 69056.514 ms DROP INDEX ix_lotsofitext; DROP INDEX Time: 250.908 ms CREATE INDEX ix_lotsofitext ON lotsofitext (i); CREATE INDEX Time: 68188.257 ms DROP INDEX ix_lotsofitext; DROP INDEX Time: 324.054 ms CREATE INDEX ix_lotsofitext ON lotsofitext (i); CREATE INDEX Time: 67887.879 ms DROP INDEX ix_lotsofitext; DROP INDEX Time: 55.114 ms CREATE INDEX ix_lotsofitext ON lotsofitext (i); CREATE INDEX Time: 66799.404 ms DROP INDEX ix_lotsofitext; DROP INDEX Time: 232.046 ms CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2); CREATE INDEX Time: 76360.744 ms DROP INDEX ix_lotsofitext_ijwzz2; DROP INDEX Time: 1616.634 ms CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2); CREATE INDEX Time: 76715.719 ms DROP INDEX ix_lotsofitext_ijwzz2; DROP INDEX Time: 1398.882 ms CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2); CREATE INDEX Time: 74872.125 ms DROP INDEX ix_lotsofitext_ijwzz2; DROP INDEX Time: 1463.035 ms CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2); CREATE INDEX Time: 74721.978 ms DROP INDEX ix_lotsofitext_ijwzz2; DROP INDEX Time: 1558.267 ms CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w); CREATE INDEX Time: 40629.584 ms DROP INDEX ix_lotsofitext_zz2ijw; DROP INDEX Time: 152.836 ms CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w); CREATE INDEX Time: 34376.882 ms DROP INDEX ix_lotsofitext_zz2ijw; DROP INDEX Time: 1322.225 ms CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w); CREATE INDEX Time: 38097.157 ms DROP INDEX ix_lotsofitext_zz2ijw; DROP INDEX Time: 3234.589 ms CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w); CREATE INDEX Time: 33381.860 ms DROP INDEX ix_lotsofitext_zz2ijw; DROP INDEX Time: 1557.195 ms CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2); CREATE INDEX Time: 5842.315 ms DROP INDEX ix_lotsofitext_zz2; DROP INDEX Time: 31.945 ms CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2); CREATE INDEX Time: 5812.605 ms DROP INDEX ix_lotsofitext_zz2; DROP INDEX Time: 32.594 ms CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2); CREATE INDEX Time: 5800.962 ms DROP INDEX ix_lotsofitext_zz2; DROP INDEX Time: 34.278 ms CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2); CREATE INDEX Time: 5831.418 ms DROP INDEX ix_lotsofitext_zz2; DROP INDEX Time: 32.776 ms Timing is on. show work_mem; work_mem ---------- 4GB (1 row) Time: 0.189 ms show maintenance_work_mem; maintenance_work_mem ---------------------- 4GB (1 row) Time: 0.075 ms select count(*) FROM (select * from lotsofints order by i) t; count ---------- 10000000 (1 row) Time: 3697.817 ms select count(*) FROM (select * from lotsofints order by i) t; count ---------- 10000000 (1 row) Time: 3685.056 ms select count(*) FROM (select * from lotsofints order by i) t; count ---------- 10000000 (1 row) Time: 3700.629 ms select count(*) FROM (select * from lotsofints order by i) t; count ---------- 10000000 (1 row) Time: 3691.971 ms select count(*) FROM (select * from lotsoftext order by i) t; count ---------- 10000000 (1 row) Time: 58453.154 ms select count(*) FROM (select * from lotsoftext order by i) t; count ---------- 10000000 (1 row) Time: 57609.017 ms select count(*) FROM (select * from lotsoftext order by i) t; count ---------- 10000000 (1 row) Time: 58122.744 ms select count(*) FROM (select * from lotsoftext order by i) t; count ---------- 10000000 (1 row) Time: 58230.818 ms select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t; count ---------- 10000000 (1 row) Time: 65616.708 ms select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t; count ---------- 10000000 (1 row) Time: 65536.904 ms select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t; count ---------- 10000000 (1 row) Time: 69543.310 ms select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t; count ---------- 10000000 (1 row) Time: 65709.001 ms select count(*) FROM (select * from lotsofitext order by i, j) t; count ---------- 10000000 (1 row) Time: 62596.253 ms select count(*) FROM (select * from lotsofitext order by i, j) t; count ---------- 10000000 (1 row) Time: 63502.543 ms select count(*) FROM (select * from lotsofitext order by i, j) t; count ---------- 10000000 (1 row) Time: 62962.022 ms select count(*) FROM (select * from lotsofitext order by i, j) t; count ---------- 10000000 (1 row) Time: 63576.934 ms select count(*) FROM (select * from lotsofitext order by z, z2) t; count ---------- 10000000 (1 row) Time: 5828.119 ms select count(*) FROM (select * from lotsofitext order by z, z2) t; count ---------- 10000000 (1 row) Time: 6003.558 ms select count(*) FROM (select * from lotsofitext order by z, z2) t; count ---------- 10000000 (1 row) Time: 5975.630 ms select count(*) FROM (select * from lotsofitext order by z, z2) t; count ---------- 10000000 (1 row) Time: 5973.326 ms select count(*) FROM (select * from lotsofitext order by i, z) t; count ---------- 10000000 (1 row) Time: 62650.049 ms select count(*) FROM (select * from lotsofitext order by i, z) t; count ---------- 10000000 (1 row) Time: 61447.788 ms select count(*) FROM (select * from lotsofitext order by i, z) t; count ---------- 10000000 (1 row) Time: 61787.406 ms select count(*) FROM (select * from lotsofitext order by i, z) t; count ---------- 10000000 (1 row) Time: 62524.886 ms select count(*) FROM lotsofints; count ---------- 10000000 (1 row) Time: 263.594 ms CREATE INDEX ix_lotsofints ON lotsofints (i); CREATE INDEX Time: 5137.231 ms DROP INDEX ix_lotsofints; DROP INDEX Time: 23.864 ms CREATE INDEX ix_lotsofints ON lotsofints (i); CREATE INDEX Time: 5099.026 ms DROP INDEX ix_lotsofints; DROP INDEX Time: 23.320 ms CREATE INDEX ix_lotsofints ON lotsofints (i); CREATE INDEX Time: 5081.905 ms DROP INDEX ix_lotsofints; DROP INDEX Time: 23.134 ms CREATE INDEX ix_lotsofints ON lotsofints (i); CREATE INDEX Time: 5104.520 ms DROP INDEX ix_lotsofints; DROP INDEX Time: 24.066 ms select count(*) FROM lotsoftext; count ---------- 10000000 (1 row) Time: 288.612 ms CREATE INDEX ix_lotsoftext ON lotsoftext (i); CREATE INDEX Time: 63509.720 ms DROP INDEX ix_lotsoftext; DROP INDEX Time: 58.994 ms CREATE INDEX ix_lotsoftext ON lotsoftext (i); CREATE INDEX Time: 62728.443 ms DROP INDEX ix_lotsoftext; DROP INDEX Time: 61.060 ms CREATE INDEX ix_lotsoftext ON lotsoftext (i); CREATE INDEX Time: 61140.845 ms DROP INDEX ix_lotsoftext; DROP INDEX Time: 59.593 ms CREATE INDEX ix_lotsoftext ON lotsoftext (i); CREATE INDEX Time: 62224.583 ms DROP INDEX ix_lotsoftext; DROP INDEX Time: 59.864 ms select count(*) FROM lotsofitext; count ---------- 10000000 (1 row) Time: 365.922 ms CREATE INDEX ix_lotsofitext ON lotsofitext (i); CREATE INDEX Time: 62362.148 ms DROP INDEX ix_lotsofitext; DROP INDEX Time: 59.167 ms CREATE INDEX ix_lotsofitext ON lotsofitext (i); CREATE INDEX Time: 64598.948 ms DROP INDEX ix_lotsofitext; DROP INDEX Time: 58.970 ms CREATE INDEX ix_lotsofitext ON lotsofitext (i); CREATE INDEX Time: 65049.680 ms DROP INDEX ix_lotsofitext; DROP INDEX Time: 63.847 ms CREATE INDEX ix_lotsofitext ON lotsofitext (i); CREATE INDEX Time: 66825.887 ms DROP INDEX ix_lotsofitext; DROP INDEX Time: 65.397 ms CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2); CREATE INDEX Time: 73882.420 ms DROP INDEX ix_lotsofitext_ijwzz2; DROP INDEX Time: 1452.198 ms CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2); CREATE INDEX Time: 69040.889 ms DROP INDEX ix_lotsofitext_ijwzz2; DROP INDEX Time: 1532.809 ms CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2); CREATE INDEX Time: 68319.287 ms DROP INDEX ix_lotsofitext_ijwzz2; DROP INDEX Time: 1388.133 ms CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2); CREATE INDEX Time: 67632.835 ms DROP INDEX ix_lotsofitext_ijwzz2; DROP INDEX Time: 1389.277 ms CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w); CREATE INDEX Time: 14544.098 ms DROP INDEX ix_lotsofitext_zz2ijw; DROP INDEX Time: 1618.593 ms CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w); CREATE INDEX Time: 15062.226 ms DROP INDEX ix_lotsofitext_zz2ijw; DROP INDEX Time: 1224.036 ms CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w); CREATE INDEX Time: 14203.197 ms DROP INDEX ix_lotsofitext_zz2ijw; DROP INDEX Time: 1479.556 ms CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w); CREATE INDEX Time: 14446.617 ms DROP INDEX ix_lotsofitext_zz2ijw; DROP INDEX Time: 1065.954 ms CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2); CREATE INDEX Time: 5810.151 ms DROP INDEX ix_lotsofitext_zz2; DROP INDEX Time: 33.996 ms CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2); CREATE INDEX Time: 5795.796 ms DROP INDEX ix_lotsofitext_zz2; DROP INDEX Time: 33.611 ms CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2); CREATE INDEX Time: 5804.430 ms DROP INDEX ix_lotsofitext_zz2; DROP INDEX Time: 33.145 ms CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2); CREATE INDEX Time: 5787.872 ms DROP INDEX ix_lotsofitext_zz2; DROP INDEX Time: 34.173 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.172 ms show maintenance_work_mem; maintenance_work_mem ---------------------- 4MB (1 row) Time: 0.074 ms select count(*) FROM (select * from lotsofints order by i) t; count ---------- 10000000 (1 row) Time: 8295.643 ms select count(*) FROM (select * from lotsofints order by i) t; count ---------- 10000000 (1 row) Time: 8339.915 ms select count(*) FROM (select * from lotsofints order by i) t; count ---------- 10000000 (1 row) Time: 6717.467 ms select count(*) FROM (select * from lotsofints order by i) t; count ---------- 10000000 (1 row) Time: 6601.435 ms select count(*) FROM (select * from lotsoftext order by i) t; count ---------- 10000000 (1 row) Time: 455787.222 ms select count(*) FROM (select * from lotsoftext order by i) t; count ---------- 10000000 (1 row) Time: 457387.053 ms select count(*) FROM (select * from lotsoftext order by i) t; count ---------- 10000000 (1 row) Time: 480056.557 ms select count(*) FROM (select * from lotsoftext order by i) t; count ---------- 10000000 (1 row) Time: 491139.786 ms select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t; count ---------- 10000000 (1 row) Time: 525572.886 ms select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t; count ---------- 10000000 (1 row) Time: 527919.217 ms select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t; count ---------- 10000000 (1 row) Time: 534245.717 ms select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t; count ---------- 10000000 (1 row) Time: 517902.126 ms select count(*) FROM (select * from lotsofitext order by i, j) t; count ---------- 10000000 (1 row) Time: 510758.924 ms select count(*) FROM (select * from lotsofitext order by i, j) t; count ---------- 10000000 (1 row) Time: 515181.160 ms select count(*) FROM (select * from lotsofitext order by i, j) t; count ---------- 10000000 (1 row) Time: 513177.443 ms select count(*) FROM (select * from lotsofitext order by i, j) t; count ---------- 10000000 (1 row) Time: 496054.216 ms select count(*) FROM (select * from lotsofitext order by z, z2) t; count ---------- 10000000 (1 row) Time: 9977.011 ms select count(*) FROM (select * from lotsofitext order by z, z2) t; count ---------- 10000000 (1 row) Time: 9601.310 ms select count(*) FROM (select * from lotsofitext order by z, z2) t; count ---------- 10000000 (1 row) Time: 9682.042 ms select count(*) FROM (select * from lotsofitext order by z, z2) t; count ---------- 10000000 (1 row) Time: 9774.768 ms select count(*) FROM (select * from lotsofitext order by i, z) t; count ---------- 10000000 (1 row) Time: 486178.522 ms select count(*) FROM (select * from lotsofitext order by i, z) t; count ---------- 10000000 (1 row) Time: 495249.936 ms select count(*) FROM (select * from lotsofitext order by i, z) t; count ---------- 10000000 (1 row) Time: 499707.736 ms select count(*) FROM (select * from lotsofitext order by i, z) t; count ---------- 10000000 (1 row) Time: 496055.198 ms select count(*) FROM lotsofints; count ---------- 10000000 (1 row) Time: 262.550 ms CREATE INDEX ix_lotsofints ON lotsofints (i); CREATE INDEX Time: 8383.067 ms DROP INDEX ix_lotsofints; DROP INDEX Time: 23.953 ms CREATE INDEX ix_lotsofints ON lotsofints (i); CREATE INDEX Time: 8420.186 ms DROP INDEX ix_lotsofints; DROP INDEX Time: 24.201 ms CREATE INDEX ix_lotsofints ON lotsofints (i); CREATE INDEX Time: 8515.804 ms DROP INDEX ix_lotsofints; DROP INDEX Time: 26.672 ms CREATE INDEX ix_lotsofints ON lotsofints (i); CREATE INDEX Time: 8369.662 ms DROP INDEX ix_lotsofints; DROP INDEX Time: 24.420 ms select count(*) FROM lotsoftext; count ---------- 10000000 (1 row) Time: 353.619 ms CREATE INDEX ix_lotsoftext ON lotsoftext (i); CREATE INDEX Time: 494537.650 ms DROP INDEX ix_lotsoftext; DROP INDEX Time: 324.412 ms CREATE INDEX ix_lotsoftext ON lotsoftext (i); CREATE INDEX Time: 503307.448 ms DROP INDEX ix_lotsoftext; DROP INDEX Time: 1472.262 ms CREATE INDEX ix_lotsoftext ON lotsoftext (i); CREATE INDEX Time: 494890.200 ms DROP INDEX ix_lotsoftext; DROP INDEX Time: 101.665 ms CREATE INDEX ix_lotsoftext ON lotsoftext (i); CREATE INDEX Time: 491586.853 ms DROP INDEX ix_lotsoftext; DROP INDEX Time: 97.377 ms select count(*) FROM lotsofitext; count ---------- 10000000 (1 row) Time: 520.671 ms CREATE INDEX ix_lotsofitext ON lotsofitext (i); CREATE INDEX Time: 486904.827 ms DROP INDEX ix_lotsofitext; DROP INDEX Time: 100.274 ms CREATE INDEX ix_lotsofitext ON lotsofitext (i); CREATE INDEX Time: 487254.128 ms DROP INDEX ix_lotsofitext; DROP INDEX Time: 125.986 ms CREATE INDEX ix_lotsofitext ON lotsofitext (i); CREATE INDEX Time: 474777.094 ms DROP INDEX ix_lotsofitext; DROP INDEX Time: 98.137 ms CREATE INDEX ix_lotsofitext ON lotsofitext (i); CREATE INDEX Time: 500952.817 ms DROP INDEX ix_lotsofitext; DROP INDEX Time: 105.925 ms CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2); CREATE INDEX Time: 552099.851 ms DROP INDEX ix_lotsofitext_ijwzz2; DROP INDEX Time: 1738.551 ms CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2); CREATE INDEX Time: 585214.356 ms DROP INDEX ix_lotsofitext_ijwzz2; DROP INDEX Time: 1634.074 ms CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2); CREATE INDEX Time: 576765.221 ms DROP INDEX ix_lotsofitext_ijwzz2; DROP INDEX Time: 1283.087 ms CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2); CREATE INDEX Time: 545324.838 ms DROP INDEX ix_lotsofitext_ijwzz2; DROP INDEX Time: 1939.677 ms CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w); CREATE INDEX Time: 230606.332 ms DROP INDEX ix_lotsofitext_zz2ijw; DROP INDEX Time: 3085.006 ms CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w); CREATE INDEX Time: 215599.556 ms DROP INDEX ix_lotsofitext_zz2ijw; DROP INDEX Time: 2267.564 ms CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w); CREATE INDEX Time: 221453.695 ms DROP INDEX ix_lotsofitext_zz2ijw; DROP INDEX Time: 4015.027 ms CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w); CREATE INDEX Time: 207143.081 ms DROP INDEX ix_lotsofitext_zz2ijw; DROP INDEX Time: 3595.175 ms CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2); CREATE INDEX Time: 10330.260 ms DROP INDEX ix_lotsofitext_zz2; DROP INDEX Time: 33.724 ms CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2); CREATE INDEX Time: 10089.513 ms DROP INDEX ix_lotsofitext_zz2; DROP INDEX Time: 33.235 ms CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2); CREATE INDEX Time: 10072.124 ms DROP INDEX ix_lotsofitext_zz2; DROP INDEX Time: 32.998 ms CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2); CREATE INDEX Time: 11774.484 ms DROP INDEX ix_lotsofitext_zz2; DROP INDEX Time: 33.840 ms Timing is on. show work_mem; work_mem ---------- 64MB (1 row) Time: 59.714 ms show maintenance_work_mem; maintenance_work_mem ---------------------- 64MB (1 row) Time: 0.127 ms select count(*) FROM (select * from lotsofints order by i) t; count ---------- 10000000 (1 row) Time: 9770.565 ms select count(*) FROM (select * from lotsofints order by i) t; count ---------- 10000000 (1 row) Time: 6403.355 ms select count(*) FROM (select * from lotsofints order by i) t; count ---------- 10000000 (1 row) Time: 6219.948 ms select count(*) FROM (select * from lotsofints order by i) t; count ---------- 10000000 (1 row) Time: 6260.003 ms select count(*) FROM (select * from lotsoftext order by i) t; count ---------- 10000000 (1 row) Time: 457063.338 ms select count(*) FROM (select * from lotsoftext order by i) t; count ---------- 10000000 (1 row) Time: 447313.297 ms select count(*) FROM (select * from lotsoftext order by i) t; count ---------- 10000000 (1 row) Time: 445236.174 ms select count(*) FROM (select * from lotsoftext order by i) t; count ---------- 10000000 (1 row) Time: 449799.326 ms select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t; count ---------- 10000000 (1 row) Time: 474638.718 ms select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t; count ---------- 10000000 (1 row) Time: 469117.591 ms select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t; count ---------- 10000000 (1 row) Time: 475823.062 ms select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t; count ---------- 10000000 (1 row) Time: 473850.746 ms select count(*) FROM (select * from lotsofitext order by i, j) t; count ---------- 10000000 (1 row) Time: 460273.503 ms select count(*) FROM (select * from lotsofitext order by i, j) t; count ---------- 10000000 (1 row) Time: 459009.989 ms select count(*) FROM (select * from lotsofitext order by i, j) t; count ---------- 10000000 (1 row) Time: 467786.629 ms select count(*) FROM (select * from lotsofitext order by i, j) t; count ---------- 10000000 (1 row) Time: 470496.436 ms select count(*) FROM (select * from lotsofitext order by z, z2) t; count ---------- 10000000 (1 row) Time: 9150.383 ms select count(*) FROM (select * from lotsofitext order by z, z2) t; count ---------- 10000000 (1 row) Time: 9119.903 ms select count(*) FROM (select * from lotsofitext order by z, z2) t; count ---------- 10000000 (1 row) Time: 9105.502 ms select count(*) FROM (select * from lotsofitext order by z, z2) t; count ---------- 10000000 (1 row) Time: 9094.891 ms select count(*) FROM (select * from lotsofitext order by i, z) t; count ---------- 10000000 (1 row) Time: 460473.684 ms select count(*) FROM (select * from lotsofitext order by i, z) t; count ---------- 10000000 (1 row) Time: 454730.711 ms select count(*) FROM (select * from lotsofitext order by i, z) t; count ---------- 10000000 (1 row) Time: 456040.474 ms select count(*) FROM (select * from lotsofitext order by i, z) t; count ---------- 10000000 (1 row) Time: 447484.722 ms select count(*) FROM lotsofints; count ---------- 10000000 (1 row) Time: 393.277 ms CREATE INDEX ix_lotsofints ON lotsofints (i); CREATE INDEX Time: 7144.549 ms DROP INDEX ix_lotsofints; DROP INDEX Time: 86.853 ms CREATE INDEX ix_lotsofints ON lotsofints (i); CREATE INDEX Time: 7026.322 ms DROP INDEX ix_lotsofints; DROP INDEX Time: 24.741 ms CREATE INDEX ix_lotsofints ON lotsofints (i); CREATE INDEX Time: 7102.245 ms DROP INDEX ix_lotsofints; DROP INDEX Time: 25.787 ms CREATE INDEX ix_lotsofints ON lotsofints (i); CREATE INDEX Time: 7044.840 ms DROP INDEX ix_lotsofints; DROP INDEX Time: 24.552 ms select count(*) FROM lotsoftext; count ---------- 10000000 (1 row) Time: 335.559 ms CREATE INDEX ix_lotsoftext ON lotsoftext (i); CREATE INDEX Time: 438831.022 ms DROP INDEX ix_lotsoftext; DROP INDEX Time: 108.120 ms CREATE INDEX ix_lotsoftext ON lotsoftext (i); CREATE INDEX Time: 429290.994 ms DROP INDEX ix_lotsoftext; DROP INDEX Time: 97.388 ms CREATE INDEX ix_lotsoftext ON lotsoftext (i); CREATE INDEX Time: 426348.287 ms DROP INDEX ix_lotsoftext; DROP INDEX Time: 98.090 ms CREATE INDEX ix_lotsoftext ON lotsoftext (i); CREATE INDEX Time: 422380.499 ms DROP INDEX ix_lotsoftext; DROP INDEX Time: 96.441 ms select count(*) FROM lotsofitext; count ---------- 10000000 (1 row) Time: 679.662 ms CREATE INDEX ix_lotsofitext ON lotsofitext (i); CREATE INDEX Time: 426547.631 ms DROP INDEX ix_lotsofitext; DROP INDEX Time: 122.154 ms CREATE INDEX ix_lotsofitext ON lotsofitext (i); CREATE INDEX Time: 430546.349 ms DROP INDEX ix_lotsofitext; DROP INDEX Time: 103.096 ms CREATE INDEX ix_lotsofitext ON lotsofitext (i); CREATE INDEX Time: 408848.423 ms DROP INDEX ix_lotsofitext; DROP INDEX Time: 96.514 ms CREATE INDEX ix_lotsofitext ON lotsofitext (i); CREATE INDEX Time: 409810.784 ms DROP INDEX ix_lotsofitext; DROP INDEX Time: 97.578 ms CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2); CREATE INDEX Time: 435793.760 ms DROP INDEX ix_lotsofitext_ijwzz2; DROP INDEX Time: 246.269 ms CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2); CREATE INDEX Time: 435879.583 ms DROP INDEX ix_lotsofitext_ijwzz2; DROP INDEX Time: 239.392 ms CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2); CREATE INDEX Time: 437225.609 ms DROP INDEX ix_lotsofitext_ijwzz2; DROP INDEX Time: 1793.833 ms CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2); CREATE INDEX Time: 435293.350 ms DROP INDEX ix_lotsofitext_ijwzz2; DROP INDEX Time: 239.956 ms CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w); CREATE INDEX Time: 70791.364 ms DROP INDEX ix_lotsofitext_zz2ijw; DROP INDEX Time: 2955.434 ms CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w); CREATE INDEX Time: 71277.599 ms DROP INDEX ix_lotsofitext_zz2ijw; DROP INDEX Time: 3311.814 ms CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w); CREATE INDEX Time: 65716.517 ms DROP INDEX ix_lotsofitext_zz2ijw; DROP INDEX Time: 2379.624 ms CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w); CREATE INDEX Time: 64482.973 ms DROP INDEX ix_lotsofitext_zz2ijw; DROP INDEX Time: 3338.400 ms CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2); CREATE INDEX Time: 8140.979 ms DROP INDEX ix_lotsofitext_zz2; DROP INDEX Time: 33.467 ms CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2); CREATE INDEX Time: 8194.664 ms DROP INDEX ix_lotsofitext_zz2; DROP INDEX Time: 33.481 ms CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2); CREATE INDEX Time: 8166.736 ms DROP INDEX ix_lotsofitext_zz2; DROP INDEX Time: 33.778 ms CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2); CREATE INDEX Time: 8185.190 ms DROP INDEX ix_lotsofitext_zz2; DROP INDEX Time: 32.473 ms Timing is on. show work_mem; work_mem ---------- 256MB (1 row) Time: 25.232 ms show maintenance_work_mem; maintenance_work_mem ---------------------- 256MB (1 row) Time: 0.160 ms select count(*) FROM (select * from lotsofints order by i) t; count ---------- 10000000 (1 row) Time: 6628.818 ms select count(*) FROM (select * from lotsofints order by i) t; count ---------- 10000000 (1 row) Time: 4882.024 ms select count(*) FROM (select * from lotsofints order by i) t; count ---------- 10000000 (1 row) Time: 4885.018 ms select count(*) FROM (select * from lotsofints order by i) t; count ---------- 10000000 (1 row) Time: 4986.430 ms select count(*) FROM (select * from lotsoftext order by i) t; count ---------- 10000000 (1 row) Time: 384344.027 ms select count(*) FROM (select * from lotsoftext order by i) t; count ---------- 10000000 (1 row) Time: 391318.084 ms select count(*) FROM (select * from lotsoftext order by i) t; count ---------- 10000000 (1 row) Time: 386110.152 ms select count(*) FROM (select * from lotsoftext order by i) t; count ---------- 10000000 (1 row) Time: 387006.023 ms select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t; count ---------- 10000000 (1 row) Time: 409847.639 ms select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t; count ---------- 10000000 (1 row) Time: 409122.753 ms select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t; count ---------- 10000000 (1 row) Time: 407799.583 ms select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t; count ---------- 10000000 (1 row) Time: 407236.937 ms select count(*) FROM (select * from lotsofitext order by i, j) t; count ---------- 10000000 (1 row) Time: 401274.027 ms select count(*) FROM (select * from lotsofitext order by i, j) t; count ---------- 10000000 (1 row) Time: 398380.407 ms select count(*) FROM (select * from lotsofitext order by i, j) t; count ---------- 10000000 (1 row) Time: 402950.104 ms select count(*) FROM (select * from lotsofitext order by i, j) t; count ---------- 10000000 (1 row) Time: 399682.566 ms select count(*) FROM (select * from lotsofitext order by z, z2) t; count ---------- 10000000 (1 row) Time: 7106.676 ms select count(*) FROM (select * from lotsofitext order by z, z2) t; count ---------- 10000000 (1 row) Time: 7095.074 ms select count(*) FROM (select * from lotsofitext order by z, z2) t; count ---------- 10000000 (1 row) Time: 7109.773 ms select count(*) FROM (select * from lotsofitext order by z, z2) t; count ---------- 10000000 (1 row) Time: 7102.870 ms select count(*) FROM (select * from lotsofitext order by i, z) t; count ---------- 10000000 (1 row) Time: 391745.691 ms select count(*) FROM (select * from lotsofitext order by i, z) t; count ---------- 10000000 (1 row) Time: 390084.502 ms select count(*) FROM (select * from lotsofitext order by i, z) t; count ---------- 10000000 (1 row) Time: 389276.100 ms select count(*) FROM (select * from lotsofitext order by i, z) t; count ---------- 10000000 (1 row) Time: 389095.984 ms select count(*) FROM lotsofints; count ---------- 10000000 (1 row) Time: 610.593 ms CREATE INDEX ix_lotsofints ON lotsofints (i); CREATE INDEX Time: 5898.466 ms DROP INDEX ix_lotsofints; DROP INDEX Time: 44.652 ms CREATE INDEX ix_lotsofints ON lotsofints (i); CREATE INDEX Time: 5841.135 ms DROP INDEX ix_lotsofints; DROP INDEX Time: 25.304 ms CREATE INDEX ix_lotsofints ON lotsofints (i); CREATE INDEX Time: 5830.256 ms DROP INDEX ix_lotsofints; DROP INDEX Time: 24.251 ms CREATE INDEX ix_lotsofints ON lotsofints (i); CREATE INDEX Time: 5895.625 ms DROP INDEX ix_lotsofints; DROP INDEX Time: 24.042 ms select count(*) FROM lotsoftext; count ---------- 10000000 (1 row) Time: 330.286 ms CREATE INDEX ix_lotsoftext ON lotsoftext (i); CREATE INDEX Time: 392238.922 ms DROP INDEX ix_lotsoftext; DROP INDEX Time: 100.061 ms CREATE INDEX ix_lotsoftext ON lotsoftext (i); CREATE INDEX Time: 388769.586 ms DROP INDEX ix_lotsoftext; DROP INDEX Time: 99.819 ms CREATE INDEX ix_lotsoftext ON lotsoftext (i); CREATE INDEX Time: 389397.908 ms DROP INDEX ix_lotsoftext; DROP INDEX Time: 99.122 ms CREATE INDEX ix_lotsoftext ON lotsoftext (i); CREATE INDEX Time: 390133.826 ms DROP INDEX ix_lotsoftext; DROP INDEX Time: 99.194 ms select count(*) FROM lotsofitext; count ---------- 10000000 (1 row) Time: 604.436 ms CREATE INDEX ix_lotsofitext ON lotsofitext (i); CREATE INDEX Time: 392817.565 ms DROP INDEX ix_lotsofitext; DROP INDEX Time: 105.411 ms CREATE INDEX ix_lotsofitext ON lotsofitext (i); CREATE INDEX Time: 391252.812 ms DROP INDEX ix_lotsofitext; DROP INDEX Time: 101.355 ms CREATE INDEX ix_lotsofitext ON lotsofitext (i); CREATE INDEX Time: 393838.659 ms DROP INDEX ix_lotsofitext; DROP INDEX Time: 104.929 ms CREATE INDEX ix_lotsofitext ON lotsofitext (i); CREATE INDEX Time: 392756.457 ms DROP INDEX ix_lotsofitext; DROP INDEX Time: 104.394 ms CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2); CREATE INDEX Time: 413362.525 ms DROP INDEX ix_lotsofitext_ijwzz2; DROP INDEX Time: 1245.076 ms CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2); CREATE INDEX Time: 416347.112 ms DROP INDEX ix_lotsofitext_ijwzz2; DROP INDEX Time: 1256.432 ms CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2); CREATE INDEX Time: 414215.939 ms DROP INDEX ix_lotsofitext_ijwzz2; DROP INDEX Time: 1636.560 ms CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2); CREATE INDEX Time: 417481.416 ms DROP INDEX ix_lotsofitext_ijwzz2; DROP INDEX Time: 1600.806 ms CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w); CREATE INDEX Time: 66491.118 ms DROP INDEX ix_lotsofitext_zz2ijw; DROP INDEX Time: 1969.042 ms CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w); CREATE INDEX Time: 65091.235 ms DROP INDEX ix_lotsofitext_zz2ijw; DROP INDEX Time: 1688.401 ms CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w); CREATE INDEX Time: 64095.433 ms DROP INDEX ix_lotsofitext_zz2ijw; DROP INDEX Time: 1753.545 ms CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w); CREATE INDEX Time: 63605.604 ms DROP INDEX ix_lotsofitext_zz2ijw; DROP INDEX Time: 2929.202 ms CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2); CREATE INDEX Time: 7096.619 ms DROP INDEX ix_lotsofitext_zz2; DROP INDEX Time: 34.515 ms CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2); CREATE INDEX Time: 7031.064 ms DROP INDEX ix_lotsofitext_zz2; DROP INDEX Time: 32.229 ms CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2); CREATE INDEX Time: 7059.976 ms DROP INDEX ix_lotsofitext_zz2; DROP INDEX Time: 34.265 ms CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2); CREATE INDEX Time: 7173.641 ms DROP INDEX ix_lotsofitext_zz2; DROP INDEX Time: 32.439 ms Timing is on. show work_mem; work_mem ---------- 1GB (1 row) Time: 0.195 ms show maintenance_work_mem; maintenance_work_mem ---------------------- 1GB (1 row) Time: 0.118 ms select count(*) FROM (select * from lotsofints order by i) t; count ---------- 10000000 (1 row) Time: 4387.202 ms select count(*) FROM (select * from lotsofints order by i) t; count ---------- 10000000 (1 row) Time: 3584.826 ms select count(*) FROM (select * from lotsofints order by i) t; count ---------- 10000000 (1 row) Time: 3566.177 ms select count(*) FROM (select * from lotsofints order by i) t; count ---------- 10000000 (1 row) Time: 3568.574 ms select count(*) FROM (select * from lotsoftext order by i) t; count ---------- 10000000 (1 row) Time: 380684.041 ms select count(*) FROM (select * from lotsoftext order by i) t; count ---------- 10000000 (1 row) Time: 379918.096 ms select count(*) FROM (select * from lotsoftext order by i) t; count ---------- 10000000 (1 row) Time: 381692.276 ms select count(*) FROM (select * from lotsoftext order by i) t; count ---------- 10000000 (1 row) Time: 380428.536 ms select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t; count ---------- 10000000 (1 row) Time: 390983.557 ms select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t; count ---------- 10000000 (1 row) Time: 391552.881 ms select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t; count ---------- 10000000 (1 row) Time: 390787.534 ms select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t; count ---------- 10000000 (1 row) Time: 388878.131 ms select count(*) FROM (select * from lotsofitext order by i, j) t; count ---------- 10000000 (1 row) Time: 389631.977 ms select count(*) FROM (select * from lotsofitext order by i, j) t; count ---------- 10000000 (1 row) Time: 384497.070 ms select count(*) FROM (select * from lotsofitext order by i, j) t; count ---------- 10000000 (1 row) Time: 386665.325 ms select count(*) FROM (select * from lotsofitext order by i, j) t; count ---------- 10000000 (1 row) Time: 386703.993 ms select count(*) FROM (select * from lotsofitext order by z, z2) t; count ---------- 10000000 (1 row) Time: 6078.169 ms select count(*) FROM (select * from lotsofitext order by z, z2) t; count ---------- 10000000 (1 row) Time: 6052.037 ms select count(*) FROM (select * from lotsofitext order by z, z2) t; count ---------- 10000000 (1 row) Time: 6064.208 ms select count(*) FROM (select * from lotsofitext order by z, z2) t; count ---------- 10000000 (1 row) Time: 6062.762 ms select count(*) FROM (select * from lotsofitext order by i, z) t; count ---------- 10000000 (1 row) Time: 379150.899 ms select count(*) FROM (select * from lotsofitext order by i, z) t; count ---------- 10000000 (1 row) Time: 377780.402 ms select count(*) FROM (select * from lotsofitext order by i, z) t; count ---------- 10000000 (1 row) Time: 378347.135 ms select count(*) FROM (select * from lotsofitext order by i, z) t; count ---------- 10000000 (1 row) Time: 379094.511 ms select count(*) FROM lotsofints; count ---------- 10000000 (1 row) Time: 587.814 ms CREATE INDEX ix_lotsofints ON lotsofints (i); CREATE INDEX Time: 5107.958 ms DROP INDEX ix_lotsofints; DROP INDEX Time: 45.124 ms CREATE INDEX ix_lotsofints ON lotsofints (i); CREATE INDEX Time: 5036.038 ms DROP INDEX ix_lotsofints; DROP INDEX Time: 26.190 ms CREATE INDEX ix_lotsofints ON lotsofints (i); CREATE INDEX Time: 4962.769 ms DROP INDEX ix_lotsofints; DROP INDEX Time: 26.088 ms CREATE INDEX ix_lotsofints ON lotsofints (i); CREATE INDEX Time: 4949.491 ms DROP INDEX ix_lotsofints; DROP INDEX Time: 24.927 ms select count(*) FROM lotsoftext; count ---------- 10000000 (1 row) Time: 329.842 ms CREATE INDEX ix_lotsoftext ON lotsoftext (i); CREATE INDEX Time: 383119.936 ms DROP INDEX ix_lotsoftext; DROP INDEX Time: 1268.520 ms CREATE INDEX ix_lotsoftext ON lotsoftext (i); CREATE INDEX Time: 380149.029 ms DROP INDEX ix_lotsoftext; DROP INDEX Time: 1471.244 ms CREATE INDEX ix_lotsoftext ON lotsoftext (i); CREATE INDEX Time: 382735.388 ms DROP INDEX ix_lotsoftext; DROP INDEX Time: 1468.506 ms CREATE INDEX ix_lotsoftext ON lotsoftext (i); CREATE INDEX Time: 382403.784 ms DROP INDEX ix_lotsoftext; DROP INDEX Time: 1122.993 ms select count(*) FROM lotsofitext; count ---------- 10000000 (1 row) Time: 556.340 ms CREATE INDEX ix_lotsofitext ON lotsofitext (i); CREATE INDEX Time: 384421.088 ms DROP INDEX ix_lotsofitext; DROP INDEX Time: 1242.206 ms CREATE INDEX ix_lotsofitext ON lotsofitext (i); CREATE INDEX Time: 383110.934 ms DROP INDEX ix_lotsofitext; DROP INDEX Time: 1298.565 ms CREATE INDEX ix_lotsofitext ON lotsofitext (i); CREATE INDEX Time: 382366.471 ms DROP INDEX ix_lotsofitext; DROP INDEX Time: 1471.117 ms CREATE INDEX ix_lotsofitext ON lotsofitext (i); CREATE INDEX Time: 383318.136 ms DROP INDEX ix_lotsofitext; DROP INDEX Time: 1534.896 ms CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2); CREATE INDEX Time: 409365.286 ms DROP INDEX ix_lotsofitext_ijwzz2; DROP INDEX Time: 186.902 ms CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2); CREATE INDEX Time: 413569.482 ms DROP INDEX ix_lotsofitext_ijwzz2; DROP INDEX Time: 221.169 ms CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2); CREATE INDEX Time: 403526.708 ms DROP INDEX ix_lotsofitext_ijwzz2; DROP INDEX Time: 259.163 ms CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2); CREATE INDEX Time: 403578.822 ms DROP INDEX ix_lotsofitext_ijwzz2; DROP INDEX Time: 257.767 ms CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w); CREATE INDEX Time: 66741.646 ms DROP INDEX ix_lotsofitext_zz2ijw; DROP INDEX Time: 2269.065 ms CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w); CREATE INDEX Time: 66268.281 ms DROP INDEX ix_lotsofitext_zz2ijw; DROP INDEX Time: 2952.174 ms CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w); CREATE INDEX Time: 66608.851 ms DROP INDEX ix_lotsofitext_zz2ijw; DROP INDEX Time: 2988.350 ms CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w); CREATE INDEX Time: 66577.783 ms DROP INDEX ix_lotsofitext_zz2ijw; DROP INDEX Time: 4461.361 ms CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2); CREATE INDEX Time: 5934.462 ms DROP INDEX ix_lotsofitext_zz2; DROP INDEX Time: 34.277 ms CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2); CREATE INDEX Time: 6007.914 ms DROP INDEX ix_lotsofitext_zz2; DROP INDEX Time: 34.991 ms CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2); CREATE INDEX Time: 6030.846 ms DROP INDEX ix_lotsofitext_zz2; DROP INDEX Time: 35.736 ms CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2); CREATE INDEX Time: 5964.042 ms DROP INDEX ix_lotsofitext_zz2; DROP INDEX Time: 34.360 ms Timing is on. show work_mem; work_mem ---------- 4GB (1 row) Time: 15.804 ms show maintenance_work_mem; maintenance_work_mem ---------------------- 4GB (1 row) Time: 0.131 ms select count(*) FROM (select * from lotsofints order by i) t; count ---------- 10000000 (1 row) Time: 7112.697 ms select count(*) FROM (select * from lotsofints order by i) t; count ---------- 10000000 (1 row) Time: 3612.099 ms select count(*) FROM (select * from lotsofints order by i) t; count ---------- 10000000 (1 row) Time: 3609.743 ms select count(*) FROM (select * from lotsofints order by i) t; count ---------- 10000000 (1 row) Time: 3600.295 ms select count(*) FROM (select * from lotsoftext order by i) t; count ---------- 10000000 (1 row) Time: 389207.326 ms select count(*) FROM (select * from lotsoftext order by i) t; count ---------- 10000000 (1 row) Time: 381650.613 ms select count(*) FROM (select * from lotsoftext order by i) t; count ---------- 10000000 (1 row) Time: 380719.257 ms select count(*) FROM (select * from lotsoftext order by i) t; count ---------- 10000000 (1 row) Time: 381596.064 ms select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t; count ---------- 10000000 (1 row) Time: 411684.602 ms select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t; count ---------- 10000000 (1 row) Time: 420361.636 ms select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t; count ---------- 10000000 (1 row) Time: 411789.096 ms select count(*) FROM (select * from lotsofitext order by i, j, w, z, z2) t; count ---------- 10000000 (1 row) Time: 408423.522 ms select count(*) FROM (select * from lotsofitext order by i, j) t; count ---------- 10000000 (1 row) Time: 379843.094 ms select count(*) FROM (select * from lotsofitext order by i, j) t; count ---------- 10000000 (1 row) Time: 379735.028 ms select count(*) FROM (select * from lotsofitext order by i, j) t; count ---------- 10000000 (1 row) Time: 380954.891 ms select count(*) FROM (select * from lotsofitext order by i, j) t; count ---------- 10000000 (1 row) Time: 379588.370 ms select count(*) FROM (select * from lotsofitext order by z, z2) t; count ---------- 10000000 (1 row) Time: 5960.867 ms select count(*) FROM (select * from lotsofitext order by z, z2) t; count ---------- 10000000 (1 row) Time: 5992.984 ms select count(*) FROM (select * from lotsofitext order by z, z2) t; count ---------- 10000000 (1 row) Time: 6003.639 ms select count(*) FROM (select * from lotsofitext order by z, z2) t; count ---------- 10000000 (1 row) Time: 5944.483 ms select count(*) FROM (select * from lotsofitext order by i, z) t; count ---------- 10000000 (1 row) Time: 376989.061 ms select count(*) FROM (select * from lotsofitext order by i, z) t; count ---------- 10000000 (1 row) Time: 379526.896 ms select count(*) FROM (select * from lotsofitext order by i, z) t; count ---------- 10000000 (1 row) Time: 377535.857 ms select count(*) FROM (select * from lotsofitext order by i, z) t; count ---------- 10000000 (1 row) Time: 377670.052 ms select count(*) FROM lotsofints; count ---------- 10000000 (1 row) Time: 5005.016 ms CREATE INDEX ix_lotsofints ON lotsofints (i); CREATE INDEX Time: 5116.295 ms DROP INDEX ix_lotsofints; DROP INDEX Time: 63.714 ms CREATE INDEX ix_lotsofints ON lotsofints (i); CREATE INDEX Time: 4934.244 ms DROP INDEX ix_lotsofints; DROP INDEX Time: 24.990 ms CREATE INDEX ix_lotsofints ON lotsofints (i); CREATE INDEX Time: 4936.585 ms DROP INDEX ix_lotsofints; DROP INDEX Time: 24.249 ms CREATE INDEX ix_lotsofints ON lotsofints (i); CREATE INDEX Time: 4946.573 ms DROP INDEX ix_lotsofints; DROP INDEX Time: 24.129 ms select count(*) FROM lotsoftext; count ---------- 10000000 (1 row) Time: 10369.699 ms CREATE INDEX ix_lotsoftext ON lotsoftext (i); CREATE INDEX Time: 382419.466 ms DROP INDEX ix_lotsoftext; DROP INDEX Time: 1043.350 ms CREATE INDEX ix_lotsoftext ON lotsoftext (i); CREATE INDEX Time: 378960.968 ms DROP INDEX ix_lotsoftext; DROP INDEX Time: 1354.450 ms CREATE INDEX ix_lotsoftext ON lotsoftext (i); CREATE INDEX Time: 380203.927 ms DROP INDEX ix_lotsoftext; DROP INDEX Time: 1360.740 ms CREATE INDEX ix_lotsoftext ON lotsoftext (i); CREATE INDEX Time: 377964.132 ms DROP INDEX ix_lotsoftext; DROP INDEX Time: 1373.526 ms select count(*) FROM lotsofitext; count ---------- 10000000 (1 row) Time: 645.163 ms CREATE INDEX ix_lotsofitext ON lotsofitext (i); CREATE INDEX Time: 379681.681 ms DROP INDEX ix_lotsofitext; DROP INDEX Time: 1363.917 ms CREATE INDEX ix_lotsofitext ON lotsofitext (i); CREATE INDEX Time: 377449.098 ms DROP INDEX ix_lotsofitext; DROP INDEX Time: 1102.904 ms CREATE INDEX ix_lotsofitext ON lotsofitext (i); CREATE INDEX Time: 377452.323 ms DROP INDEX ix_lotsofitext; DROP INDEX Time: 1287.376 ms CREATE INDEX ix_lotsofitext ON lotsofitext (i); CREATE INDEX Time: 379316.211 ms DROP INDEX ix_lotsofitext; DROP INDEX Time: 1381.142 ms CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2); CREATE INDEX Time: 451535.043 ms DROP INDEX ix_lotsofitext_ijwzz2; DROP INDEX Time: 1369.050 ms CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2); CREATE INDEX Time: 460042.952 ms DROP INDEX ix_lotsofitext_ijwzz2; DROP INDEX Time: 187.911 ms CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2); CREATE INDEX Time: 456818.044 ms DROP INDEX ix_lotsofitext_ijwzz2; DROP INDEX Time: 387.050 ms CREATE INDEX ix_lotsofitext_ijwzz2 ON lotsofitext (i, j, w, z, z2); CREATE INDEX Time: 466443.909 ms DROP INDEX ix_lotsofitext_ijwzz2; DROP INDEX Time: 192.113 ms CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w); CREATE INDEX Time: 109526.864 ms DROP INDEX ix_lotsofitext_zz2ijw; DROP INDEX Time: 194.895 ms CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w); CREATE INDEX Time: 107865.027 ms DROP INDEX ix_lotsofitext_zz2ijw; DROP INDEX Time: 190.618 ms CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w); CREATE INDEX Time: 107063.798 ms DROP INDEX ix_lotsofitext_zz2ijw; DROP INDEX Time: 534.742 ms CREATE INDEX ix_lotsofitext_zz2ijw ON lotsofitext (z, z2, i, j, w); CREATE INDEX Time: 108540.941 ms DROP INDEX ix_lotsofitext_zz2ijw; DROP INDEX Time: 239.533 ms CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2); CREATE INDEX Time: 31129.313 ms DROP INDEX ix_lotsofitext_zz2; DROP INDEX Time: 31.877 ms CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2); CREATE INDEX Time: 5977.397 ms DROP INDEX ix_lotsofitext_zz2; DROP INDEX Time: 30.011 ms CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2); CREATE INDEX Time: 5965.895 ms DROP INDEX ix_lotsofitext_zz2; DROP INDEX Time: 30.266 ms CREATE INDEX ix_lotsofitext_zz2 ON lotsofitext (z, z2); CREATE INDEX Time: 5929.517 ms DROP INDEX ix_lotsofitext_zz2; DROP INDEX Time: 96.393 ms