Re: Seq scans roadmap - Mailing list pgsql-hackers

I wrote:
> I'll review my test methodology and keep testing...

I ran a set of tests on a 100 warehouse TPC-C stock table that is ~3.2
GB in size and the server has 4 GB of memory. IOW the table fits in OS
cache, but not in shared_buffers (set at 1 GB).

copy - COPY from a file
select - SELECT COUNT(*) FROM stock
vacuum - VACUUM on a clean table, effectively a read-only operation
vacuum_hintbits - VACUUM on a table with no dead tuples, but hint bits
need to be set on every page
vacuum_dirty - VACUUM with exactly 1 dead tuple per page,

The number after the test name is the ring size used.

There was no indexes on the table, which means that the vacuum tests
only had to do one pass. The 1st vacuum phase of a real-world table is
like a mixture of vacuum- and vacuum_hintbits-tests, and 2nd phase is
like the vacuum_dirty test.

  copy-1            | 00:31:47.042365
  copy-2            | 00:17:57.630772
  copy-4            | 00:17:55.041794
  copy-8            | 00:08:31.014009
  copy-16           | 00:05:38.39848
  copy-32           | 00:05:52.295512
  copy-64           | 00:06:08.404646
  copy-128          | 00:05:05.032448
  copy-256          | 00:05:48.573146
  copy-512          | 00:04:56.098752
  copy-1024         | 00:05:27.05316
  select-4          | 00:00:04.344873
  select-4          | 00:00:02.2498
  select-1          | 00:00:08.754011
  select-1          | 00:00:10.521174
  select-1          | 00:00:10.819376
  select-1          | 00:00:14.818831
  select-1          | 00:00:14.893562
  select-1          | 00:00:16.973934
  select-2          | 00:00:15.722776
  select-2          | 00:00:02.291078
  select-2          | 00:00:02.230167
  select-4          | 00:00:02.232935
  select-8          | 00:00:02.238791
  select-16         | 00:00:02.245566
  select-32         | 00:00:02.267158
  select-64         | 00:00:02.311878
  select-128        | 00:00:02.487086
  select-256        | 00:00:02.764085
  select-512        | 00:00:03.161025
  select-1024       | 00:00:03.387246
  vacuum-1          | 00:00:01.843337
  vacuum-2          | 00:00:01.612738
  vacuum-4          | 00:00:01.6304
  vacuum-8          | 00:00:01.655126
  vacuum-16         | 00:00:01.641808
  vacuum-32         | 00:00:01.664108
  vacuum-64         | 00:00:01.729106
  vacuum-128        | 00:00:01.879023
  vacuum-256        | 00:00:02.218303
  vacuum-512        | 00:00:02.569571
  vacuum-1024       | 00:00:02.791995
  vacuum_dirty-1    | 00:24:15.424337
  vacuum_dirty-2    | 00:13:26.981835
  vacuum_dirty-4    | 00:08:07.260113
  vacuum_dirty-8    | 00:05:24.1476
  vacuum_dirty-16   | 00:03:52.690336
  vacuum_dirty-32   | 00:02:40.759203
  vacuum_dirty-64   | 00:02:45.14425
  vacuum_dirty-128  | 00:02:46.718922
  vacuum_dirty-256  | 00:02:43.797785
  vacuum_dirty-512  | 00:02:36.363763
  vacuum_dirty-1024 | 00:02:32.767481
  vacuum_hintbits-1    | 00:00:37.847935
  vacuum_hintbits-2    | 00:00:38.788662
  vacuum_hintbits-4    | 00:00:43.554029
  vacuum_hintbits-8    | 00:00:42.040379
  vacuum_hintbits-16   | 00:00:44.187508
  vacuum_hintbits-32   | 00:00:38.252052
  vacuum_hintbits-64   | 00:00:37.920379
  vacuum_hintbits-128  | 00:00:38.463007
  vacuum_hintbits-256  | 00:00:38.157724
  vacuum_hintbits-512  | 00:00:38.309285
  vacuum_hintbits-1024 | 00:00:39.178738

I ran the some of the select tests multiple times because the behavior
changed when the test was repeated. I don't know what's going on in the
select-1 test, it looks like the same effect I had with the more complex
query involving a LIMIT-node, but this time I'm just doing a plain
SELECT COUNT(*). I ran the test script multiple times; the results shown
above are copy-pasted from one particular run but the numbers didn't
change much from run to run. In particular, the run times for the
select-1 test really do increase as you repeat the test many times. The
copy results seem to vary quite a bit, though.

For comparison, here's the test results with vanilla CVS HEAD:

  copy-head         | 00:06:21.533137
  copy-head         | 00:05:54.141285
  select-head       | 00:00:16.213693
  select-head       | 00:00:18.500792
  vacuum-head       | 00:00:12.843479
  vacuum-head       | 00:00:08.719845
  vacuum_dirty-head | 00:22:02.533553
  vacuum_dirty-head | 00:22:02.852786
  vacuum_hintbits-head | 00:00:38.278701
  vacuum_hintbits-head | 00:00:35.226191

Looking at the results, it seems that using a fixed sized ring of 32
pages hits the sweet spot on all tests. I wonder if that holds on other
hardware.

The test scripts I used are attached. I used a modified DBT-2 schema and
dump file, so you'll need to replace that with some other large table to
run it. I would appreciate it if others would repeat the tests on other
hardware to get a bigger sample.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com
/*
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;/*
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;
INSERT INTO testresult (description) VALUES ('copy-head');
COPY stock100 FROM '/mnt/data/dbt2/test-w100/dbdata/stock.data';
UPDATE testresult SET endts = now() WHERE endts IS NULL;

---

TRUNCATE stock100; CHECKPOINT;
INSERT INTO testresult (description) VALUES ('copy-head');
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;
INSERT INTO testresult (description) VALUES ('select-head');
SELECT COUNT(*) FROM stock100;
UPDATE testresult SET endts = now() WHERE endts IS NULL;

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

------- VACUUM tests -------

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


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

----

DROP TABLE IF EXISTS stock100_copy;
SELECT * INTO stock100_copy FROM stock100;
DELETE FROM stock100_copy WHERE textin(tidout(ctid)) LIKE '%,1)' ;
CHECKPOINT;
INSERT INTO testresult (description) VALUES ('vacuum_dirty-head');
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;
DELETE FROM stock100_copy WHERE textin(tidout(ctid)) LIKE '%,1)' ;
CHECKPOINT;
INSERT INTO testresult (description) VALUES ('vacuum_dirty-head');
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;

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


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


SELECT description, endts-begints FROM testresult;