Re: Caching of Queries

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
systems.

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.

/Aaron

----- Original Message -----
From: "Scott Kirkwood" <scottakirkwood(at)gmail(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
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
>