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