From: PFC <lists@peufeu.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Preparing statements on connection startup
Date: Mon, 24 Mar 2008 12:23:29 +0100
Message-ID: <op.t8ipxfekcigqcu@apollo13.peufeu.com> (raw)
Hello,
I am using Postgres with PHP and persistent connections.
For simple queries, parsing & preparing time is often longer than actual
query execution time...
I would like to execute a bunch of PREPARE statements to prepare my most
often used small queries on connection startup, then reuse these prepared
statements during all the life of the persistent connection.
(Simple queries in PG are actually faster than in MySQL if prepared, lol)
How do I achieve this ?
Best way, would be of course a "PERSISTENT PREPARE" which would record
the information (name, SQL, params, not the Plan) about the prepared
statement in a system catalog shared by all connections ; when issuing
EXECUTE, if the prepared statement does not exist in the current
connection, pg would look there, and if it finds the name of the statement
and corresponding SQL, issue a PREPARE so the current connection would
then have this statement in its store, and be able to execute it faster
for all the times this connection is reused.
Is such a feature planned someday ?
I tried to write a function which is called by my PHP script just after
establishing the connection, it is a simple function which looks in
pg_prepared_statements, if it is empty it issues the PREPARE statements I
need. It works, no problem, but it is less elegant and needs one extra
query per page.
I also tried to issue a dummy EXECUTE of a prepared "SELECT 1" just after
establishing the connection : if it fails, we prepare the plans (by
issuing queries from PHP), if it succeeds, this means we are reusing a
connection with all the plans already prepared. This also works well.
What do you think ?
Regards,
Pierre
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@peufeu.com
Subject: Re: Preparing statements on connection startup
In-Reply-To: <op.t8ipxfekcigqcu@apollo13.peufeu.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