Covering Index for Analytics Dashboard

advanced

Analytics dashboard shows sales by region and product category. Query aggregates 50M orders and takes 45 seconds. Users complain dashboard is unusable. Optimize to under 1 second.

-- BEFORE: Slow query (45 seconds)

-- Problem: No index, full table scan + sort

SELECT

region,

category,

COUNT(*) as order_count,

SUM(amount) as total_revenue,

AVG(amount) as avg_order_value

FROM orders

WHERE order_date >= '2024-01-01'

GROUP BY region, category

ORDER BY total_revenue DESC;

-- Execution plan:

-- 1. Seq Scan on orders (50M rows) - 30s

-- 2. Filter by date (reduces to 10M rows) - 5s

-- 3. Sort by region, category - 8s

-- 4. Aggregate - 2s

-- Total: 45 seconds

-- SOLUTION 1: Add index on GROUP BY columns (15x faster)

CREATE INDEX idx_region_category

ON orders(region, category);

-- Now takes 3 seconds

-- Index provides pre-sorted data, eliminates sort step

-- SOLUTION 2: Covering index with all columns (45x faster)

CREATE INDEX idx_orders_analytics

ON orders(region, category, order_date, amount);

SELECT

region,

category,

COUNT(*) as order_count,

SUM(amount) as total_revenue,

AVG(amount) as avg_order_value

FROM orders

WHERE order_date >= '2024-01-01'

GROUP BY region, category

ORDER BY total_revenue DESC;

-- Execution plan: