Say I have a table with a thousand users and 50 million user_actions. A few users have more than a million actions but most have thousands.
CREATE TABLE users (id, name)
CREATE TABLE user_actions (id, user_id, created_at)
CREATE INDEX index_user_actions_on_user_id ON user_actions(user_id)
Querying user_actions by user_id is fast, using the index.
SELECT *
FROM user_actions
WHERE user_id = ?
LIMIT 1
But I'd like to know the last action by a user.
SELECT *
FROM user_actions
WHERE user_id = ?
ORDER BY created_at DESC
LIMIT 1
This query throws out the index and does a table scan, backwards until it finds an action. Not a problem for users that have been active recently, too slow for users that haven't.
Is there a way to tune this index so postgres keeps track of the last action by each user? (For bonus points the last N actions!)
Or, suggested alternate strategies? I suppose a materialized view of a window function will do the trick.
