Not knowing anything about the internals of pg, I don't know how this relates, but in 
query plan caching is not just about saving time re-planning queries, it's about 
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 
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 

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!

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?


Reply via email to