0

One column is value in integer. Second column is timestamp in datetime.

I want to efficiently get the sum of the values in the first column over a datetime range (start time to end time).

Is there some way to tell postgres to maintain a range query segment tree over one column.

Also imagine if the table had another column called userId. My queries would look 'give me the sum total over a date range for this userid'.

Improve this question
1
  • Laurenz's answer can be a little misleading, but before I post a clarifying answer, in practice what are you expecting as far as runtime?...if you were able to accomplish your goal in roughly tens of milliseconds, is that an acceptable runtime?
    – J.D.
    Commented Jun 1 at 12:11

1 Answer 1

Reset to default
0

Counting rows in PostgreSQL is always O(n), where n is the number of rows counted.

If that is too slow for you, the usual remedy is to pre-aggregate the data, for example per user ID and day, so that the number of rows to process becomes less.

Improve this answer
2
  • There's gotta be a better way. Sum with segment trees is O(logn). count of set, length of vector is O(1).
    – Kuelf Deez
    Commented May 22 at 4:57
  • That you don't want to hear it doesn't make it any less true. You can try your luck with third-party extensions that implement some kind of column store, but PostgreSQL uses row storage.
    – Laurenz Albe
    Commented May 22 at 5:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.