2011/9/25 pasman pasmański <pasman.p@gmail.com>:
> 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