* Dawid Kuroczko:
> Right now the only way of getting such information from PostgreSQL
> is by logging all queries and analyzing logs. The current_query
> column of pg_stat_activity is useless as the (prepared) queries are
> usually so short lived that you will see one execution out of
> thousand
On Mon, Apr 14, 2008 at 5:01 PM, Csaba Nagy <[EMAIL PROTECTED]> wrote:
> On Mon, 2008-04-14 at 10:55 -0400, Mark Mielke wrote:
> > The other ideas about automatically deciding between plans based on
> > ranges and such strike me as involving enough complexity and logic, that
> > to do properly,
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 li
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 ti
On Mon, 2008-04-14 at 10:55 -0400, Mark Mielke wrote:
> The other ideas about automatically deciding between plans based on
> ranges and such strike me as involving enough complexity and logic, that
> to do properly, it might as well be completely re-planned from the
> beginning to get the most
I like cross-session query plan caching talk. I would prefer if the
function was optional (i.e. per-session "use cross-session query plan
cache" variable).
I like the "automatic re-plan if the estimate did not match the actual"
idea with some softening technique involved such as "if the last 3
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, an
> ... 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
> cou
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
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 anot
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
c
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 ta
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 wit
> 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
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 plans
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
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,
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
Would it be possible to store plans with an indication of the
search path that was used to find tables, and for temp tables
some snapshot of the statistics for the table if any?
My suspicions are that:
* where you have a lot of short-lived connections then actually
they will often use the defau
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 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
"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
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.
Y
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
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 s
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
> > > wi
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
Well if you're caching per-connection then it doesn't really matter
whether
you do it on the client side or the server side, it's pretty much
exactly the
same problem.
Actually I thought about doing it on the server since it would then also
work with connection pooling.
Doi
"PFC" <[EMAIL PROTECTED]> writes:
> But, using prepared statements with persistent connections is messy,
> because you never know if the connection is new or not, if it contains
> already
> prepared statements or not, you'd have to maintain a list of those statements
> (named) for every q
PFC <[EMAIL PROTECTED]> writes:
> And it is very easy to recognize a query we've seen before,
It's not so easy as all that. Consider search_path. Consider temp
tables.
The real problem here is entirely on the client side:
> But, using prepared statements with persistent connections
I think what he's referring to is persistently caching plans so that new
connections can use them. That makes a lot more sense if you have lots of
short-lived connections like a stock php server without persistent
connections
turned on or a connection pooler. You can prepare queries but they o
"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
>
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 n
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
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 website
35 matches
Mail list logo
