Re: Seq scans roadmap

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