Sequential scan speed, mmap, disk i/o

Someone was complaining about sequential scan speed, so I decided to run
a test.

I have run the test by performing a total read-through of a 177MB table.
This exceeds all my cache sizes by over two times, so the cache is
totally useless ("cache wipe").  I have timed PostgreSQL's sequential
scan (returning no rows), and various Unix methods of reading a 177MB
file.

I have found that a sequential scan by PostgreSQL is almost as fast or
faster than various other Unix methods of reading files.  In fact,
mmap() is very slow, perhaps because you are changing the process
virtual table maps for each chunk you read in, and faulting them in,
rather than using the file system for I/O.

Basically, we beat 'wc', which is pretty good considering how little
'wc' does.

My conclusion from this is that we really are not going to gain a lot of
speed by exploring some async solution, because if the data we need is
not in the cache, we really are going to spend most of our time waiting
for disk I/O.

Comments?

---------------------------------------------------------------------------


177MB file, BSD/OS 3.1, 64MB RAM, PostgreSQL current

wc                            41 sec
wc -l                            31 sec
dd if=/u/pg/data/base/test/testv of=/dev/null bs=512    32 sec
dd if=/u/pg/data/base/test/testv of=/dev/null bs=8k    31 sec
dd if=/u/pg/data/base/test/testv of=/dev/null bs=256k    31 sec
dd if=/u/pg/data/base/test/testv of=/dev/null bs=1m    30 sec
mmap() of file in 8k chunks                99 sec
mmap() of file in 8mb chunks                40 sec
mmap() of file in 32mb chunks                56 sec

PostgreSQL sequential scan                37 sec


---------------------------------------------------------------------------

/* mmap() test program */
#include <stdio.h>
#include <fcntl.h>
#include <assert.h>
#include <sys/types.h>
#include <sys/mman.h>

#define MMAP_SIZE 8192   /* chunk size */

int main(int argc, char *argv[], char *envp[])
{
    int i, j, fd, spaces = 0;
    int off;
    char *addr;

    fd = open("/u/pg/data/base/test/testv", O_RDONLY, 0);
    assert(fd != 0);

    for (off = 0; 1; off += MMAP_SIZE)
    {
        addr = mmap(0, MMAP_SIZE, PROT_READ, 0, fd, off);
        assert(addr != NULL);

        for (j = 0; j < MMAP_SIZE; j++)
            if (*(addr + j)    != ' ')
                spaces++;
        munmap(addr,MMAP_SIZE);
    }
    printf("%d\n",spaces);
    return 0;
}

--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)