Re: Caching of Queries

"Jason Coene" <jcoene(at)gotfrag(dot)com> writes:

> All of our "postgres" processes end up in the "semwai" state - seemingly
> waiting on other queries to complete. If the system isn't taxed in CPU or
> disk, I have a good feeling that this may be the cause.

Well, it's possible contention of some sort is an issue but it's not clear
that it's planning related contention.

> We're running on SELECT's, and the number of locks on our "high traffic"
> tables grows to the hundreds.

Where are you seeing this? What information do you have about these locks?

> I've looked at PREPARE, but apparently it only lasts per-session - that's
> worthless in our case (web based service, one connection per data-requiring
> connection).

Well the connection time in postgres is pretty quick. But a lot of other
things, including prepared queries but also including other factors are a lot
more effective if you have long-lived sessions.

I would strongly recommend you consider some sort of persistent database
connection for your application. Most web based services run queries from a
single source base where all the queries are written in-house. In that
situation you can ensure that one request never leaves the session in an
unusual state (like setting guc variables strangely, or leaving a transaction
open, or whatever).

That saves you the reconnect time, which as I said is actually small, but
could still be contributing to your problem. I think it also makes the buffer
cache more effective as well. And It also means you can prepare all your
queries and reuse them on subsequent requests.

The nice thing about web based services is that while each page only executes
each query once, you tend to get the same pages over and over thousands of
times. So if they prepare their queries the first time around they can reuse
those prepared queries thousands of times.

Using a text cache of the query string on the server side is just a
work-around for failing to do that on the client side. It's much more
efficient and more flexible to do it on the client-side.

--
greg