Re: Caching of Queries

"Jason Coene" <jcoene(at)gotfrag(dot)com> writes:
> I'm not sure how I go about getting the stack traceback you need. Any info
> on this? Results of "ps" below. System is dual xeon 2.6, 2gb ram, hardware
> raid 10 running FreeBSD 5.2.1.

Hmm. Dual Xeon sets off alarm bells ...

I think you are probably looking at the same problem previously reported
by Josh Berkus among others. Does the rate of context swaps shown by
vmstat go through the roof when this happens? If you strace or ktrace
one of the backends, do you see lots of semop()s and little else?

Check the archives for this thread among others:
http://archives.postgresql.org/pgsql-performance/2004-04/msg00249.php
The test case you are talking about is a tight indexscan loop, which
is pretty much the same scenario as here:
http://archives.postgresql.org/pgsql-performance/2004-04/msg00280.php

The fundamental problem is heavy contention for access to a shared data
structure. We're still looking for good solutions, but in the context
of this thread it's worth pointing out that a shared query-plan cache
would itself be subject to heavy contention, and arguably would make
this sort of problem worse not better.

regards, tom lane