Aggregation in SQL -如何加速查询

Question

我有疑问，因为我想在我的查询中按月计算所有指标的总和，但加载数据的时间太长，有另一种方法来加速此查询-意味着更快地返回数据？

SELECT
    to_char(date, 'YYYY-MM') AS date_format_m,
    /*  to_char(cd.date, 'YYYY-MM-DD') AS date_format_d, */
    client_id,
    sum(impressions) as impressions,
    sum(visits) as visits,
    sum(conversions) as conversions,
    sum(clicks) as clicks,
    sum(tracked_cost) as tracked_cost,
    sum(tracked_revenue) as tracked_revenue,
    sum(conversions) as conversions,
    sum(number_of_reports_queries) as report_queries,
    sum(errors_total) as errors_total,
    sum(traffic_log_requests) as traffic_log_requests,
    sum(conversions) as conversions,
    sum(campaigns_with_traffic) as campaigns_with_traffic,
    sum(flows_with_traffic) as flows_with_traffic,
    sum(paths_with_traffic) as paths_with_traffic,
    sum(landers_with_traffic) as landers_with_traffic,
    sum(offers_with_traffic) as offers_with_traffic
FROM 
    client_data
WHERE
    date BETWEEN CURRENT_DATE - INTERVAL '180' DAY AND CURRENT_DATE
    AND long_term_remaining_days > 0
GROUP BY 
    date_format_m, client_id

Answer 1

您的查询可能需要处理大量数据，这就是它花费这么长时间的原因。当然，还有其他可能导致查询变慢的原因，例如资源争用。

对于给定的查询，我认为client_data(date, long_term_remaining_days)上的索引可能是查询的最佳索引。