Hey there! Let's work through getting that slow query back on track—generating week-over-week (WoW) comparisons for your top 10 purchasing cities shouldn't trigger timeouts. Here are actionable optimizations tailored to your scenario:

1. Narrow Down Data Early: Filter Top Cities First #

The biggest mistake in slow aggregate queries often is processing more data than needed. Instead of calculating WoW for all cities then filtering to top 10, first identify your top cities, then only compute WoW for those 10. This cuts down your dataset drastically.

Example Optimized Query: #

-- Step 1: Grab the top 10 cities by total purchases (adjust time range as needed)
WITH top_cities AS (
  SELECT 
    city,
    SUM(purchase_quantity) AS total_purchases
  FROM purchases
  WHERE purchase_date >= CURRENT_DATE - INTERVAL '12 weeks' -- Use a relevant window to determine top cities
  GROUP BY city
  ORDER BY total_purchases DESC
  LIMIT 10
),
-- Step 2: Only compute weekly totals for these top 10 cities (last 2 weeks for WoW)
weekly_totals AS (
  SELECT 
    p.city,
    DATE_TRUNC('week', p.purchase_date)::DATE AS week_start,
    SUM(p.purchase_quantity) AS total_qty
  FROM purchases p
  INNER JOIN top_cities tc ON p.city = tc.city
  WHERE p.purchase_date >= CURRENT_DATE - INTERVAL '2 weeks' -- Only pull data needed for WoW
  GROUP BY p.city, week_start
)
-- Step 3: Calculate WoW comparison
SELECT
  curr.city,
  curr.week_start AS current_week,
  curr.total_qty AS current_week_qty,
  COALESCE(prev.total_qty, 0) AS previous_week_qty,
  -- Handle division by zero gracefully
  CASE 
    WHEN prev.total_qty = 0 THEN NULL 
    ELSE ROUND(((curr.total_qty - prev.total_qty)::FLOAT / prev.total_qty) * 100, 2) 
  END AS wow_growth_pct
FROM weekly_totals curr
LEFT JOIN weekly_totals prev
  ON curr.city = prev.city
  AND curr.week_start = prev.week_start + INTERVAL '1 week'
WHERE curr.week_start = CURRENT_DATE - INTERVAL '1 week' -- Target most recent week
ORDER BY curr.total_qty DESC;

2. Add Targeted Indexes to Avoid Full Table Scans #

Indexes are your best friend for speeding up aggregate queries. Create a composite index that covers the fields you filter, group, and aggregate on:

-- For most databases (PostgreSQL, MySQL 8.0+):
CREATE INDEX idx_purchases_city_date_qty ON purchases (city, purchase_date) INCLUDE (purchase_quantity);

-- If your database supports function-based indexes (e.g., PostgreSQL), add this for faster week grouping:
CREATE INDEX idx_purchases_city_week ON purchases (city, DATE_TRUNC('week', purchase_date)) INCLUDE (purchase_quantity);

This index lets the database pull all needed data directly from the index (no "table lookups" required), which drastically reduces query time.

3. Pre-Aggregate Data with a Summary Table #

If you run this query frequently, pre-computing weekly totals into a summary table will make your query near-instant. Here's how to set it up:

Step 1: Create the Summary Table #

CREATE TABLE city_weekly_summary (
  city VARCHAR(100) NOT NULL,
  week_start DATE NOT NULL,
  total_purchases BIGINT NOT NULL,
  PRIMARY KEY (city, week_start)
);

Step 2: Refresh the Table (Schedule this weekly, e.g., via cron or database job) #

TRUNCATE TABLE city_weekly_summary;
INSERT INTO city_weekly_summary
SELECT 
  city,
  DATE_TRUNC('week', purchase_date)::DATE AS week_start,
  SUM(purchase_quantity) AS total_purchases
FROM purchases
GROUP BY city, week_start;

Step 3: Query the Summary Table #

Now your WoW query becomes trivial:

WITH top_cities AS (
  SELECT city
  FROM city_weekly_summary
  WHERE week_start >= CURRENT_DATE - INTERVAL '12 weeks'
  GROUP BY city
  ORDER BY SUM(total_purchases) DESC
  LIMIT 10
)
SELECT
  curr.city,
  curr.week_start AS current_week,
  curr.total_purchases AS current_qty,
  COALESCE(prev.total_purchases, 0) AS previous_qty,
  CASE 
    WHEN prev.total_purchases = 0 THEN NULL 
    ELSE ROUND(((curr.total_purchases - prev.total_purchases)::FLOAT / prev.total_purchases) * 100, 2) 
  END AS wow_growth_pct
FROM city_weekly_summary curr
INNER JOIN top_cities tc ON curr.city = tc.city
LEFT JOIN city_weekly_summary prev
  ON curr.city = prev.city
  AND curr.week_start = prev.week_start + INTERVAL '1 week'
WHERE curr.week_start = CURRENT_DATE - INTERVAL '1 week'
ORDER BY curr.total_purchases DESC;

4. Tune Database Configuration (Quick Wins) #

• Increase sort memory: If your query is spending time sorting on disk (check via EXPLAIN ANALYZE), temporarily boost the sort memory limit. For PostgreSQL: SET work_mem = '64MB'; (adjust based on your server's RAM).
• Analyze your table: Make sure the database has up-to-date statistics so it can choose the best execution plan: ANALYZE purchases;

5. Verify with Execution Plans #

Always run EXPLAIN ANALYZE on your query to see where the bottlenecks are. Look for:

• Seq Scan (full table scan) → means your indexes aren't being used.
• Sort Method: External Merge Disk: → means sorting is hitting disk, increase work_mem.
• High Rows values in intermediate steps → means you're processing too much data early on.

内容的提问来源于stack exchange，提问作者skutik