Cache for query plans

Roberto Abalde wrote:

Hi,

I need to implement a cache for query plans as part of my BSc thesis. Does
anybody know what happened to Karel Zak's patch?

Dunno.

I'm also looking for some comments & tips about how to implement a cache for
query plans and how to deal with the implementation of shared memory in
PSQL.

Query trees and -plans are only handled on the backend side.
And don't forget that dealing with such thing on the client
side, which allmost runs under clients memory control, opens
a can of worms with respect to security and other issues.
Plus it's not a psql only thing. If you want applications to
benefit from it, it has to be implemented on the libpq level.

That said, general purpose query cacheing using shared memory
must at least run the parser on each query string. It could
then build some hash key based on the querytree node
structure and hold query trees and -plans in shared memory.
If it finds a query with the same key in it's cache, it'll
take a closer look if the cached and actual query only differ
in const nodes and instead of planning/optimizing again it'll
just use the cached one. A query shouldn't make it into the
cache just at first occurence, but it's hash key does and is
subsequently counted up. Combined with some ageing and the
rest of required herbs it'll serve a good meal for some types
of applications. Thus, it has to be a per database
configurable feature.

Explicit PREPARE and EXECUTE statements (or whatever keywords
actually used) are IMHO better candidates for a per session
(backend) non-shared implementation, because how could
another client be sure that a given query identifier actually
does or doesn't exist and what querystring is/should be
associated with it?

Greetings,
Roberto

PS: Sorry for my english :(

What's wrong with it?

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

On Wed, May 30, 2001 at 03:00:53PM -0300, Roberto Abalde wrote:

Hi,

I need to implement a cache for query plans as part of my BSc thesis. Does
anybody know what happened to Karel Zak's patch?

Hi,

my patch is on my ftp and nobody works on it, but I mean it's good
begin for some next work. I not sure with implement this experimental
patch (but usable) to official sources. For example Jan has more complex
idea about query plan cache ... but first time we must solve some
sub-problems like memory management in shared memory that is transparently
for starndard routines like copy query plan ... and Tom isn't sure with
query cache in shared memory...etc. Too much queries, but less answers :-)

Karel

PS: Sorry for my english :(

Do you anytime read any my mail :-)

Karel

--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/

C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz