Re: Re: [GENERAL] Query caching

On Thu, 2 Nov 2000, Tom Lane wrote:

> The Hermit Hacker <scrappy(at)hub(dot)org> writes:
> > Karel, where did things stand the last time this was brought up? We
> > haven't gone beta yet, can you re-submit a patch for v7.1 before beta so
> > that we can integrate the changes?
>
> I think it would be a very bad idea to try to integrate the query cache

We not talking about integrate it.. we talking about "prepare
*experimental* patch for 7.1" as contrib matter or compile time
option. I mean that contrib will better.

> stuff at this point in the 7.1 cycle. The feature needs more
> discussion/design/testing than we have time to give it for 7.1.

Agree.

>
> Some of the concerns I have about it:
>
> 1. What is the true performance gain --- if any --- in real-world
> situations? The numbers Karel has quoted sound like wildly optimistic

:-)

> best cases to me. What's the worst case? What's the average case?

It's total some as SPI's saved planns. The query cache not has too much
cost, EXECUTE saved plan is: lock, search in HTAB, unlock, run executor..

> 2. How do we handle flushing the cache when conditions change (schema
> alterations, etc)?

It's a *global* PG problem. What happen with VIEW if anyone change table
definition? ...etc. IMHO not ide for this.

> 3. Is it really a good idea to use a shared-across-backends cache?

I know your fear. But IMHO it's capital feature. For application
that not use persistent connection and very often re-connecting to
backend is very interesting share planns.

The query cache has two stores:
- global in shared memory -
- local in HTAB inside standard backend memory

> What are the locking and contention costs? What happens when we run

costs of spinlock..

> out of shared memory (which is a *very* finite resource)? Will cache

The cache has list of all planns and keep track of usage. If use define
cache entry as "removeable" is this oldest entry remove. Else cache
return error like 'cache is full'. The size of cache is possible define
during backen start up (argv).

> flush work correctly in a situation where backends are concurrently
> inserting new plans? Doesn't a shared cache make it nearly impossible
> to control the query planner, if the returned plan might have been
> generated by a different backend with a different set of
> optimization-control variables?

Hmm, not implemented now.

> 4. How does one control the cache, anyway? Can it be flushed by user
> command? How is a new query matched against existing cache entries?

All depend on user, the query is stored under some key (can be text or
binary). The key must be unique, but can be stored some planns but under
differnet keys.

> Can one determine which elements of a query are considered parameters to
> the cached plan, and which are constants? Does the syntax for doing

I don't underestend here. I use strandard '$' parameters and executor
options for this.

> these things have anything to do with the SQL standard?

Yes, it is a problem. I mean that SQL92 expect a little differnet
stuff of PREPARE/EXECUTE.

> I think this is a potentially interesting feature, but it requires far
> more discussion and review than it's gotten so far, and there's no time
> to do that unless we want to push out 7.1 release a lot more. I'm also
> concerned that we will need to focus heavily on testing WAL during 7.1
> beta, and I don't want a major distraction from that...

Total agree.. I prepare it as patch for playful hackers
(hope, like you :-)))

Karel