Skip site navigation (1) Skip section navigation (2)

Re: Caching of Queries

From: Mr Pink <mr_pink_is_the_only_pro(at)yahoo(dot)com>
To: Scott Kirkwood <scottakirkwood(at)gmail(dot)com>,pgsql-performance(at)postgresql(dot)org
Subject: Re: Caching of Queries
Date: 2004-09-23 15:29:25
Message-ID: 20040923152925.18234.qmail@web41114.mail.yahoo.com (view raw or flat)
Thread:
Lists: 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(at)gmail(dot)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

In response to

Responses

pgsql-performance by date

Next:From: Mr PinkDate: 2004-09-23 15:39:31
Subject: Re: SAN performance
Previous:From: Tom LaneDate: 2004-09-23 14:57:41
Subject: Re: O_DIRECT setting

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group