Re: PERSISTANT PREPARE (another point of view)

Milan Oparnica wrote:
> I found this link from IBM DB2 developers showing why PERSISTENT PREPARE 
> is a good idea and how could it be implemented.

[snip]

> NONE OF POPULAR SQL DBMS (Oracle, MS SQL, MySQL, Postgre, INTERBASE, 
> FIREBIRD) HAVE THIS FEATURE.
> 
> WHY ?

I suspect that people tend to use SQL or PL/PgSQL stored procedures 
instead. I'm not 100% sure SQL functions cache their query plans, but I 
know PL/PgSQL does.

Exactly what is gained by the use of persistent prepare over the use of 
a stored procedure?

What would the interface to the feature be through database access 
drivers like JDBC? Explicit PREPARE GLOBAL or similar, then invocation 
with EXECUTE ?

How would users using increasingly common layers like Hibernate/JPA use it?

I'm also curious about how you'd address the possible need for periodic 
re-planning as the data changes, though AFAIK SQL functions suffer from 
the same limitation there.

I guess I personally just don't understand what the point of the 
persistent prepare feature you describe is. However, this post that you 
linked to:

http://archives.postgresql.org/pgsql-hackers/2008-04/msg00867.php

actually describes a query plan cache, rather than persistent prepare. 
The post assumes the app will explicitly manage the cache, which I'm not 
sure is a good idea, but I can see the point of a plan cache. There 
might be some heuristics Pg could use to decide what to cache and to 
evict (planner time cost vs memory use, frequency of use, etc) so the 
app doesn't have to know or care about the plan cache.  However, I'm not 
too sure how you'd match an incoming query to a cached plan, and 
determine that the plan was still valid, with enough speed to really 
benefit from the plan cache. Then again, I don't know much about Pg's 
innards, so that doesn't mean much.

Tom Lane responded to that post to point out some of the complexities:

http://archives.postgresql.org/pgsql-hackers/2008-04/msg00868.php

--
Craig Ringer