Thanks Marti for inspiration :). Monotonic functions allows to skip
some sorts in window expressions containing them:
select winfun1(...) over(order by x), winfun2(...) over(order by f(x)) from ...
2011/9/27, pasman pasmański :
> Yes, accumulative functions may be used for sorting,groupping an
Yes, accumulative functions may be used for sorting,groupping and
merge joins with limit.
Groupping looks simplest to implement, and comparable to performance
of functional index
.
2011/9/27, Marti Raudsepp :
> 2011/9/25 pasman pasmański :
>> My english is not perfect, by accumulative i think abo
2011/9/25 pasman pasmański :
> My english is not perfect, by accumulative i think about monotonically
> increasing function.
>
> It works that for clause WHERE f(x)=const:
> 1. Read root page of index_on_x and get x1 ... Xn
> 2. Calculate f(x1) ... f(xn) for this page
> 3. When f(x1)<=const<= f(xn)
In article
,
Pavel Stehule writes:
> 2011/9/25 pasman pasmański :
>> I found second use case. Look at expression:
>>
>> where left(str,n)='value'
>>
>> function left(str,n) increase monotonically for str and n. With this
>> feature it can use index on str.
>>
>> Classic index needs recreating
I write small summary.
Feature details: additional flags for monotonical functions. Learn
planner to use them. New node in execution plan - functional index
scan.
Pro: single btree index may be used in many expressions containing
only monotonnical functions.
Contra: big developement effort. No n
For single argument strict increasing function f(x), estimation is
simple: it is f(estimation of x).
2011/9/25, Pavel Stehule :
> 2011/9/25 pasman pasmański :
>> See that setting flag on function need less work than create new gist
>> operator. Of course if postgresql's developers do biggest work
2011/9/25 pasman pasmański :
> See that setting flag on function need less work than create new gist
> operator. Of course if postgresql's developers do biggest work before.
any feature in pg should to have a general usage - with real use cases
and real performance advantages.
I am not sure, if m
Yes, i wrote this for pleasure and discusion, not for solve a real problem :).
2011/9/25, Tom Lane :
> =?ISO-8859-2?Q?pasman_pasma=F1ski?= writes:
>> I found second use case. Look at expression:
>> where left(str,n)='value'
>
>> function left(str,n) increase monotonically for str and n. With this
See that setting flag on function need less work than create new gist
operator. Of course if postgresql's developers do biggest work before.
2011/9/25, Pavel Stehule :
> 2011/9/25 pasman pasmański :
>> I found second use case. Look at expression:
>>
>> where left(str,n)='value'
>>
>> function lef
=?ISO-8859-2?Q?pasman_pasma=F1ski?= writes:
> I found second use case. Look at expression:
> where left(str,n)='value'
> function left(str,n) increase monotonically for str and n. With this
> feature it can use index on str.
Can't get excited about that, because that only works in C locale,
and
This feature give profits for increasing muliti-arg functions. Example:
WHERE f(x,param) = const
it may be impossible to create functional indexes for all params.
2011/9/25, Pavel Stehule :
> Hello
>
> what is a real use case?
>
> Regards
>
> Pavel
>
> 2011/9/25 pasman pasmański :
>> My englis
2011/9/25 pasman pasmański :
> I found second use case. Look at expression:
>
> where left(str,n)='value'
>
> function left(str,n) increase monotonically for str and n. With this
> feature it can use index on str.
>
> Classic index needs recreating.
>
these use cases are just theory - for example
I found second use case. Look at expression:
where left(str,n)='value'
function left(str,n) increase monotonically for str and n. With this
feature it can use index on str.
Classic index needs recreating.
2011/9/25, Pavel Stehule :
> Hello
>
> what is a real use case?
>
> Regards
>
> Pavel
>
>
... When n changes of course.
Sorry for top posting, phone not allows to move cite.
2011/9/25, pasman pasmański :
> I found second use case. Look at expression:
>
> where left(str,n)='value'
>
> function left(str,n) increase monotonically for str and n. With this
> feature it can use index on str
2011/9/25 pasman pasmański :
> This feature give profits for increasing muliti-arg functions. Example:
>
> WHERE f(x,param) = const
>
> it may be impossible to create functional indexes for all params.
>
Sorry, I asked on real use case. Do you know some one?
Pavel
>
>
> 2011/9/25, Pavel Stehule
I think, it should be new node in executor. Planner select classic
index scan or new functional index scan.
2011/9/25, Tom Lane :
> =?ISO-8859-2?Q?pasman_pasma=F1ski?= writes:
>> My english is not perfect, by accumulative i think about monotonically
>> increasing function.
>
> Oh, I see how that
=?ISO-8859-2?Q?pasman_pasma=F1ski?= writes:
> My english is not perfect, by accumulative i think about monotonically
> increasing function.
Oh, I see how that would work. I can't get real excited about it
though. The use-case seems a bit narrow, and the amount of complexity
added to the btree s
My english is not perfect, by accumulative i think about monotonically
increasing function.
It works that for clause WHERE f(x)=const:
1. Read root page of index_on_x and get x1 ... Xn
2. Calculate f(x1) ... f(xn) for this page
3. When f(x1)<=const<= f(xn) then x1 <= searched x <= xn and we can
te
pasman pasmański Sunday 25 of September 2011 15:19:28
> Hi.
>
> I propose to add "accumulative" flag to a function definition. This
> flag would be set for function f(x) which is accumulative and
> immutable.
>
> This flag allows to use an index on x for clauses containing f(x):
>
> where f(x)
=?ISO-8859-2?Q?pasman_pasma=F1ski?= writes:
> I propose to add "accumulative" flag to a function definition. This
> flag would be set for function f(x) which is accumulative and
> immutable.
Maybe you'd better define what you mean by "accumulative" ...
> This flag allows to use an index on x fo
On Sep 25, 2011, at 9:19, pasman pasmański wrote:
> Hi.
>
> I propose to add "accumulative" flag to a function definition. This
> flag would be set for function f(x) which is accumulative and
> immutable.
>
> This flag allows to use an index on x for clauses containing f(x):
>
> where f(x) =
21 matches
Mail list logo
