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
