Index Strategy by Query Category

Cohort Analysis

-- Core indexes for cohort queries
CREATE INDEX idx_users_signup_date ON users(signup_date);
CREATE INDEX idx_events_user_ts ON events(user_id, event_timestamp);
CREATE INDEX idx_events_name_user ON events(event_name, user_id, event_timestamp);

Why: Cohort queries always filter by signup_date and then join to events by user_id + time range. The compound index on events prevents a full table scan on the fact table.

Funnel Metrics

-- Funnel queries need fast lookup by event_name within time windows
CREATE INDEX idx_events_name_ts_user ON events(event_name, event_timestamp, user_id);
CREATE INDEX idx_events_session_ts ON events(session_id, event_timestamp);

Why: Funnel queries filter by event_name first, then by time window. Covering the user_id in the index avoids table lookups.

Retention

-- Retention checks "was user X active on day Y?"
CREATE INDEX idx_events_user_date ON events(user_id, DATE(event_timestamp));
-- Postgres: use expression index
CREATE INDEX idx_events_user_event_date ON events(user_id, (event_timestamp::date));

Revenue

CREATE INDEX idx_orders_user_date_status ON orders(user_id, order_date, status);
CREATE INDEX idx_orders_date_status ON orders(order_date, status) INCLUDE (total_amount, user_id);
CREATE INDEX idx_subs_status_period ON subscriptions(status, current_period_start, current_period_end);

Operational (Support Tickets)

CREATE INDEX idx_tickets_created_priority ON support_tickets(created_at, priority);
CREATE INDEX idx_tickets_status_priority ON support_tickets(status, priority, created_at);
CREATE INDEX idx_tickets_assigned ON support_tickets(assigned_to, resolved_at);

Included with Advanced SQL Query Library

Ready for the complete Advanced SQL Query Library?