This is all logical, all sequential access will be faster with bigger
blocksize. It usually gets faster up to a blocksize of 256k.
You could have done the same test using dd.
The slowdown will be on random access using index and too much data
to cache all data pages and needed index pages.
1. The cache will not be as selective, since for one often needed
row the backend will cache 32k (plus at least 32k for each index
needed to find this row).
2. The index lookup will use more CPU time, since one index page
is larger.
3. To cache one row with 3 indices you will need at least 128k memory
compared to 32 k with an 8k pagesize.
4. It will also increase the amount of disk space and cache needed
for the system tables (since they are all rather small)
For the default configuration a 4-8k page size seems to be a good
tradeoff in other DBMS's. For PostgreSQL this might not be so,
because of the lack of read ahead and write behind.
Remember that the read ahead would actually need to read in bigger
blocks (up to 256k) to actually perform best. The same is true for
the write behind.
Andreas
> Tatsuo Ishii wrote:
> >>
> >> But modern Unixes have read/write ahead i/o if it seems a sequential
> >> access, don't they. I did some testing on my LinuxPPC box.
> >>
> >> 0. create table t2(i int,c char(4000));
> >> 1. time psql -c "copy t2 from '/tmp/aaa'" test
> >> (aaa has 5120 records and this will create 20MB table)
> >> 2. time psql -c "select count(*) from t2" test
> >> 3. total time of the regression test
> >>
> >> o result of testing 1
> >>
> >> 8K: 0.02user 0.04system 3:26.20elapsed
> >> 32K: 0.03user 0.06system 0:48.25elapsed
> >>
> >> 32K is 4 times faster than 8k!
> >>
> >> o result of testing 2
> >>
> >> 8K: 0.02user 0.04system 6:00.31elapsed
> >> 32K: 0.04user 0.02system 1:02.13elapsed
> >>
> >> 32K is neary 6 times faster than 8k!
> >
> >Did you use the same -B for 8K and 32K ?
> >You should use 4x buffers in 8K case!
>
> Ok. This time I started postmaster as 'postmaster -S -i -B 256'.
>
> test1:
> 0.03user 0.02system 3:21.65elapsed
>
> test2:
> 0.01user 0.08system 5:30.94elapsed
>
> a little bit faster, but no significant difference?
>
>
>