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

Re: Caching of Queries

From: "Aaron Werman" <awerman(at)hotmail(dot)com>
To: "Scott Kirkwood" <scottakirkwood(at)gmail(dot)com>,<pgsql-performance(at)postgresql(dot)org>
Subject: Re: Caching of Queries
Date: 2004-09-22 21:43:16
Message-ID: BAY9-DAV21Y1SWoBDys0000cef5@hotmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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
>

In response to

Responses

pgsql-performance by date

Next:From: Anjan DaveDate: 2004-09-22 21:49:02
Subject: SAN performance
Previous:From: Rod TaylorDate: 2004-09-22 20:52:59
Subject: Re: NAS, SAN or any alternate solution ?

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