MATERIALIZED VIEW VS query with ALLOW FILTERING

Question

I have the following table users((username), last_seen) where last_seen is basically the writetime . Number of records in the table is aprox 10 million. Insert is pretty much straightforward insert into users (username, last_seen) VALUES ([username], now)

BUT I need to query by the last_seen column (this query runs every minute) e.g :

select username from users where last_seen < (now - 1 day) 

I have two options as I see it:

1. use materialized view :

   CREATE MATERIALIZED VIEW users_last_seen AS
   SELECT last_seen, username
   FROM users
   WHERE last_seen IS NOT NULL
   PRIMARY KEY (last_seen, username);
   

   and simply query

   select username from users_last_seen where last_seen < (now - 1 day)
   

2. query the users table

   select username from users where last_seen < (now - 1 day) ALLOW FILTERING
   

which one is more efficient? AFAIK materialized view is unstable and have impact on performance.

Answer 1

I found out that using SASI Index is the best option in this situation