Not knowing anything about the internals of pg, I don't know how this relates, but in
theory,
query plan caching is not just about saving time re-planning queries, it's about
scalability.
Optimizing queries requires shared locks on the database metadata, which, as I
understand it
causes contention and serialization, which kills scalability.
I read this thread from last to first, and I'm not sure if I missed something, but if
pg isnt
caching plans, then I would say plan caching should be a top priority for future
enhancements. It
needn't be complex either: if the SQL string is the same, and none of the tables
involved in the
query have changed (in structure), then re-use the cached plan. Basically, DDL and
updated
statistics would have to invalidate plans for affected tables.
Preferably, it should work equally for prepared statements and those not pre-prepared.
If you're
not using prepare (and bind variables) though, your plan caching down the drain
anyway...
I don't think that re-optimizing based on values of bind variables is needed. It seems
like it
could actually be counter-productive and difficult to asses it's impact.
That's the way I see it anyway.
:)
--- Scott Kirkwood <[EMAIL PROTECTED]> wrote:
> I couldn't find anything in the docs or in the mailing list on this,
> but it is something that Oracle appears to do as does MySQL.
> The idea, I believe, is to do a quick (hash) string lookup of the
> query and if it's exactly the same as another query that has been done
> recently to re-use the old parse tree.
> It should save the time of doing the parsing of the SQL and looking up
> the object in the system tables.
> It should probably go through the planner again because values passed
> as parameters may have changed. Although, for extra points it could
> look at the previous query plan as a hint.
> On the surface it looks like an easy enhancement, but what do I know?
> I suppose it would benefit mostly those programs that use a lot of
> PQexecParams() with simple queries where a greater percentage of the
> time is spent parsing the SQL rather than building the execute plan.
> What do you think?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
_______________________________
Do you Yahoo!?
Declare Yourself - Register online to vote today!
http://vote.yahoo.com
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
