Optimizing a Vertica SQL query

[adrian.oprea]

I'd like to construct a cumulative column from the delta values (positive INTs), for the purposes of inserting this cumulative data into another table.
This is what I've got so far:

Code: Select all

SELECT key, day,
   SUM(delta) OVER (
PARTITION BY key ORDER BY day asc RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
),  delta
FROM S

In my SQL flavor, default window clause is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, but I left that in there to be explicit.

This query is really slow, like order of magnitude slower than the old broken query, which filled in 0s for the cumulative count. Any suggestions for other methods to generate the cumulative numbers?


The RDBMs I'm using is Vertica. Vertica SQL precludes the first subselect solution there, and its query planner predicts that the 2nd left outer join solution is about 100 times more costly than the analytic form I show above.

[NorbertKrupa]

Can you show us an EXPLAIN plan or a table structure?