Re: Seq scans roadmap

Heikki Linnakangas wrote:
> But all these assumptions need to be validated. I'm setting up tests 
> with different ring sizes and queries to get a clear picture of this:
> - VACUUM on a clean table
> - VACUUM on a table with 1 dead tuple per page
> - read-only scan, large table
> - read-only scan, table fits in OS cache
> - COPY

Just to keep you guys informed, here's my results on a read-only scan on 
a table bigger than shared_buffers but smaller than RAM:
 select-1    | 00:00:10.853831 select-1    | 00:00:10.380667 select-1    | 00:00:11.530528 select-2    |
00:00:08.634105select-2    | 00:00:02.674084 select-4    | 00:00:02.65664 select-8    | 00:00:02.662922 select-16   |
00:00:02.682475select-32   | 00:00:02.693163 select-64   | 00:00:02.722031 select-128  | 00:00:02.873645 select-256  |
00:00:03.185586select-512  | 00:00:03.534285 select-1024 | 00:00:03.741867
 

lshw utility tells me that this server has 32KB of L1 cache and 4MB of 
L2 cache. The performance starts to drop between 64-128 buffers, which 
is 512 - 1024 KB, so I'm not sure how it's related to cache size but 
using a small number of buffers is clearly better than using a large number.

However, it caught me by total surprise that the performance with 1 
buffer is so horrible. Using 2 buffers is enough to avoid whatever the 
issue is with just 1 buffer. I have no idea what's causing that. There 
must be some interaction that I don't understand.

All the numbers are quite repeatable, I ran the same test script many 
times. The runtime of the first select-2 test however varied between 
3-10 seconds, somehow the bad karma from using just 1 buffer in the 
earlier test carries over to the next test.

I'm not sure what to think about this, but I'll set up more test 
scenarios with VACUUM and COPY.

--   Heikki Linnakangas  EnterpriseDB   http://www.enterprisedb.com