Hi team, we have a user case where we would want to get the latest event and the said event can just occur or occur months(or even years) ago. What would be the recommended approach to make such query performant if possible?

Are you referring to upsert?

Not upsert. But say I want to find the most recent event that has its state with value “deal_closed”

I think you can look at lastwithtime function

Thank you! This is exactly what I looked for. Curious how does it work behind the scene?

Last with time works right?

I gave it a shot but it seems not perform better. It feels it still has to scan through all matched documents.

That can be avoided. I am not clear why upsert is not a solution

Can you say more how upsert would help?

If you are only interested in the latest record for any primary-key, then you just use the upsert feature. it will overwrite older data for the PK as new data comes in.

I think I may not explain my case clearly. Say the latest e1(type = 1) occurred on 2024/01/01, and the latest e2(type = 2) occurred on 2025/01/01. And I want to get the latest timestamp for each type, so I would like the response to be:

Type, Latest TS
1, 2024/01/01
2, 2025/01/01

That still seems like upsert to me, with Type as PK

Type is not PK, event has its own id. For type T, it could have events continusouly ingested

Ok, then it is not upsert

So ST with MAX on ts col would not help?

Can you give sample query and sample data? (Few rows)

Let’s say I have these cols:

id

,

eventType

,

occurredAt

and have rows like:

1, t1, 2024/01/01
2, t2, 2024/02/03
3, t1, 2025/01/01
4, t3, 2025/08/03
5, t4, 2022/01/01

I wanted to query like: show me each event type’s latest occurred ts over the past 2 year window and the expected result would be the following and I would want this query to be performant:

t1, 2025/01/01
t2, 2024/02/03
t3, 2025/08/03

So you don't have any metric column to aggregate?

Nope in this case

How many total columns you have in the actual table?

it can have other cols like

properties

,

processedAt

or

textSearchCol

You can use ST index to have Max on time and split on all columns you need. But that is really not how ST index is supposed to be used.

And are you going to do select * on them for the latest timestamp?

for this particular case, I would not b/c I only need the latest ts

Yeah then you need to split on all the coluns, which if there are many, ST index won't be able to be performant anyway.

why would I have to split on all other cols?

Actually, I take that back, ST index won't be used if you use select *

What if I have something like:

"tableIndexConfig": {
  "starTreeIndexConfigs": [{
    "dimensionsSplitOrder": [
      "eventType"
    ],
    "skipStarNodeCreationForDimensions": [
    ],
    "functionColumnPairs": [
      "MAX__occurredAt"
    ],
    "maxLeafRecords": 10000
  }],
  ...
}

The planner won't use ST index if you have select * or select a list of columns

and my query is like : select eventType, max(occurredAt) as latestTs from table where occurredAt between 20240101 and 20250803 group by eventType

Ok, that is not select *

yeah it is not a select *

Yes, the questions I am asking are for specific reasons, it will help if answers are very specific

and it would perform better than without ST, is my understanding correct? Since it would aggregate over fewer nodes

Wait

each segment would have

eventType, max(occurredAt)

node

You have both max() as well as filter on

occurredAs

that is a good point

Yeah, this is not how ST index is typically used.

so then

select eventType, max(occurredAt) as latestTs from table where occurredAt between 20240101 and 20250803 group by eventType

would have to scan through all the matched rows to find out the max

No, you can always have range index on

occurredAt

I were to remove the

occurredAt

filter, would it perform better with ST since it would not have time based pruner

Pruning happens across segment level. If you don't have any time filter, no time pruning will have for segemnts. Within segment range index will still kick in to avoid full scan of the segment

but wouldn’t range index be applied to the entire segment but I would need to break down by eventType in this case?

you can partition on eventType yes

I guess I am still fuzzy how would the query plan be like. Can you help walk me through?

select eventType, max(occurredAt) as latestTs from table where occurredAt between 20240101 and 20250803 group by eventType

for data

1, t1, 2024/01/01
2, t2, 2024/02/03
3, t1, 2025/01/01
4, t3, 2025/08/03
5, t4, 2022/01/01

and segments are partitioned by a full date. So there would be segments for 2024/01/01, segments for

2024/02/03

. Each segment can contain rows across types

First any segment that does not contain data for time in range will be pruned out just by looking at the metadata.

Absolutely, I guess my wish was if we have seen event 4: t3, 2025/08/03, we could smartly not check other older segments and rows within those segments or maybe fewer rows/ST nodes within those segments with the help of ST

Segments are planned/processed independently/concurrently. Seeing an event in one segment doesn't tell us whether we will or won't see newer / older segments in others

yeah that is right. > maybe fewer rows/ST nodes within those segments with the help of ST and this option is not viable neither

Is this the actual query you will run in production or are you simplifying things?

select eventType, max(occurredAt) as latestTs from table where occurredAt between 20240101 and 20250803 group by eventType

this is the essence of a production query. I added occurredAt filter because I would want to leverage time pruner to reduce segments being scanned.

How many eventTypes?

could be 10 thousands

Options I see: 1. Dedicated upsert table just for this query. 2. Window Function 3. LastWithTime

yeah now I get this idea

Sure

Thank you Mayank for walking through this with me

No that is not try at all. In your case you are doing aggregating on time, most ppl aggregate on metrics

but let’s say I want to aggregate on a different metic col, would I still be able to put the filter on

occurredAt

and also have ST index kicked in?

You need to split on all columns you filter or group by on, including time

Correct, I am now not thinking about my original case but how to use ST index in general.

Let me flip the question to you, why do you think it will work?

yeah b/c it is a separate col we could think of it as a different metric col I would want to aggregation on

Well, those two columns have same value. Splitting on one means splitting on other. So ST index will not have any rows to aggregate

would a range play a role here?

I recommend looking at how ST index works. That will help you understand why this doesn't work

I did and in

Default index generation configuration

section, it says:

All dictionary-encoded Time/DateTime columns will be appended to the _dimensionsSplitOrder _following the dimensions, sorted by their cardinality in descending order. Here we assume that time columns will be included in most queries as the range filter column and/or the group by column, so for better performance, we always include them as the last elements in the dimensionsSplitOrder.

Why is ST index fast for aggregations?

since it would not need to scan through all rows to get the max

Right so it does precompute aggregations. We agree on this right?

yes

So my next question to you, how does ST index know which rows it can pre-aggregate? Or do you think it preaggregates the entire segment into 1 value?

nope, my understanding it would compute by range if the num of leave nodes is too large

Yeah that is the understand gap

Why would it need to get to the leafRecords to find the aggregation result for a range?

Just because your query asked for a range doesn't mean ST index knew that range and knows the answer, right?

right but if the node is within the asked range

but you split on occurredAt, there is no range

could you help me understand what this paragraph means:

All dictionary-encoded Time/DateTime columns will be appended to the _dimensionsSplitOrder _following the dimensions, sorted by their cardinality in descending order. Here we assume that time columns will be included in most queries as the range filter column and/or the group by column, so for better performance, we always include them as the last elements in the dimensionsSplitOrder.

I think I understand where the confusion is.

nope, just

eventType

and

occurredAt

I see

"tableIndexConfig": {
  "starTreeIndexConfigs": [{
    "dimensionsSplitOrder": [
      "eventType",
      "occurredAt"
    ],
    "skipStarNodeCreationForDimensions": [
    ],
    "functionColumnPairs": [
      "MAX__derivedOccurredAt"
    ],
    "maxLeafRecords": 1
  }],
  ...
}

this is the config I imagine

My next question, for a specifc combination of

eventType

and

occurredAt

, how many rows will be there?

I am not sure, could be 1 or N

I am assuming it is 1. And if it is 1, then ST index has now rows to pre-aggregate.

like multiple events fire at the same time but with different properties

but they will all have the same

occurredAt

value, so they are all MAX?

All dictionary-encoded Time/DateTime columns will be appended to the _dimensionsSplitOrder _following the dimensions, sorted by their cardinality in descending order. Here we assume that time columns will be included in most queries as the range filter column and/or the group by column, so for better performance, we always include them as the last elements in the dimensionsSplitOrder.

I think I am still trying to grab the range filter aspect from the above paragraph

Let's stick to your example

If ST index has a single node for each row, then I agree with you it is not efficient

It is not effective at all (not not efficient)

right

That's all I am trying to say 😅

Right, that’s why I think if the default config would include a time col

Default example assumes metric columns are being aggregated. You are aggregating on time column

How would default example get aggregated if it was to split on

occurredAt

You are mixing default / documentation with your requirement.

Can we forget my case for a moment

And in your case also, this is happening because you are trying to fit ST index where it doesn't belong

yeah will you be available next week?

i am available now if you are

yeah

Summarizing the results: 1. In this case, splitting on

occurredAt

will by construction prevent pre-aggregation, as I mentioned above. 2. Solution suggested for this case was to create a derived column with lower time granularity (eg days) and use that in the query and split on that in the ST index. 3. This approach allows for multiple rows to be aggregated across 1 day for the same eventType, helping with performance. 4. Independently, we discussed columnValue based pruner to further optimize performance by pruning segments based on the new derived column (for which time pruner won't take effect).

It seems

ColumnValueSegmentPruner

is enabled by default for servers

Yes