The hairiness is in the plan dependence (or independence) on parameter
values, ideally we only want to cache plans that would be good for all
parameter values, only the user knows that precisely. Although it could be
possible to examine the column histograms...
If cached plans
If cached plans would be implemented, the dependence on parameter values
could be solved too: use special fork nodes in the plan which execute
different sub-plans depending on special parameter values/ranges,
possibly looking up the stats at runtime, so that the plan is in a
compiled state with
Csaba Nagy wrote:
If cached plans would be implemented, the dependence on parameter values
could be solved too: use special fork nodes in the plan which execute
different sub-plans depending on special parameter values/ranges,
possibly looking up the stats at runtime, so that the plan is in a
PFC wrote:
Bind message behaviour was modified :
- If the user asks for execution of a named prepared statement, and the
named statement does not exist in PG's prepared statements cache, instead
of issuing an error and borking the transaction, it Binds to an empty
statement, that takes
On Mon, 2008-04-14 at 16:54 +0300, Heikki Linnakangas wrote:
Figuring out the optimal decision points is hard, and potentially very
expensive. There is one pretty simple scenario though: enabling the use
of partial indexes, preparing one plan where a partial index can be
used, and another
On Mon, 2008-04-14 at 16:10 +0200, Csaba Nagy wrote:
... or plan the query with the actual parameter value you get, and also
record the range of the parameter values you expect the plan to be valid
for. If at execution time the parameter happens to be out of that range,
replan, and possibly
... or plan the query with the actual parameter value you get, and also
record the range of the parameter values you expect the plan to be valid
for. If at execution time the parameter happens to be out of that range,
replan, and possibly add new sublpan covering the extra range. This
could
Bind message behaviour was modified :
- If the user asks for execution of a named prepared statement, and the
named statement does not exist in PG's prepared statements cache,
instead
of issuing an error and borking the transaction, it Binds to an empty
statement, that takes no parameters,
On Mon, 14 Apr 2008 16:17:18 +0200, Csaba Nagy [EMAIL PROTECTED] wrote:
On Mon, 2008-04-14 at 16:10 +0200, Csaba Nagy wrote:
... or plan the query with the actual parameter value you get, and also
record the range of the parameter values you expect the plan to be valid
for. If at execution
On Mon, 2008-04-14 at 17:08 +0200, PFC wrote:
Those Decision nodes could potentially lead to lots of decisions
(ahem).
What if you have 10 conditions in the Where, plus some joined ones ?
That
would make lots of possibilities...
Yes, that's true, but most of them are likely
Another issue with plan caches, besides contention, in Oracle at least, is
shared memory fragmentation (as plans aren't all the same size in memory ...)
But this cache is very helpful for developments where every query is done via
prepare/execute/deallocate. I've seen it a lot on java apps, the
On Fri, Apr 11, 2008 at 12:34 PM, PFC [EMAIL PROTECTED] wrote:
Well, I realized the idea of global prepared statements actually
sucked, so I set on another approach thanks to ideas from this list,
this is
caching query plans.
Well, that's a blatantly bad realization. Perhaps you
On Sun, Apr 13, 2008 at 02:26:04PM +0200, PFC wrote:
* global plan cache in shared memory, implemented as hashtable, hash key
being the (search_path, query_string)
Doubt : Can a plan be stored in shared memory ? Will it have to be copied
to local memory before being executed ?
Frankly, I
On Sun, Apr 13, 2008 at 2:26 PM, PFC [EMAIL PROTECTED] wrote:
Oracle keeps a statement/plan cache in its shared memory segment (SGA)
that greatly improves its performance at running queries that don't
change very often.
Can we have more details on how Oracle does it ? For
Why limit ourselves with Oracle? How all major proprietary RDBMSs do it.
Thanks for the links. Very interesting.
The DB2 document especially mentions an important point : in order to make
their planner/optimizer smarter, they had to make it slower, hence it
became crucial to cache the
In article [EMAIL PROTECTED],
PFC wrote:
So, where to go from that ? I don't see a way to implement this without
a (backwards-compatible) change to the wire protocol, because the clients
will want to specify when a plan should be cached or not. Since the user
should not have to
On Sat, Apr 12, 2008 at 2:44 PM, Perez [EMAIL PROTECTED] wrote:
In article [EMAIL PROTECTED],
PFC wrote:
So, where to go from that ? I don't see a way to implement this
without
a (backwards-compatible) change to the wire protocol, because the clients
will want to specify
On Fri, Apr 11, 2008 at 12:34 PM, PFC [EMAIL PROTECTED] wrote:
Well, I realized the idea of global prepared statements actually
sucked, so I set on another approach thanks to ideas from this list, this is
caching query plans.
Well, that's a blatantly bad realization. Perhaps you
On Sat, Apr 12, 2008 at 8:44 AM, Perez [EMAIL PROTECTED] wrote:
Doesn't Oracle do this now transparently to clients?
Of course it does, and it has since the late 80's I believe.
Oracle keeps a statement/plan cache in its shared memory segment (SGA)
that greatly improves its performance at
On Sat, Apr 12, 2008 at 2:19 PM, Dawid Kuroczko [EMAIL PROTECTED] wrote:
There are other benefits as well. Oracle lets you see the statistics
associated
with given plans. So you can see how many times given (cached) query was
executed, how much resources did it consume and do on.
Yes,
Jonah H. Harris [EMAIL PROTECTED] writes:
On Sat, Apr 12, 2008 at 2:19 PM, Dawid Kuroczko [EMAIL PROTECTED] wrote:
There are other benefits as well. Oracle lets you see the statistics
associated
with given plans. So you can see how many times given (cached) query was
executed, how much
On Sat, Apr 12, 2008 at 10:17 PM, Tom Lane [EMAIL PROTECTED] wrote:
Yes, this is worthless on large active databases. The logging
overhead alone starts to affect performance.
But somehow, all that stuff with cached plans is free?
Of course not. The first time you execute a query, it is
Well, I realized the idea of global prepared statements actually sucked,
so I set on another approach thanks to ideas from this list, this is
caching query plans.
First, let's see if there is low hanging fruit with the typical small,
often-executed queries that are so frequent on
PFC wrote:
So, where to go from that ? I don't see a way to implement this without
a (backwards-compatible) change to the wire protocol, because the clients
will want to specify when a plan should be cached or not. Since the user
should not have to name each and every one of the
Alvaro Herrera [EMAIL PROTECTED] writes:
PFC wrote:
So, where to go from that ? I don't see a way to implement this without
a (backwards-compatible) change to the wire protocol, because the clients
will want to specify when a plan should be cached or not. Since the user
should not have to
25 matches
Mail list logo
