prepared transactions that persist across sessions? - Mailing list pgsql-performance

Hey all.

Please point me to a place I should be looking if this is a common
question that has been debated periodically and at great length
already. :-)

I have a complex query. It's a few Kbytes large, and yes, I've already
worked on reducing it to be efficient in terms of database design, and
minimizing the expressions used to join the tables. Running some timing
tests, I've finding that the query itself, when issued in full, takes
around 60 milliseconds to complete on modest hardware. If prepared, and
then executed, however, it appears to take around 60 milliseconds to
prepare, and 20 milliseconds to execute. I'm not surprised. PostgreSQL
is very likely calculating the costs of many, many query plans.

This is telling me that the quickest method of me to accelerate these
queries, is to have them pre-select a query plan, and to use it.
Unfortunately, I'll only be executing this query once per session,
so "PREPARE" seems to be out of the question.

I am using PHP's PDO PGSQL interface - I haven't read up enough on it
to determine whether a persistent connection can re-use server-side
prepared queries as an option. Anybody know?

My read of the PLPGSQL documentation seems to suggest that it will do
some sort of query plan caching. Is there better documentation on this
that would explain exactly how it works? What is the best way to define
a PLPGSQL function that will return a set of records? Is RETURNS SETOF
the only option in this regard? It seems inefficient to me. Am I doing
it wrong? Not understanding it? For very simple queries, it seems that
using PLPGSQL and SELECT INTO, RETURN, and then SELECT * FROM F(arg)"
actually slows down the query slightly. It wasn't giving me much faith,
and I wanted to pick up some people's opinions befor egoing further.

What is the reason that SQL and/or PostgreSQL have not added
server-defined prepared statements? As in, one defines a
server-defined prepared statement, and all sessions that have
permission can execute the prepared statement. Is this just an issue
of nobody implementing it? Or was there some deeper explanation as
to why this would be a bad thing?

My reading of views, are that views would not accelerate the queries.
Perhaps the bytes sent to the server would reduce, however, the cost
to prepare, and execute the statement would be similar, or possibly
even longer?

I'm thinking I need some way of defined a server side query, that
takes arguments, that will infrequently prepare the query, such that
the majority of the time that it is executed, it will not have to
choose a query plan.

Am I missing something obvious? :-)

Thanks,
mark

--
mark@mielke.cc / markm@ncf.ca / markm@nortel.com     __________________________
.  .  _  ._  . .   .__    .  . ._. .__ .   . . .__  | Neighbourhood Coder
|\/| |_| |_| |/    |_     |\/|  |  |_  |   |/  |_   |
|  | | | | \ | \   |__ .  |  | .|. |__ |__ | \ |__  | Ottawa, Ontario, Canada

  One ring to rule them all, one ring to find them, one ring to bring them all
                       and in the darkness bind them...

                           http://mark.mielke.cc/