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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
