-----Original Message-----
From: Neil Conway [mailto:nconway@klamath.dyndns.org]
Sent: Tuesday, February 26, 2002 3:04 PM
To: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] eWeek Poll: Which database is most critical to
your
On Tue, 2002-02-26 at 15:30, Zak Greant wrote:
Good Day All,
eWeek has posted a poll that asks which database server is most
critical
to your organization.
The article mentions a MySQL feature which apparently improved
performance considerably:
//
MySQL 4.0.1's new, extremely fast query cache is also quite notable, as
no other database we tested had this feature. If the text of an incoming
query has a byte-for-byte match with a cached query, MySQL can retrieve
the results directly from the cache without compiling the query, getting
locks or doing index accesses. This query caching will be effective only
for tables with few updates because any table updates that clear the
cache to guarantee correct results are always returned.
//
My guess is that it would be relatively simple to implement. Any
comments on this?
If I implemented this, any chance this would make it into the tree? Of
course, it would be:
- disabled by default
- enabled on a table-by-table basis (maybe an ALTER TABLE command)
I don't see how it will do any good. There is no "prepare" in
Postgresql
and therefore you will simply be reexecuting the queries every time any
way. Also, parameter markers only work in embedded SQL and that is a
single tasking system.
I think it would be a major piece of work to do anything useful along
those lines.
If you look at how DB/2 works, you will see that they store prepared
statements. Another alternative would be to keep some point in the
parser marked and somehow jump to that point, but you would have to
be able to save a parse tree somewhere and also recognize the query.
Here is where problems come in...
-- Someone wants blue and blue-green, etc shirts that are backordered
SELECT shirt, color, backorder_qty FROM garments WHERE color like
"BLUE%"
Now, another query comes along:
-- Someone else wants reddish, etc shirts that are backordered:
SELECT shirt, color, back
order_qty FROM garments WHERE color like "RED%"
It's the same query with different data. Without parameter markers you
will never know it. And yet this is exactly the sort of caching that is
useful.
However, an exact match is still not a bad idea. You might have a different
execution plan depending on the statistics of the data in your column.