Robert Haas writes:
> This isn't the first complaint about this mechanism that we've gotten,
> and it won't be the last. Way too many of our users are way more
> aware than they should be that the threshold here is five rather than
> any other number, which to me is a clear-cut sign that this nee
On Wed, Jan 13, 2016 at 10:47 AM, Tom Lane wrote:
> Vladimir Sitnikov writes:
>> Note: I state that mixing "kinds" of bind values is a bad application
>> design anyway. In other words, application developer should understand
>> if a query is DWH-like (requires replans) or OLTP-like (does not
>> r
On Mon, Jan 18, 2016 at 02:14:11PM -0500, Tom Lane wrote:
> Bruce Momjian writes:
> > I never understood why we don't just keep the selectivity estimates of
> > previous plans and just reuse the plan if the selectivity estimates are
> > similar. Isn't parameter selectivity the only thing that dis
Bruce Momjian writes:
> I never understood why we don't just keep the selectivity estimates of
> previous plans and just reuse the plan if the selectivity estimates are
> similar. Isn't parameter selectivity the only thing that distinguishes
> on plan with parameter from another?
> Checking sele
On Wed, Jan 13, 2016 at 10:47:18AM -0500, Tom Lane wrote:
> Vladimir Sitnikov writes:
> > Note: I state that mixing "kinds" of bind values is a bad application
> > design anyway. In other words, application developer should understand
> > if a query is DWH-like (requires replans) or OLTP-like (doe
2016-01-13 17:12 GMT+01:00 Vladimir Sitnikov :
> >If plan is ok for one value parameters, then can be pretty bad for
> following parameters.
>
> Happy statements are all alike; every unhappy statement is unhappy in
> its own way (see [1]).
> If user is sending different kinds of parameters, he is
>If plan is ok for one value parameters, then can be pretty bad for following
>parameters.
Happy statements are all alike; every unhappy statement is unhappy in
its own way (see [1]).
If user is sending different kinds of parameters, he is shooting in the foot.
>Albe's proposal can be good enoug
>(1) the generic plan is falsely optimistic
That is my case.
Application is sending most common value on every execution while
backend is optimistic and it things that the app would stop sending
MCVs.
Costs for the plans are OK. However, there is a data skew, so it is
hard to tell what is the "tr
Vladimir Sitnikov writes:
> Note: I state that mixing "kinds" of bind values is a bad application
> design anyway. In other words, application developer should understand
> if a query is DWH-like (requires replans) or OLTP-like (does not
> require replans). Agreed?
No, not agreed. As was already
2016-01-13 16:22 GMT+01:00 Vladimir Sitnikov :
> >A value of -1 could disable generic plans
>
> I do not like the idea.
>
> I've seen dramatic performance improvements from using cached plans.
> The numbers are like "20ms to plan vs 1ms to execute" for an often
> used OLTP query. Query text is inv
2016-01-13 16:18 GMT+01:00 Albe Laurenz :
> Pavel Stehule wrote:
> > I like a strategy based on risks. Probably there are situation, when the
> generic plan is great every
> > time - INSERTs, UPDATEs via PK, simple SELECTs via PK. generic plan can
> be well if almost all data has
> > similar proba
>A value of -1 could disable generic plans
I do not like the idea.
I've seen dramatic performance improvements from using cached plans.
The numbers are like "20ms to plan vs 1ms to execute" for an often
used OLTP query. Query text is involved (~5-10KiB).
Vladimir
--
Sent via pgsql-hackers mai
Pavel Stehule wrote:
> I like a strategy based on risks. Probably there are situation, when the
> generic plan is great every
> time - INSERTs, UPDATEs via PK, simple SELECTs via PK. generic plan can be
> well if almost all data has
> similar probability. Elsewhere on bigger data, the probability
>Basically you're arguing to fix one specific edge case which bugs you
>personally, by creating a lot of others, which don't bug you. Not
>convincing.
It bugs me.
It bugs clients of pgjdbc (e.g. Thomas who started the thread).
Note: support of prepared statements for java applications has just
la
> Generally using a very small sample
That is another issue. Inventing some other algorithm instead of
current "cache after 5 executions" is another effort.
However, I suggest to "learn" from what client is sending.
You suggest to completely ignore that and just prepare for the case
he/she will s
>so you don't get to (or want to) have any control over the underlying prepared
>statement.
That is pl/pgsql's problem, isn't it?
In the mean time, user can use different query texts (e.g. by adding
offset 0, offset 0*1, offset 0*2, etc kind of stuff they typically use
to tune queries) to convinc
On 2016-01-13 17:38:22 +0300, Vladimir Sitnikov wrote:
> >so you don't get to (or want to) have any control over the underlying
> >prepared statement.
>
> That is pl/pgsql's problem, isn't it?
> In the mean time, user can use different query texts (e.g. by adding
> offset 0, offset 0*1, offset 0*
On 13/01/16 15:34, Vladimir Sitnikov wrote:
This completely screws over PL/PgSQL, among other things.
Can you elaborate a bit?
You just write a query like this:
SELECT * FROM foo WHERE bar = _Variable;
so you don't get to (or want to) have any control over the underlying
prepared stateme
On 2016-01-13 17:26:43 +0300, Vladimir Sitnikov wrote:
> > consider e.g a table with one somewhat common and otherwise just unique
> > values.>
> So what?
> If I understand you properly, you mean: "if client sends unique binds
> first 5-6 executions and bad non-unique afterwards, then cached plan
>This completely screws over PL/PgSQL, among other things.
Can you elaborate a bit?
Vladimir
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 13/01/16 15:26, Vladimir Sitnikov wrote:
2) It is likely to be more performant. We just need to explain users
that "if different plans required, just use different statements".
This completely screws over PL/PgSQL, among other things.
.m
--
Sent via pgsql-hackers mailing list (pgsql-hack
> for one custom plans can be much better than the generic plan, independent of
> cardinalities
So what? I do not suggest dropping custom plans entirely.
I perfectly understand there are cases when better replan every time.
> consider e.g a table with one somewhat common and otherwise just uniqu
On January 13, 2016 3:02:27 PM GMT+01:00, Vladimir Sitnikov
wrote:
>> The custom plan is *more expensive*;
>
>You compare costs of custom vs generic plans.
>I suggest: do not compare costs *at all*.
>
>>I don't know, it's your proposal :-) But it looks like I
>misunderstood.
>
>It is not.
>
>My
> The custom plan is *more expensive*;
You compare costs of custom vs generic plans.
I suggest: do not compare costs *at all*.
>I don't know, it's your proposal :-) But it looks like I misunderstood.
It is not.
My suggestion is: build a generic plan (that is the plan that will
return proper re
On 13/01/16 14:36, Vladimir Sitnikov wrote:
Say you already have a plan which looks like this:
Now the plan gets invoked with $1 = 5. What exactly in your mind would happen
here?
A sequential scan with $1=5 condition. What else could be there?
I don't know, it's your proposal :-) But it l
Note: I do not suggest changing already cached plans yet.
I suggest looking into "6th bind values" when building a cached plan.
In other words, "if first 5 execution do not reveal dependence on bind
values, then cache the generated plan".
>Say you already have a plan which looks like this:
>Now th
On 13/01/16 14:27, Vladimir Sitnikov wrote:
TL;DR: I suggest to create "generic plan" with regard to current bind values.
What's wrong with that approach?
I don't understand how this is supposed to work. Say you already have a
plan which looks like this:
Seq Scan on foo (cost=0.00..100.00
On 13/01/16 14:12, Pavel Stehule wrote:
so the strategy - if cost of generic plan is less than some MAGIC CONSTANT
(can be specified by GUC), then use generic plan. Elsewhere use a custom
plan everytime.
It allow to controll the plan reusing. When MAGIC CONSTANT = 0 .. use
custom plan everytime,
Please, read my suggestion again.
TL;DR: I suggest to create "generic plan" with regard to current bind values.
What's wrong with that approach?
Vladimir
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpr
2016-01-13 11:44 GMT+01:00 Vladimir Sitnikov :
> >the implementation is simply - but it hard to design some really general
> - it is task for UI
>
> Can you please rephrase?
>
Sorry - It is task for artifical inteligency
>
> Current design is "if the cost of a generic plan is less than the one
>
>the implementation is simply - but it hard to design some really general - it
>is task for UI
Can you please rephrase?
Current design is "if the cost of a generic plan is less than the one
of a custom plan+replan, prefer generic".
I think that is wrong.
"Generic plan" misunderestimates a cost
2016-01-12 17:01 GMT+01:00 Vladimir Sitnikov :
> VS>>Why doesn't backend reuse already existing good plan?
> PS>this logic is driven by plan cost, not by plan execution time.
>
> It do understand that currently PG replans with $1, $2 and uses
> default selectivities for that.
>
> What I am asking
VS>>Why doesn't backend reuse already existing good plan?
PS>this logic is driven by plan cost, not by plan execution time.
It do understand that currently PG replans with $1, $2 and uses
default selectivities for that.
What I am asking is to make PG aware of "previously used bind values",
so it
2016-01-12 16:52 GMT+01:00 Vladimir Sitnikov :
> > I don't know if there is a good solution except disabling server
> prepared statements.
>
> Why doesn't backend reuse already existing good plan?
>
probably good plan is more expensive than wrong plan :(
this logic is driven by plan cost, not by
> I don't know if there is a good solution except disabling server prepared
> statements.
Why doesn't backend reuse already existing good plan?
The plan does account for the skew.
Can backend take selectivities from the original bind values?
Vladimir
--
Sent via pgsql-hackers mailing list (p
Vladimir Sitnikov wrote:
> Here's the simplified testcase:
> https://gist.github.com/vlsi/df08cbef370b2e86a5c1
>
> It reproduces the problem in both 9.4.4 and 9.5rc1.
> It is reproducible via both psql and pgjdbc.
>
> I use a single table, however my production case includes a join of
> two table
Here's the simplified testcase:
https://gist.github.com/vlsi/df08cbef370b2e86a5c1
It reproduces the problem in both 9.4.4 and 9.5rc1.
It is reproducible via both psql and pgjdbc.
I use a single table, however my production case includes a join of
two tables and the query is like
select ... from f
Hi Marko,
Interesting so why would it choose a worse plan at that point ? Why would
it change at all if the current plan is working well ?
Dave Cramer
da...@postgresintl.com
www.postgresintl.com
On 12 January 2016 at 07:15, Marko Tiikkaja wrote:
> On 12/01/16 13:00, Dave Cramer wrote:
>
>> We
On 12/01/16 13:00, Dave Cramer wrote:
We have an interesting problem, and the reporter has been kind enough to
provide logs for which we can't explain.
I'd be interested to hear any plausible explanations for a prepared plan
suddenly going from 2ms to 60ms for the same input values ?
This is a
39 matches
Mail list logo
