pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: James Russell <internationalhobo@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Retaining execution plans between connections?
Date: Fri, 20 Jan 2006 18:14:15 +0900
Message-ID: <3a3a16930601200114p27ccb3fdn750286898f39c405@mail.gmail.com> (raw)

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

view thread (3+ messages)  latest in thread

Message-ID: <3a3a16930601200114p27ccb3fdn750286898f39c405@mail.gmail.com>
Permalink:  ../3a3a16930601200114p27ccb3fdn750286898f39c405@mail.gmail.com/
Also on:    postgresql.org/message-id/3a3a16930601200114p27ccb3fdn750286898f39c405@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: internationalhobo@gmail.com
  Subject: Re: Retaining execution plans between connections?
  In-Reply-To: <3a3a16930601200114p27ccb3fdn750286898f39c405@mail.gmail.com>
  References: <3a3a16930601200114p27ccb3fdn750286898f39c405@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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