There is a difference between MySQL and Oracle here.
Oracle, to reduce parse/planner costs, hashes statements to see if it can
match an existing optimizer plan. This is optional and there are a few
flavors that range from a characher to characyter match through parse tree
matches through replacing of literals in the statements with parameters.
This dramatically improves performance in almost all high transaction rate
MySQL stores a statement with its results. This is optional and when a
client allows this type of processing, the SQL is hashed and matched to the
statement - and the stored *result* is returned. The point is that a lot of
systems do lots of static queries, such as a pick list on a web page - but
if the data changes the prior result is returned. This (plus a stable jdbc
driver) was the reason MySQL did well in the eWeek database comparison.
----- Original Message -----
From: "Scott Kirkwood" <scottakirkwood(at)gmail(dot)com>
Sent: Wednesday, September 22, 2004 3:50 PM
Subject: [PERFORM] Caching of Queries
> 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
In response to
pgsql-performance by date
|Next:||From: Anjan Dave||Date: 2004-09-22 21:49:02|
|Subject: SAN performance|
|Previous:||From: Rod Taylor||Date: 2004-09-22 20:52:59|
|Subject: Re: NAS, SAN or any alternate solution ?|