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

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.634105
 select-2    | 00:00:02.674084
 select-4    | 00:00:02.65664
 select-8    | 00:00:02.662922
 select-16   | 00:00:02.682475
 select-32   | 00:00:02.693163
 select-64   | 00:00:02.722031
 select-128  | 00:00:02.873645
 select-256  | 00:00:03.185586
 select-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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
      choose an index scan if your joining column's datatypes do not

Reply via email to