Re: Retaining execution plans between connections?

you could use pgpool

http://pgpool.projects.postgresql.org/

On 1/20/06, James Russell <internationalhobo(at)gmail(dot)com> wrote:
> Hi there,
>
> I am running a website where each page connects to the DB to retrieve and
> write information. Each page load uses a separate connection (rather than
> just sharing one as is the common case) because I use a lot of transactions.
>
> I am looking to speed up performance, and since each page executes a static
> set of queries where only the parameters change, I was hoping to take
> advantage of stored procedures since I read that PostgreSQL's caches the
> execution plans used inside stored procedures.
>
> However, the documentation states that this execution plan caching is done
> on a per-connection basis. If each page uses a separate connection, I can
> get no performance benefit between pages.
>
> In other words, there's no benefit to me in putting a one-shot query that
> is basically the same for every page (e.g. "SELECT * FROM users WHERE
> user_name='<username>'") inside a stored proc, since the generated execution
> plan will be thrown away once the connection is dropped.
>
> Has anyone found a way around this limitation? As I said, I can't share the
> DB connection between pages (unless someone knows of a way to do this and
> still retain a level of separation between pages that use the same DB
> connection).
>
> Many thanks,
>
> James
>