I have a table on QuestDB with user orders. It is more complex than this, but this would be the basic schema.
create table exchanges(
timestamp timestamp,
uid long,
amount double
) timestamp(timestamp) partition by DAY;
Now, I want to run a query showing the last 100 orders from a user, which would seem straight forward
SELECT * from EXCHANGE WHERE uid = 1001 ORDER BY timestamp DESC LIMIT 10;
The problem with this query is that it is slow as dataset keeps growing. At the moment with only 250 million rows I can already feel it getting slower than before.
I know in QuestDB it is best to always filter by a time-range, so the database can prune partitions and read only data from some time ranges. My problem here is that not all the users are very active, so it might be the case that for some users I have to go several months, or even years, back to find 100 orders, while for other users 100 orders might be in just a single week of data.
Any ideas to make this more efficient?
A potential solution would be using materialized views to keep activity summaries for the users, then using that table to find the maximum date range I need to query for the user.
create materialized view uid_weekly_orders AS (
select timestamp, uid, min(timestamp) as min_timestamp, count() as orders from exchanges
sample by 1w
) partition by week;
This query will store 1 record for each user and week, for users making any orders, keeping the total number of orders and the earliest timestamp for an order for that user within the week.
Now we can run a query using that materialized view to do a running sum by reverse timestamp, so we can find the week in which we already have at least 100 orders for a user, then we use that timestamp as the condition to SELECT from the main table.
DECLARE @user := 1001
with user_orders as (
select min_timestamp, sum(orders) over (order by timestamp DESC) as total from uid_weekly_orders
where uid = @user order by timestamp desc
)
select * from exchanges where uid=@user and timestamp >= (select min_timestamp from user_orders where total > 100 LIMIT 1);