Optimize SUM with time segment WHERE Suppose I have a table A with columns id:int, kpi:int, created_at:datetime. And suppose I would need to fire the following query:
SELECT SUM(kpi) FROM A WHERE (created_at &gt; X AND created_at &lt; Y)

This query would make a full scan. But it can be optimised with a data structure segment tree. It takes O(nlog(n)) to build and O(log(n)) to query (instead of O(n) in case of full scan).
The question is, is there any index or plugin for Postgres (or other SQL Database) that does this?
And why don't you simply create b-tree index on created_at column?
Or compound (created_at+kpi) ? In the second case all the information will be taken during the index scan and no need to read blocks from the table itself.
But if the time range between X and Y is rather big, then the optimizer might think that full table scan is cheaper anyway.
In some particular situations the clustered index may be a good solution, too. For example by days or months, so when you select values, all blocks are read from the same area that belongs to that cluster.