Search Postgresql Archives

Re: New feature: accumulative functions.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux