Re: Retaining execution plans between connections?

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

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
>

On Fri, 2006-01-20 at 18:14 +0900, James Russell wrote:
> 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.

Note that you can also take advantage of plan caching by using prepared
statements (PREPARE, EXECUTE and DEALLOCATE). These are also session
local, however (i.e. you can't share prepared statements between
connections).

> 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).

You can't share plans among different sessions at the moment. Can you
elaborate on why you can't use persistent or pooled database
connections?

-Neil