X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id ACEA2329D28 for ; Thu, 23 Sep 2004 16:29:30 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 61295-10 for ; Thu, 23 Sep 2004 15:29:28 +0000 (GMT) Received: from web41114.mail.yahoo.com (web41114.mail.yahoo.com [66.218.93.30]) by svr1.postgresql.org (Postfix) with SMTP id EB05D329DB3 for ; Thu, 23 Sep 2004 16:29:26 +0100 (BST) Message-ID: <20040923152925.18234.qmail@web41114.mail.yahoo.com> Received: from [61.25.53.100] by web41114.mail.yahoo.com via HTTP; Thu, 23 Sep 2004 08:29:25 PDT Date: Thu, 23 Sep 2004 08:29:25 -0700 (PDT) From: Mr Pink Subject: Re: Caching of Queries To: Scott Kirkwood , pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200409/238 X-Sequence-Number: 8366 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 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