Matthew Peter wrote:
Wouldn't it work just like plpgsql functions? Where the first call caches the plan
or whatever?
A stored procedure can cache the query plan but that is separate from
caching data.
When sending sql select statements to the server the query plan isn't
cached (if it was you would still need to compare the two selects to see
if they could use the same plan negating the benefit)
Your client program may be sending the same select command but how is
the server going to know it is the same? - a straight string comparison?
when you change the where clause that goes out.
If you want to bypass the parser/planner then use stored procedures.
With a small database you can have enough ram to have the whole dataset
(and indexes) in memory and only use the disk to save updates. With
large datasets you want enough ram to keep the most used data in memory
to reduce disk reads as much as possible.
Back to your cached view - if you have enough ram to cache your whole
database then the first select will bring that data into ram cache and
from then on will not need to read it from disk, effectively achieving
what you want - postgres is doing that without you implicitly telling it
to. If the data you want cached is getting replaced by more recent data
then either it isn't used as much as you think or you don't have enough
ram to cache what you use most. Forcing postgres to cache data you think
is more often used only makes it read the disk more for the other data
that it can no longer fit into cache.
Either way adding more ram is the only way to reduce disk access.
--
Shane Ambler
pgSQL@xxxxxxxxxxxxxxxx
Get Sheeky @ http://Sheeky.Biz
