Indexing Materialized Views — Accelerating Pre-Aggregated Data

A materialized view is stored like a table, so it supports the same indexes: B-tree, GIN, partial, composite, covering. This is the key advantage over regular views — you can index the aggregated output, not just the base tables.

DROP MATERIALIZED VIEW IF EXISTS mv_daily_hashtag_stats CASCADE;

-- Build a heavier materialized view: daily hashtag performance
CREATE MATERIALIZED VIEW IF NOT EXISTS mv_daily_hashtag_stats AS
SELECT
    h.tag,
    DATE_TRUNC('day', t.created_at)::DATE                              AS stat_date,
    COUNT(t.id)                                     AS tweet_count,
    COUNT(DISTINCT t.user_id)                       AS unique_users,
    SUM(t.like_count)                               AS total_likes,
    SUM(t.retweet_count)                            AS total_retweets,
    SUM(t.impression_count)                         AS total_impressions,
    ROUND(100.0 * SUM(t.like_count + t.retweet_count)
          / NULLIF(SUM(t.impression_count), 0), 4)  AS engagement_rate,
    MAX(t.like_count)                               AS peak_tweet_likes
FROM tw_hashtags h
JOIN tw_tweet_hashtags th ON th.hashtag_id = h.id
JOIN tw_tweets t          ON t.id = th.tweet_id
GROUP BY h.tag, DATE_TRUNC('day', t.created_at)::DATE
WITH DATA;

-- Unique index (required for CONCURRENT refresh)
CREATE UNIQUE INDEX IF NOT EXISTS idx_mvdhs_tag_date ON mv_daily_hashtag_stats (tag, stat_date);

-- Additional indexes for common query patterns
CREATE INDEX IF NOT EXISTS idx_mvdhs_stat_date ON mv_daily_hashtag_stats (stat_date DESC);
CREATE INDEX IF NOT EXISTS idx_mvdhs_likes ON mv_daily_hashtag_stats (total_likes DESC);

-- Partial index: only recent data (last 30 days queried most)
CREATE INDEX IF NOT EXISTS idx_mvdhs_recent ON mv_daily_hashtag_stats (tag, stat_date)
    WHERE stat_date >= '2024-01-01';  -- partial index for recent data