Elizabeth Garrett Christensen：Postgres COUNT(DISTINCT) 太慢？快速近似查询指南
内容提要
本文介绍了PostgreSQL中用于大数据集近似查询的多种技术，包括TABLESAMPLE采样、HyperLogLog（HLL）和DataSketches扩展（如CPC、Theta、KLL和Frequent Strings）。通过10万行测试数据，比较了各方法在计数、百分位数和去重上的性能与精度，并展示了预聚合回填表如何实现毫秒级查询，同时提醒财务等场景需用精确计算。
延伸解读
何时该用近似查询
文章指出，近似查询并非万能。对于财务报告、账单、合规等场景，必须使用精确计算。此外，当数据量小于约100万行时，精确聚合通常足够快，无需引入近似方法。若需要查询具体行或进行连接操作，近似结果也无法满足需求。因此，选择近似查询前，应评估业务场景对精度的要求。
采样与概率算法的取舍
TABLESAMPLE采样简单易用，但存在偏差风险，尤其当数据物理聚集时。对于去重计数，直接按比例放大采样结果会严重高估，因为高频用户几乎必然出现在样本中。相比之下，HyperLogLog和DataSketches等概率算法提供更稳定的估计，且支持合并，适合预聚合场景。选择时需权衡实现复杂度与精度需求。
预聚合架构的威力
文章展示了预聚合回填表的巨大优势：将原始数据按天、渠道等维度预先计算成紧凑的草图，查询时只需合并少量草图，即可在毫秒级返回结果。例如，7天唯一用户数从329毫秒降至4毫秒，p99延迟按渠道查询从3.9秒降至22毫秒。这种模式尤其适合需要频繁刷新的大数据仪表盘。
Q&A
PostgreSQL中如何快速估算平均值和百分位数？
可以使用TABLESAMPLE子句对表进行采样，例如`SELECT avg(response_ms) FROM page_views TABLESAMPLE SYSTEM(10);`，这样只需读取部分数据即可获得近似结果。对于百分位数，也可以对采样数据使用`percentile_cont`，例如`SELECT percentile_cont(0.99) WITHIN GROUP (ORDER BY response_ms) FROM page_views TABLESAMPLE SYSTEM(10);`。在测试中，10%的采样将平均值的查询时间从352ms降至111ms，p99的查询时间从2617ms降至250ms，且误差很小。
PostgreSQL中TABLESAMPLE的SYSTEM和BERNOULLI方法有什么区别？
SYSTEM方法按数据页（8KB）进行随机采样，速度快但可能因数据物理聚集而产生偏差；BERNOULLI方法逐行独立判断是否保留，样本更均匀但速度较慢。例如，在10%采样下，SYSTEM的count(DISTINCT user_id)耗时217ms，而BERNOULLI耗时404ms。
为什么用TABLESAMPLE估算去重计数时乘以10会严重高估？
因为采样是随机的，一个用户如果在全表出现多次，即使只采样10%，也很可能至少被采样到一次，导致几乎所有用户都会出现在样本中。因此，将样本中的去重计数乘以10会严重高估真实值。例如，测试中10%样本的去重计数乘以10得到415万，而实际只有50万。
PostgreSQL中如何使用HyperLogLog进行快速去重计数？
需要安装hll扩展，然后使用`hll_add_agg`和`hll_hash_integer`等函数。例如：`SELECT hll_cardinality(hll_add_agg(hll_hash_integer(user_id)))::int AS unique_users FROM page_views;`。在测试中，该方法耗时320ms，而精确的COUNT(DISTINCT)耗时671ms，误差约0.6%。HLL的另一个优势是草图可合并，可以按天构建草图，然后通过`hll_union_agg`合并，实现毫秒级查询。
PostgreSQL中如何用DataSketches的Theta草图计算两个渠道的用户重叠？
使用datasketches扩展，先按渠道构建Theta草图，然后使用`theta_sketch_intersection`计算交集。例如：`SELECT theta_sketch_get_estimate(theta_sketch_intersection((SELECT sketch FROM channel_sketches WHERE channel = 'google'), (SELECT sketch FROM channel_sketches WHERE channel = 'facebook')))::int AS users_on_both FROM ...;`。在测试中，该方法耗时382ms，而精确的自连接查询耗时2499ms。
PostgreSQL中如何用KLL草图快速估算百分位数？
使用datasketches扩展，通过`kll_float_sketch_build`构建草图，然后用`kll_float_sketch_get_quantile`获取分位数。例如：`SELECT kll_float_sketch_get_quantile(kll_float_sketch_build(response_ms::real), 0.99)::numeric(8,2) AS p99 FROM page_views;`。在测试中，该方法耗时625ms，而精确的`percentile_cont`耗时2617ms，误差约7%。KLL草图也可合并，适合预聚合。
PostgreSQL中如何用Frequent Strings草图查找最常出现的值？
使用datasketches扩展，通过`frequent_strings_sketch_build`构建草图，然后用`frequent_strings_sketch_result_no_false_negatives`获取结果。例如：`SELECT frequent_strings_sketch_result_no_false_negatives(frequent_strings_sketch_build(7, page_path), 50000) FROM page_views;`。在单次全表扫描时，草图可能比GROUP BY稍慢，但预聚合后合并草图可以大幅提升性能，例如在30天查询中从1027ms降至1ms。
PostgreSQL中如何通过预聚合回填表实现毫秒级查询？
可以创建一个回填表，按天、渠道、地区等维度预聚合草图，例如：`CREATE TABLE analytics_rollup AS SELECT date_trunc('day', event_time)::date AS event_date, channel, region, cpc_sketch_build(user_id) AS users_sketch, theta_sketch_build(user_id) AS theta_users, kll_float_sketch_build(response_ms::real) AS latency_sketch, count(*) AS total_events, sum(cost_cents) AS total_cost_cents FROM page_views GROUP BY 1, 2, 3;`。这样查询时只需扫描回填表，例如7天唯一用户查询从329ms降至4ms，p99按渠道查询从3932ms降至22ms。
PostgreSQL中草图如何减少内存和磁盘开销？
草图的内存占用是固定的，与数据量无关。例如，CPC草图无论处理50万还是5亿个不同值，都只使用2-4KB内存；KLL草图处理1000万或100亿个值也只用4-8KB。相比之下，精确的COUNT(DISTINCT)需要构建哈希表，`percentile_cont`需要排序，当超过work_mem时会导致磁盘临时文件，性能急剧下降。草图避免了这些问题。
PostgreSQL中如何保持回填表更新？
可以使用物化视图，通过`REFRESH MATERIALIZED VIEW CONCURRENTLY`定期重建，但会全表重扫。或者使用pg_incremental扩展进行增量处理，只处理新到达的行，例如通过`incremental.create_time_interval_pipeline`定义管道。草图是可加的，因此可以按天构建草图并合并，无需重扫历史数据。
