Query cache import?

On Tue, 31 Oct 2000, Alfred Perlstein wrote:

I never saw much traffic regarding Karel's work on making stored
proceedures:

http://people.freebsd.org/~alfred/karel-pgsql.txt

What happened with this? It looked pretty interesting. :(

It's probably a little about me :-) ... well,

My query cache is in usable state and it's efficient for all
things those motivate me to work on this.

some basic features:

- share parsed plans between backends in shared memory
- store plans to private backend hash table
- use parameters for stored queries
- better design for SPI
- memory usage for saved plans
- save plans "by key"

The current query cache code depend on 7.1 memory management. After
official 7.1 release I prepare patch with query cache+SPI (if not
hit me over head, please ..)

All what will doing next time not depend on me, *it's on code developers*.

For example Jan has interesting idea about caching all plans which
processing backend. But it's far future and IMHO we must go by small
steps to Oracle's funeral :-)

If I need the query cache in the my work (typical for some web+pgsql) or
will some public interest I will continue on this, if not I freeze it.
(Exists more interesting work like http://mape.jcu.cz ... sorry of
advertising :-)

Karel

My query cache is in usable state and it's efficient for all things
those motivate me to work on this.

Well, you know, us application developers are lazy egoists, we want all of
that without efforts on our side :) In fact, customers do that. They don't
want to pay for both implementing query cache and re-writing applications.
I suggest by your description that it shouldn't be a brain surgery to
apply your caching to a stable server, so when I'll have a chance to put my
hands on a busy discussion forum next time, it'd be nice to give it a
whirl.

--

contaminated fish and microchips
huge supertankers on Arabian trips
oily propaganda from the leaders' lips
all about the future
there's people over here, people over there
everybody's looking for a little more air
crossing all the borders just to take their share
planning for the future

Rainbow, Difficult to Cure

* Karel Zak <zakkr@zf.jcu.cz> [001031 16:18] wrote:

On Tue, 31 Oct 2000, Alfred Perlstein wrote:

I never saw much traffic regarding Karel's work on making stored
proceedures:

http://people.freebsd.org/~alfred/karel-pgsql.txt

What happened with this? It looked pretty interesting. :(

It's probably a little about me :-) ... well,

My query cache is in usable state and it's efficient for all
things those motivate me to work on this.

some basic features:

- share parsed plans between backends in shared memory
- store plans to private backend hash table
- use parameters for stored queries
- better design for SPI
- memory usage for saved plans
- save plans "by key"

The current query cache code depend on 7.1 memory management. After
official 7.1 release I prepare patch with query cache+SPI (if not
hit me over head, please ..)

All what will doing next time not depend on me, *it's on code developers*.

For example Jan has interesting idea about caching all plans which
processing backend. But it's far future and IMHO we must go by small
steps to Oracle's funeral :-)

Well I'm just hoping that perl's $dbh->prepare() actually does a
temporary stored proceedure so that I can shave cycles off of
my thousands upon thousands of repeated queries. :)

--
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
"I have the heart of a child; I keep it in a jar on my desk."

On Tue, 31 Oct 2000, Alfred Perlstein wrote:

* Karel Zak <zakkr@zf.jcu.cz> [001031 16:18] wrote:

On Tue, 31 Oct 2000, Alfred Perlstein wrote:

All what will doing next time not depend on me, *it's on code developers*.

^^^^^^^^
right is "core"

Well I'm just hoping that perl's $dbh->prepare() actually does a
temporary stored proceedure so that I can shave cycles off of
my thousands upon thousands of repeated queries. :)

IMHO implement good cache for planns is not easy, if is wanted
use cached planns in more backend and store it in shared memory. I
wrote for this new memory context type. My idea is not save/share
parsed planns only for PREPARE/EXECUTE statemment but for SPI
(triggers - FK for example) too. It expect support inside backend
and not is possible write it in some application layout (at client).

But it's very good "investment", because query pasring in the PG is
very expensive (all in queries is dynamic). In my tests is execute
for stored planns very faster (90%) for queries that spending more time
in the parser/planner/rewriter.

Karel