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 <pasman.p@xxxxxxxxx>:
> 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 <marti@xxxxxxxxx>:
>> 2011/9/25 pasman pasmański <pasman.p@xxxxxxxxx>:
>>> 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
>>> test smaller range (xlower, xgreater).
>>> 4. Otherwise no rows satisfy condition.
>>
>> I can't get very excited about this feature for index scans. However,
>> I think there's another, more interesting use case: sorting
>>
>> I frequently write queries like:
>> SELECT date_trunc('month', somedate), sum(foo)
>> GROUP BY date_trunc('month', somedate);
>>
>> Currently the planner doesn't realize that instead of
>> GroupAggregate+Sort, it can use the already existing sorted index on
>> just (somedate). Alternatively I would need to create a separate
>> date_trunc functional index for daily, weekly and monthly aggregates
>> for EACH meaningful time zone.
>>
>> This would be a planner-only change and nothing the executor needs to
>> know
>> of.
>>
>> Now obviously HashAggregate helps a lot with these kinds of queries,
>> but there are still cases where GroupAggregate would be a win -- for
>> instance, queries with a LIMIT.
>>
>> Regards,
>> Marti
>>
>
>
> --
> ------------
> pasman
>
--
------------
pasman
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
