On Mon, Dec 29, 2008 at 8:36 AM, Scott Ribe <scott_ribe@killerbytes.com> wrote:
> Creating the partial index reads rows, and the pages are left in the disk
> cache. The only way to do proper comparisons is to reboot between trials in
> order to compare queries with cold caches, or use the latter of multiple
> runs in order to compare queries with hot caches.
There are two other ways, one is to unmount and remount the partition
on which pgsql is running. On many db servers this is possible
because pgsql gets its own disk array / mount point. The other is to
use drop caches:
smarlowe@abasin:/home/smarlowe$ cd /proc/sys/vm
smarlowe@abasin:/proc/sys/vm$ free
total used free shared buffers cached
Mem: 4016300 2257688 1758612 0 152060 1106400
-/+ buffers/cache: 999228 3017072
Swap: 1068280 45712 1022568
smarlowe@abasin:/proc/sys/vm$ echo 1|sudo tee drop_caches
1
smarlowe@abasin:/proc/sys/vm$ free
total used free shared buffers cached
Mem: 4016300 1046788 2969512 0 188 94628
-/+ buffers/cache: 951972 3064328
Swap: 1068280 45712 1022568
voila! cache dumped.