PostgreSQL大表查询性能优化求助：慢查询超时与历史数据处理

Hey there, let's tackle this performance headache step by step. The core issue here is that you're doing a full-table scan on 95 million rows to compute all those aggregations in real-time—no wonder it's timing out. Your idea of using cron jobs for incremental pre-aggregation is spot-on; let's break down how to handle the historical data and set up the ongoing pipeline.

1. First: Build a Pre-Aggregation Table for Historical + Incremental Data #

We need a dedicated table to store pre-computed aggregates so we don't hammer the massive aff_ref table every time. Design it to support daily granularity (for easy incremental updates) and flexible rollups to monthly/other time ranges.

Create the Pre-Aggregation Table #

CREATE TABLE aff_ref_agg (
    day DATE NOT NULL,
    site_id INTEGER NOT NULL,
    affiliate_id INTEGER NOT NULL,
    -- Core sum columns to support your final metric calculations
    sum_onsite BIGINT,
    sum_sessions INTEGER,
    sum_bounce_count INTEGER,
    sum_bounce_desktop DOUBLE PRECISION,
    sum_bounce_mobile DOUBLE PRECISION,
    sum_bounce_tablet DOUBLE PRECISION,
    sum_uniques_desktop INTEGER,
    sum_uniques_mobile_phone INTEGER,
    sum_uniques_tablet INTEGER,
    sum_quality_3 INTEGER,
    sum_quality_2 INTEGER,
    sum_quality_1 INTEGER,
    sum_amount DOUBLE PRECISION,
    sum_clicks INTEGER,
    sum_add_par_1 INTEGER,
    sum_add_par_3 INTEGER,
    sum_uniques INTEGER,
    sum_money_bonus DOUBLE PRECISION,
    sum_money_volume DOUBLE PRECISION,
    PRIMARY KEY (day, site_id, affiliate_id)
);

Note: Storing raw sums instead of pre-calculated ratios/percentages gives you flexibility to tweak metrics later without reprocessing all historical data.

Backfill Historical Data (The 95 Million Rows) #

Trying to process all data in one go will likely time out, so we'll batch it by month to avoid locking the table for too long:

DO $$
DECLARE
    start_date DATE := '2013-12-14';
    end_date DATE := '2018-01-20';
    current_month DATE;
BEGIN
    current_month := date_trunc('month', start_date)::DATE;
    WHILE current_month <= end_date LOOP
        INSERT INTO aff_ref_agg (day, site_id, affiliate_id, sum_onsite, sum_sessions, sum_bounce_count, sum_bounce_desktop, sum_bounce_mobile, sum_bounce_tablet, sum_uniques_desktop, sum_uniques_mobile_phone, sum_uniques_tablet, sum_quality_3, sum_quality_2, sum_quality_1, sum_amount, sum_clicks, sum_add_par_1, sum_add_par_3, sum_uniques, sum_money_bonus, sum_money_volume)
        SELECT 
            day,
            site_id,
            affiliate_id,
            SUM(onsite) AS sum_onsite,
            SUM(sessions) AS sum_sessions,
            SUM(bounce_count) AS sum_bounce_count,
            SUM(bounce_desktop) AS sum_bounce_desktop,
            SUM(bounce_mobile) AS sum_bounce_mobile,
            SUM(bounce_tablet) AS sum_bounce_tablet,
            SUM(uniques_desktop) AS sum_uniques_desktop,
            SUM(uniques_mobile_phone) AS sum_uniques_mobile_phone,
            SUM(uniques_tablet) AS sum_uniques_tablet,
            SUM(quality_3) AS sum_quality_3,
            SUM(quality_2) AS sum_quality_2,
            SUM(quality_1) AS sum_quality_1,
            SUM(amount) AS sum_amount,
            SUM(clicks) AS sum_clicks,
            SUM(add_par_1) AS sum_add_par_1,
            SUM(add_par_3) AS sum_add_par_3,
            SUM(uniques) AS sum_uniques,
            SUM(money_bonus) AS sum_money_bonus,
            SUM(money_volume) AS sum_money_volume
        FROM aff_ref t
        -- Skip this LEFT JOIN if it doesn't filter rows (your original query doesn't use ad columns!)
        -- LEFT JOIN affiliate_domains ad ON ad.domain = t.referer AND ad.affiliate_id = t.affiliate_id
        WHERE day >= current_month 
          AND day < (current_month + INTERVAL '1 month')::DATE
        GROUP BY day, site_id, affiliate_id;
        
        current_month := current_month + INTERVAL '1 month'::DATE;
        COMMIT; -- Commit each batch to free up resources
    END LOOP;
END $$;

Critical Check: That LEFT JOIN affiliate_domains adds unnecessary overhead since your query doesn't filter on any columns from affiliate_domains. Remove it entirely unless you need to exclude rows based on domain status.

2. Set Up Incremental Cron Job for Daily Data #

Once historical data is backfilled, set up a daily cron job (e.g., run at 2 AM) to process the previous day's new data:

INSERT INTO aff_ref_agg (day, site_id, affiliate_id, sum_onsite, sum_sessions, sum_bounce_count, sum_bounce_desktop, sum_bounce_mobile, sum_bounce_tablet, sum_uniques_desktop, sum_uniques_mobile_phone, sum_uniques_tablet, sum_quality_3, sum_quality_2, sum_quality_1, sum_amount, sum_clicks, sum_add_par_1, sum_add_par_3, sum_uniques, sum_money_bonus, sum_money_volume)
SELECT 
    day,
    site_id,
    affiliate_id,
    SUM(onsite) AS sum_onsite,
    SUM(sessions) AS sum_sessions,
    SUM(bounce_count) AS sum_bounce_count,
    SUM(bounce_desktop) AS sum_bounce_desktop,
    SUM(bounce_mobile) AS sum_bounce_mobile,
    SUM(bounce_tablet) AS sum_bounce_tablet,
    SUM(uniques_desktop) AS sum_uniques_desktop,
    SUM(uniques_mobile_phone) AS sum_uniques_mobile_phone,
    SUM(uniques_tablet) AS sum_uniques_tablet,
    SUM(quality_3) AS sum_quality_3,
    SUM(quality_2) AS sum_quality_2,
    SUM(quality_1) AS sum_quality_1,
    SUM(amount) AS sum_amount,
    SUM(clicks) AS sum_clicks,
    SUM(add_par_1) AS sum_add_par_1,
    SUM(add_par_3) AS sum_add_par_3,
    SUM(uniques) AS sum_uniques,
    SUM(money_bonus) AS sum_money_bonus,
    SUM(money_volume) AS sum_money_volume
FROM aff_ref t
-- Skip join if unnecessary
-- LEFT JOIN affiliate_domains ad ON ad.domain = t.referer AND ad.affiliate_id = t.affiliate_id
WHERE day = CURRENT_DATE - INTERVAL '1 day'
GROUP BY day, site_id, affiliate_id
ON CONFLICT (day, site_id, affiliate_id) DO UPDATE 
SET sum_onsite = EXCLUDED.sum_onsite,
    sum_sessions = EXCLUDED.sum_sessions,
    sum_bounce_count = EXCLUDED.sum_bounce_count,
    -- Update all other sum columns here
    sum_money_volume = EXCLUDED.sum_money_volume;

The ON CONFLICT clause handles late-arriving data that might be added after the cron job runs.

3. Query the Pre-Aggregated Data (Fast!) #

Now you can query the tiny aff_ref_agg table instead of the 50GB aff_ref table—this will run in milliseconds.

Example: Monthly Grouped Stats (With Pagination) #

-- Get total count for pagination
SELECT COUNT(DISTINCT date_trunc('month', day)) AS total_months
FROM aff_ref_agg
WHERE day >= '2013-12-14' AND day <= '2018-01-20';

-- Paginated monthly stats
SELECT 
    date_trunc('month', day)::DATE AS month_start,
    (SUM(sum_onsite) / NULLIF(SUM(sum_sessions), 0)) AS sumonsite,
    SUM(sum_onsite) AS sum_onsite,
    SUM(sum_bounce_count) AS bounce_count,
    (SUM(sum_bounce_count)::FLOAT / NULLIF(SUM(sum_sessions), 0)) * 100 AS sumbounce,
    100 * SUM(sum_uniques_mobile_phone)::FLOAT / SUM(sum_uniques)::FLOAT AS uniques_mobile_phone,
    -- Add all your other computed metrics here
    SUM(sum_amount) AS sumamount
FROM aff_ref_agg
WHERE day >= '2013-12-14' AND day <= '2018-01-20'
GROUP BY date_trunc('month', day)
ORDER BY month_start
LIMIT 10 OFFSET 0; -- Adjust OFFSET for pagination

4. Bonus Optimizations #

• Drop Duplicate Indexes: You have two identical ref_sites_day_aff_stype_idx indexes—drop one to save storage and write overhead.
• Partition the Original aff_ref Table: If you ever need ad-hoc raw data queries, partitioning by day (monthly/yearly) will drastically reduce scan times.
• Materialized Views (Alternative to Cron): If you prefer not to manage cron jobs, use a materialized view with incremental refreshes. Cron gives you more control over timing and resource usage though.

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