Thread:

<div class="Section1"><p class="MsoNormal" style="text-autospace:none"><font face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"">Hi,</span></font><p class="MsoNormal"
style="text-autospace:none"><fontface="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier
New""> </span></font><pclass="MsoNormal" style="text-autospace:none"><font face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"">My name is Fabiana Prabhakar. I am a graduate student at Lehigh
University.I have been doing some research using PostgreSQL towards implementing a cache conscious hash join
algorithm.</span></font><pclass="MsoNormal" style="text-autospace:none"><font face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew""> </span></font><p class="MsoNormal" style="text-autospace:none"><font
face="CourierNew" size="2"><span style="font-size:10.0pt;font-family:"Courier New"">I noticed that version 8.3 has a
feature"L2 Cache Scan Protection" that is described in the website as "New code optimizations prevent thrashing CPU
cacheswhich slows concurrent queries".</span></font><p class="MsoNormal" style="text-autospace:none"><font
face="CourierNew" size="2"><span style="font-size:10.0pt;font-family:"Courier New""> </span></font><p class="MsoNormal"
style="text-autospace:none"><fontface="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier
New"">Itwould be really helpful for me if I could have some more information about this feature, especially if there is
anypublication that explains it in details.</span></font><p class="MsoNormal" style="text-autospace:none"><font
face="CourierNew" size="2"><span style="font-size:10.0pt;font-family:"Courier New""> </span></font><p class="MsoNormal"
style="text-autospace:none"><fontface="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier
New"">Thanksa lot in advance.</span></font><p class="MsoNormal" style="text-autospace:none"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt;font-family:"Courier New""> </span></font><p class="MsoNormal"
style="text-autospace:none"><fontface="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier
New"">Regards,</span></font><pclass="MsoNormal" style="text-autospace:none"><font face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew""> </span></font><p class="MsoNormal" style="text-autospace:none"><font
face="CourierNew" size="2"><span style="font-size:10.0pt;font-family:"Courier New"">Fabiana Prabhakar</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font></div>

"Fabiana Prabhakar" <ffp206@Lehigh.EDU> writes:

> I noticed that version 8.3 has a feature "L2 Cache Scan Protection" that is
> described in the website as "New code optimizations prevent thrashing CPU
> caches which slows concurrent queries".
>
> It would be really helpful for me if I could have some more information
> about this feature, especially if there is any publication that explains it
> in details.

It's probably not terribly interesting to you. It's basically fixing the way
PostgreSQL managed its own memory. 

Postgres manages a pool of buffers in shared memory which it uses to store
pages of the database. It uses a clock sweep algorithm to pick buffers which
haven't been used recently when it has to read in a new buffer.

Previously PostgreSQL was using its normal algorithm for all reads. This
optimization made it use a different strategy when it's doing a sequential
scan of a large table. Instead it picks a small number (32 iirc) of buffers
and reuse them over and over again. If a buffer isn't found in the cache it
uses the next buffer from its small pool.

This was measurably faster, possibly due to improved L2 cache efficiency, and
also should spoil the cache less for other backends.

You could look at these two threads for some more information:

http://archives.postgresql.org/pgsql-hackers/2007-05/msg00115.php
http://archives.postgresql.org/pgsql-hackers/2007-05/msg00361.php

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support!