Re: Caching of Queries - Mailing list pgsql-performance

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 <scottakirkwood@gmail.com> 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