pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRetaining execution plans between connections?
3+ messages / 3 participants
[nested] [flat]
* Retaining execution plans between connections?
@ 2006-01-20 09:14 James Russell <internationalhobo@gmail.com>
2006-01-20 09:35 ` Re: Retaining execution plans between connections? Pandurangan R S <pandurangan.r.s@gmail.com>
2006-01-20 15:14 ` Re: Retaining execution plans between connections? Neil Conway <neilc@samurai.com>
0 siblings, 2 replies; 3+ messages in thread
From: James Russell @ 2006-01-20 09:14 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Retaining execution plans between connections?
2006-01-20 09:14 Retaining execution plans between connections? James Russell <internationalhobo@gmail.com>
@ 2006-01-20 09:35 ` Pandurangan R S <pandurangan.r.s@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Pandurangan R S @ 2006-01-20 09:35 UTC (permalink / raw)
To: James Russell <internationalhobo@gmail.com>; +Cc: pgsql-performance
you could use pgpool
http://pgpool.projects.postgresql.org/
On 1/20/06, James Russell <internationalhobo@gmail.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
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Retaining execution plans between connections?
2006-01-20 09:14 Retaining execution plans between connections? James Russell <internationalhobo@gmail.com>
@ 2006-01-20 15:14 ` Neil Conway <neilc@samurai.com>
1 sibling, 0 replies; 3+ messages in thread
From: Neil Conway @ 2006-01-20 15:14 UTC (permalink / raw)
To: James Russell <internationalhobo@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-01-20 15:14 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-01-20 09:14 Retaining execution plans between connections? James Russell <internationalhobo@gmail.com>
2006-01-20 09:35 ` Pandurangan R S <pandurangan.r.s@gmail.com>
2006-01-20 15:14 ` Neil Conway <neilc@samurai.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox