/*
drop table if exists stock100;
create table stock100
(
  s_i_id        integer
, s_w_id        smallint
, s_quantity    smallint
, s_order_cnt   smallint            -- not listed as a monetary value
, s_remote_cnt  smallint            -- not listed as a monetary value
, s_ytd         integer             -- not listed as a monetary value
, s_dist_01     char(24)
, s_dist_02     char(24)
, s_dist_03     char(24)
, s_dist_04     char(24)
, s_dist_05     char(24)
, s_dist_06     char(24)
, s_dist_07     char(24)
, s_dist_08     char(24)
, s_dist_09     char(24)
, s_dist_10     char(24)
, s_data        text                -- varchar(50)
);


drop table if exists testresult;
CREATE TABLE testresult (
  description text NOT NULL,
  begints timestamp DEFAULT (now()) NOT NULL,
  endts timestamp);
*/
---
/*
TRUNCATE stock100; CHECKPOINT;
SET scan_recycle_buffers = 1;
INSERT INTO testresult (description) VALUES ('copy-1');
COPY stock100 FROM '/mnt/data/dbt2/test-w100/dbdata/stock.data';
UPDATE testresult SET endts = now() WHERE endts IS NULL;

---

TRUNCATE stock100; CHECKPOINT;
SET scan_recycle_buffers = 2;
INSERT INTO testresult (description) VALUES ('copy-2');
COPY stock100 FROM '/mnt/data/dbt2/test-w100/dbdata/stock.data';
UPDATE testresult SET endts = now() WHERE endts IS NULL;

---

TRUNCATE stock100; CHECKPOINT;
SET scan_recycle_buffers = 4;
INSERT INTO testresult (description) VALUES ('copy-4');
COPY stock100 FROM '/mnt/data/dbt2/test-w100/dbdata/stock.data';
UPDATE testresult SET endts = now() WHERE endts IS NULL;

---

TRUNCATE stock100; CHECKPOINT;
SET scan_recycle_buffers = 8;
INSERT INTO testresult (description) VALUES ('copy-8');
COPY stock100 FROM '/mnt/data/dbt2/test-w100/dbdata/stock.data';
UPDATE testresult SET endts = now() WHERE endts IS NULL;

---

TRUNCATE stock100; CHECKPOINT;
SET scan_recycle_buffers = 16;
INSERT INTO testresult (description) VALUES ('copy-16');
COPY stock100 FROM '/mnt/data/dbt2/test-w100/dbdata/stock.data';
UPDATE testresult SET endts = now() WHERE endts IS NULL;

----
TRUNCATE stock100; CHECKPOINT;
SET scan_recycle_buffers = 32;
INSERT INTO testresult (description) VALUES ('copy-32');
COPY stock100 FROM '/mnt/data/dbt2/test-w100/dbdata/stock.data';
UPDATE testresult SET endts = now() WHERE endts IS NULL;

----
TRUNCATE stock100; CHECKPOINT;
SET scan_recycle_buffers = 64;
INSERT INTO testresult (description) VALUES ('copy-64');
COPY stock100 FROM '/mnt/data/dbt2/test-w100/dbdata/stock.data';
UPDATE testresult SET endts = now() WHERE endts IS NULL;

----
TRUNCATE stock100; CHECKPOINT;
SET scan_recycle_buffers = 128;
INSERT INTO testresult (description) VALUES ('copy-128');
COPY stock100 FROM '/mnt/data/dbt2/test-w100/dbdata/stock.data';
UPDATE testresult SET endts = now() WHERE endts IS NULL;

----
TRUNCATE stock100; CHECKPOINT;
SET scan_recycle_buffers = 256;
INSERT INTO testresult (description) VALUES ('copy-256');
COPY stock100 FROM '/mnt/data/dbt2/test-w100/dbdata/stock.data';
UPDATE testresult SET endts = now() WHERE endts IS NULL;

----
TRUNCATE stock100; CHECKPOINT;
SET scan_recycle_buffers = 512;
INSERT INTO testresult (description) VALUES ('copy-512');
COPY stock100 FROM '/mnt/data/dbt2/test-w100/dbdata/stock.data';
UPDATE testresult SET endts = now() WHERE endts IS NULL;
----
TRUNCATE stock100; CHECKPOINT;
SET scan_recycle_buffers = 1024;
INSERT INTO testresult (description) VALUES ('copy-1024');
COPY stock100 FROM '/mnt/data/dbt2/test-w100/dbdata/stock.data';
UPDATE testresult SET endts = now() WHERE endts IS NULL;
*/

----
/*
SELECT COUNT(*) FROM stock100; -- set hint bits

CHECKPOINT;
SET scan_recycle_buffers = 4;
INSERT INTO testresult (description) VALUES ('select-4');
SELECT COUNT(*) FROM stock100;
UPDATE testresult SET endts = now() WHERE endts IS NULL;


CHECKPOINT;
SET scan_recycle_buffers = 4;
INSERT INTO testresult (description) VALUES ('select-4');
SELECT COUNT(*) FROM stock100;
UPDATE testresult SET endts = now() WHERE endts IS NULL;


CHECKPOINT;
SET scan_recycle_buffers = 1;
INSERT INTO testresult (description) VALUES ('select-1');
SELECT COUNT(*) FROM stock100;
UPDATE testresult SET endts = now() WHERE endts IS NULL;


CHECKPOINT;
SET scan_recycle_buffers = 1;
INSERT INTO testresult (description) VALUES ('select-1');
SELECT COUNT(*) FROM stock100;
UPDATE testresult SET endts = now() WHERE endts IS NULL;


CHECKPOINT;
SET scan_recycle_buffers = 1;
INSERT INTO testresult (description) VALUES ('select-1');
SELECT COUNT(*) FROM stock100;
UPDATE testresult SET endts = now() WHERE endts IS NULL;


CHECKPOINT;
SET scan_recycle_buffers = 1;
INSERT INTO testresult (description) VALUES ('select-1');
SELECT COUNT(*) FROM stock100;
UPDATE testresult SET endts = now() WHERE endts IS NULL;


CHECKPOINT;
SET scan_recycle_buffers = 1;
INSERT INTO testresult (description) VALUES ('select-1');
SELECT COUNT(*) FROM stock100;
UPDATE testresult SET endts = now() WHERE endts IS NULL;



CHECKPOINT;
SET scan_recycle_buffers = 1;
INSERT INTO testresult (description) VALUES ('select-1');
SELECT COUNT(*) FROM stock100;
UPDATE testresult SET endts = now() WHERE endts IS NULL;

----

CHECKPOINT;
SET scan_recycle_buffers = 2;
INSERT INTO testresult (description) VALUES ('select-2');
SELECT COUNT(*) FROM stock100;
UPDATE testresult SET endts = now() WHERE endts IS NULL;


CHECKPOINT;
SET scan_recycle_buffers = 2;
INSERT INTO testresult (description) VALUES ('select-2');
SELECT COUNT(*) FROM stock100;
UPDATE testresult SET endts = now() WHERE endts IS NULL;



CHECKPOINT;
SET scan_recycle_buffers = 2;
INSERT INTO testresult (description) VALUES ('select-2');
SELECT COUNT(*) FROM stock100;
UPDATE testresult SET endts = now() WHERE endts IS NULL;

----

CHECKPOINT;
SET scan_recycle_buffers = 4;
INSERT INTO testresult (description) VALUES ('select-4');
SELECT COUNT(*) FROM stock100;
UPDATE testresult SET endts = now() WHERE endts IS NULL;

----

CHECKPOINT;
SET scan_recycle_buffers = 8;
INSERT INTO testresult (description) VALUES ('select-8');
SELECT COUNT(*) FROM stock100;
UPDATE testresult SET endts = now() WHERE endts IS NULL;

----

CHECKPOINT;
SET scan_recycle_buffers = 16;
INSERT INTO testresult (description) VALUES ('select-16');
SELECT COUNT(*) FROM stock100;
UPDATE testresult SET endts = now() WHERE endts IS NULL;

----

CHECKPOINT;
SET scan_recycle_buffers = 32;
INSERT INTO testresult (description) VALUES ('select-32');
SELECT COUNT(*) FROM stock100;
UPDATE testresult SET endts = now() WHERE endts IS NULL;

----

CHECKPOINT;
SET scan_recycle_buffers = 64;
INSERT INTO testresult (description) VALUES ('select-64');
SELECT COUNT(*) FROM stock100;
UPDATE testresult SET endts = now() WHERE endts IS NULL;

----

CHECKPOINT;
SET scan_recycle_buffers = 128;
INSERT INTO testresult (description) VALUES ('select-128');
SELECT COUNT(*) FROM stock100;
UPDATE testresult SET endts = now() WHERE endts IS NULL;

----

CHECKPOINT;
SET scan_recycle_buffers = 256;
INSERT INTO testresult (description) VALUES ('select-256');
SELECT COUNT(*) FROM stock100;
UPDATE testresult SET endts = now() WHERE endts IS NULL;

----

CHECKPOINT;
SET scan_recycle_buffers = 512;
INSERT INTO testresult (description) VALUES ('select-512');
SELECT COUNT(*) FROM stock100;
UPDATE testresult SET endts = now() WHERE endts IS NULL;

----

CHECKPOINT;
SET scan_recycle_buffers = 1024;
INSERT INTO testresult (description) VALUES ('select-1024');
SELECT COUNT(*) FROM stock100;
UPDATE testresult SET endts = now() WHERE endts IS NULL;
*/
----
/*
------- VACUUM tests -------

CHECKPOINT;
SET scan_recycle_buffers = 1;
INSERT INTO testresult (description) VALUES ('vacuum-1');
VACUUM stock100;
UPDATE testresult SET endts = now() WHERE endts IS NULL;

----
CHECKPOINT;
SET scan_recycle_buffers = 2;
INSERT INTO testresult (description) VALUES ('vacuum-2');
VACUUM stock100;
UPDATE testresult SET endts = now() WHERE endts IS NULL;

----
CHECKPOINT;
SET scan_recycle_buffers = 4;
INSERT INTO testresult (description) VALUES ('vacuum-4');
VACUUM stock100;
UPDATE testresult SET endts = now() WHERE endts IS NULL;

----
CHECKPOINT;
SET scan_recycle_buffers = 8;
INSERT INTO testresult (description) VALUES ('vacuum-8');
VACUUM stock100;
UPDATE testresult SET endts = now() WHERE endts IS NULL;

----
CHECKPOINT;
SET scan_recycle_buffers = 16;
INSERT INTO testresult (description) VALUES ('vacuum-16');
VACUUM stock100;
UPDATE testresult SET endts = now() WHERE endts IS NULL;

----
CHECKPOINT;
SET scan_recycle_buffers = 32;
INSERT INTO testresult (description) VALUES ('vacuum-32');
VACUUM stock100;
UPDATE testresult SET endts = now() WHERE endts IS NULL;

----
CHECKPOINT;
SET scan_recycle_buffers = 64;
INSERT INTO testresult (description) VALUES ('vacuum-64');
VACUUM stock100;
UPDATE testresult SET endts = now() WHERE endts IS NULL;

----
CHECKPOINT;
SET scan_recycle_buffers = 128;
INSERT INTO testresult (description) VALUES ('vacuum-128');
VACUUM stock100;
UPDATE testresult SET endts = now() WHERE endts IS NULL;

----
CHECKPOINT;
SET scan_recycle_buffers = 256;
INSERT INTO testresult (description) VALUES ('vacuum-256');
VACUUM stock100;
UPDATE testresult SET endts = now() WHERE endts IS NULL;

----
CHECKPOINT;
SET scan_recycle_buffers = 512;
INSERT INTO testresult (description) VALUES ('vacuum-512');
VACUUM stock100;
UPDATE testresult SET endts = now() WHERE endts IS NULL;

----
CHECKPOINT;
SET scan_recycle_buffers = 1024;
INSERT INTO testresult (description) VALUES ('vacuum-1024');
VACUUM stock100;
UPDATE testresult SET endts = now() WHERE endts IS NULL;

----

DROP TABLE IF EXISTS stock100_copy; 
SELECT * INTO stock100_copy FROM stock100;
SET scan_recycle_buffers = 1024;
DELETE FROM stock100_copy WHERE textin(tidout(ctid)) LIKE '%,1)' ;
SET scan_recycle_buffers = 1;
CHECKPOINT;
INSERT INTO testresult (description) VALUES ('vacuum_dirty-1');
VACUUM VERBOSE stock100_copy;
UPDATE testresult SET endts = now() WHERE endts IS NULL;


----

DROP TABLE IF EXISTS stock100_copy; 
SELECT * INTO stock100_copy FROM stock100;
SET scan_recycle_buffers = 1024;
DELETE FROM stock100_copy WHERE textin(tidout(ctid)) LIKE '%,1)' ;
SET scan_recycle_buffers = 2;
CHECKPOINT;
INSERT INTO testresult (description) VALUES ('vacuum_dirty-2');
VACUUM VERBOSE stock100_copy;
UPDATE testresult SET endts = now() WHERE endts IS NULL;

----

DROP TABLE IF EXISTS stock100_copy; 
SELECT * INTO stock100_copy FROM stock100;
SET scan_recycle_buffers = 1024;
DELETE FROM stock100_copy WHERE textin(tidout(ctid)) LIKE '%,1)' ;
SET scan_recycle_buffers = 4;
CHECKPOINT;
INSERT INTO testresult (description) VALUES ('vacuum_dirty-4');
VACUUM VERBOSE stock100_copy;
UPDATE testresult SET endts = now() WHERE endts IS NULL;

----

DROP TABLE IF EXISTS stock100_copy; 
SELECT * INTO stock100_copy FROM stock100;
SET scan_recycle_buffers = 1024;
DELETE FROM stock100_copy WHERE textin(tidout(ctid)) LIKE '%,1)' ;
SET scan_recycle_buffers = 8;
CHECKPOINT;
INSERT INTO testresult (description) VALUES ('vacuum_dirty-8');
VACUUM VERBOSE stock100_copy;
UPDATE testresult SET endts = now() WHERE endts IS NULL;

----

DROP TABLE IF EXISTS stock100_copy; 
SELECT * INTO stock100_copy FROM stock100;
SET scan_recycle_buffers = 1024;
DELETE FROM stock100_copy WHERE textin(tidout(ctid)) LIKE '%,1)' ;
SET scan_recycle_buffers = 16;
CHECKPOINT;
INSERT INTO testresult (description) VALUES ('vacuum_dirty-16');
VACUUM VERBOSE stock100_copy;
UPDATE testresult SET endts = now() WHERE endts IS NULL;

----

DROP TABLE IF EXISTS stock100_copy; 
SELECT * INTO stock100_copy FROM stock100;
SET scan_recycle_buffers = 1024;
DELETE FROM stock100_copy WHERE textin(tidout(ctid)) LIKE '%,1)' ;
SET scan_recycle_buffers = 32;
CHECKPOINT;
INSERT INTO testresult (description) VALUES ('vacuum_dirty-32');
VACUUM VERBOSE stock100_copy;
UPDATE testresult SET endts = now() WHERE endts IS NULL;

----

DROP TABLE IF EXISTS stock100_copy; 
SELECT * INTO stock100_copy FROM stock100;
SET scan_recycle_buffers = 1024;
DELETE FROM stock100_copy WHERE textin(tidout(ctid)) LIKE '%,1)' ;
SET scan_recycle_buffers = 64;
CHECKPOINT;
INSERT INTO testresult (description) VALUES ('vacuum_dirty-64');
VACUUM VERBOSE stock100_copy;
UPDATE testresult SET endts = now() WHERE endts IS NULL;

----

DROP TABLE IF EXISTS stock100_copy; 
SELECT * INTO stock100_copy FROM stock100;
SET scan_recycle_buffers = 1024;
DELETE FROM stock100_copy WHERE textin(tidout(ctid)) LIKE '%,1)' ;
SET scan_recycle_buffers = 128;
CHECKPOINT;
INSERT INTO testresult (description) VALUES ('vacuum_dirty-128');
VACUUM VERBOSE stock100_copy;
UPDATE testresult SET endts = now() WHERE endts IS NULL;

----

DROP TABLE IF EXISTS stock100_copy; 
SELECT * INTO stock100_copy FROM stock100;
SET scan_recycle_buffers = 1024;
DELETE FROM stock100_copy WHERE textin(tidout(ctid)) LIKE '%,1)' ;
SET scan_recycle_buffers = 256;
CHECKPOINT;
INSERT INTO testresult (description) VALUES ('vacuum_dirty-256');
VACUUM VERBOSE stock100_copy;
UPDATE testresult SET endts = now() WHERE endts IS NULL;

----

DROP TABLE IF EXISTS stock100_copy; 
SELECT * INTO stock100_copy FROM stock100;
SET scan_recycle_buffers = 1024;
DELETE FROM stock100_copy WHERE textin(tidout(ctid)) LIKE '%,1)' ;
SET scan_recycle_buffers = 512;
CHECKPOINT;
INSERT INTO testresult (description) VALUES ('vacuum_dirty-512');
VACUUM VERBOSE stock100_copy;
UPDATE testresult SET endts = now() WHERE endts IS NULL;

----

DROP TABLE IF EXISTS stock100_copy; 
SELECT * INTO stock100_copy FROM stock100;
SET scan_recycle_buffers = 1024;
DELETE FROM stock100_copy WHERE textin(tidout(ctid)) LIKE '%,1)' ;
SET scan_recycle_buffers = 1024;
CHECKPOINT;
INSERT INTO testresult (description) VALUES ('vacuum_dirty-1024');
VACUUM VERBOSE stock100_copy;
UPDATE testresult SET endts = now() WHERE endts IS NULL;

*/

SET scan_recycle_buffers = 1024;
DROP TABLE IF EXISTS stock100_copy; 
SELECT * INTO stock100_copy FROM stock100;


SET scan_recycle_buffers = 1024;
BEGIN; DELETE FROM stock100_copy WHERE textin(tidout(ctid)) LIKE '%,1)'; ROLLBACK;
SET scan_recycle_buffers = 1;
CHECKPOINT;
INSERT INTO testresult (description) VALUES ('vacuum_hintbits-1');
VACUUM VERBOSE stock100_copy;
UPDATE testresult SET endts = now() WHERE endts IS NULL;


----

SET scan_recycle_buffers = 1024;
BEGIN; DELETE FROM stock100_copy WHERE textin(tidout(ctid)) LIKE '%,1)'; ROLLBACK;
SET scan_recycle_buffers = 2;
CHECKPOINT;
INSERT INTO testresult (description) VALUES ('vacuum_hintbits-2');
VACUUM VERBOSE stock100_copy;
UPDATE testresult SET endts = now() WHERE endts IS NULL;

----

SET scan_recycle_buffers = 1024;
BEGIN; DELETE FROM stock100_copy WHERE textin(tidout(ctid)) LIKE '%,1)'; ROLLBACK;
SET scan_recycle_buffers = 4;
CHECKPOINT;
INSERT INTO testresult (description) VALUES ('vacuum_hintbits-4');
VACUUM VERBOSE stock100_copy;
UPDATE testresult SET endts = now() WHERE endts IS NULL;

----

SET scan_recycle_buffers = 1024;
BEGIN; DELETE FROM stock100_copy WHERE textin(tidout(ctid)) LIKE '%,1)'; ROLLBACK;
SET scan_recycle_buffers = 8;
CHECKPOINT;
INSERT INTO testresult (description) VALUES ('vacuum_hintbits-8');
VACUUM VERBOSE stock100_copy;
UPDATE testresult SET endts = now() WHERE endts IS NULL;

----

SET scan_recycle_buffers = 1024;
BEGIN; DELETE FROM stock100_copy WHERE textin(tidout(ctid)) LIKE '%,1)'; ROLLBACK;
SET scan_recycle_buffers = 16;
CHECKPOINT;
INSERT INTO testresult (description) VALUES ('vacuum_hintbits-16');
VACUUM VERBOSE stock100_copy;
UPDATE testresult SET endts = now() WHERE endts IS NULL;

----

SET scan_recycle_buffers = 1024;
BEGIN; DELETE FROM stock100_copy WHERE textin(tidout(ctid)) LIKE '%,1)'; ROLLBACK;
SET scan_recycle_buffers = 32;
CHECKPOINT;
INSERT INTO testresult (description) VALUES ('vacuum_hintbits-32');
VACUUM VERBOSE stock100_copy;
UPDATE testresult SET endts = now() WHERE endts IS NULL;

----

SET scan_recycle_buffers = 1024;
BEGIN; DELETE FROM stock100_copy WHERE textin(tidout(ctid)) LIKE '%,1)'; ROLLBACK;
SET scan_recycle_buffers = 64;
CHECKPOINT;
INSERT INTO testresult (description) VALUES ('vacuum_hintbits-64');
VACUUM VERBOSE stock100_copy;
UPDATE testresult SET endts = now() WHERE endts IS NULL;

----

SET scan_recycle_buffers = 1024;
BEGIN; DELETE FROM stock100_copy WHERE textin(tidout(ctid)) LIKE '%,1)'; ROLLBACK;
SET scan_recycle_buffers = 128;
CHECKPOINT;
INSERT INTO testresult (description) VALUES ('vacuum_hintbits-128');
VACUUM VERBOSE stock100_copy;
UPDATE testresult SET endts = now() WHERE endts IS NULL;

----

SET scan_recycle_buffers = 1024;
BEGIN; DELETE FROM stock100_copy WHERE textin(tidout(ctid)) LIKE '%,1)'; ROLLBACK;
SET scan_recycle_buffers = 256;
CHECKPOINT;
INSERT INTO testresult (description) VALUES ('vacuum_hintbits-256');
VACUUM VERBOSE stock100_copy;
UPDATE testresult SET endts = now() WHERE endts IS NULL;

----

SET scan_recycle_buffers = 1024;
BEGIN; DELETE FROM stock100_copy WHERE textin(tidout(ctid)) LIKE '%,1)'; ROLLBACK;
SET scan_recycle_buffers = 512;
CHECKPOINT;
INSERT INTO testresult (description) VALUES ('vacuum_hintbits-512');
VACUUM VERBOSE stock100_copy;
UPDATE testresult SET endts = now() WHERE endts IS NULL;

----

SET scan_recycle_buffers = 1024;
BEGIN; DELETE FROM stock100_copy WHERE textin(tidout(ctid)) LIKE '%,1)'; ROLLBACK;
SET scan_recycle_buffers = 1024;
CHECKPOINT;
INSERT INTO testresult (description) VALUES ('vacuum_hintbits-1024');
VACUUM VERBOSE stock100_copy;
UPDATE testresult SET endts = now() WHERE endts IS NULL;


SELECT description, endts-begints FROM testresult;