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
