Following up with some additional information.
The machine has 1Gb physical RAM. When I run the query (with sort and
seqscan enabled), top reports (numbers are fairly consistent):
Mem: 1,032,972k total, 1,019,516k used, 13,412k free, 17,132k buffers
Swap: 2,032,140k total, 17,592k used, 2,014,548k free, 742,636k cached
The postmaster process is using 34.7% of RAM - 359m virt, 349 res, 319m.
No other process is using more than 2% of the memory.
From vmstat:
r b swpd free buff cache
1 0 17592 13568 17056 743676
vmstat also shows no swapping going on.
Note that I have part of the database, for just Colorado, on my Windows
XP laptop (table size for completechain table in this case is 1Gb versus
18Gb for the whole US) for development purposes. I see the same
behavior on it, which is a Dell D6100 laptop with 1Gb, running 8.1, and
a default postgres.conf file with three changes (shared_buffers set to
7000, and work_mem set to 8192, effective_cache_size 2500).
Out of curiosity, how much longer would an index_scan expected to be
versus a seq scan? I was under the impression it would be about a facto
of 4, or is that not usually the case?
Thanks for the help,
Charlie
