主页 / dba / 问题 / 249969
Accepted
Morris de Oryx
Asked: 2019-10-01 16:04:06 +0800 CST

改进 Postgres 中的不同值估计

  • 772

Postgres 中的完整计数可能很慢，原因已被充分理解和讨论。因此，我一直在尽可能使用估算技术。对于行，pg_stats 似乎很好，对于视图，提取工作返回的估计值EXPLAIN。

https://www.cybertec-postgresql.com/en/count-made-fast/

但是不同的价值观呢？在这里，我的运气要差得多。有时估计值是 100% 正确的，有时它们会相差 2 或 20 倍。截断的表似乎特别是过时的估计值（？）。

我刚刚运行了这个测试并提供了一些结果：

analyze assembly_prods; -- Doing an ANLYZE to give pg_stats every help.

select 'count(*) distinct' as method,
        count(*) as count
from (select distinct assembly_id 
      from assembly_prods) d 
union all
select 'n_distinct from pg_stats' as method,
        n_distinct as count
from pg_stats 
where tablename  = 'assembly_prods' and
      attname    = 'assembly_id';

结果：

method                      count
count(*) distinct           28088
n_distinct from pg_stats    13805

这只差了 2 倍，但我的数据似乎更糟。到我不会使用估计的地步。还有什么我可以尝试的吗？这是PG 12改进的东西吗？

跟进

我以前从未尝试SET STATISTICS过，因为一天只有那么多小时。受 Laurenz 回答的启发，我快速浏览了一下。这是文档中的有用评论：

https://www.postgresql.org/docs/current/planner-stats.html

pg_statistic存储在by中的信息量ANALYZE，特别是每列的和 histogram_bounds 数组中的最大条目数most_common_vals，可以使用命令逐列设置，或通过设置配置变量ALTER TABLE SET STATISTICS全局设置。default_statistics_target默认限制目前为 100 个条目。提高限制可能会允许更准确的规划器估计，特别是对于具有不规则数据分布的列，代价是消耗更多空间pg_statistic和稍微更多的时间来计算估计。相反，对于具有简单数据分布的列，下限可能就足够了。

我经常得到包含一些常见值和许多罕见值的表。或者反过来，因此正确的阈值将取决于。对于那些没有使用过的人SET STATISTICS，它可以让您将采样率设置为目标条目数。默认值为 100，因此 1000 的保真度应该更高。这是它的样子：

ALTER TABLE assembly_prods 
    ALTER COLUMN assembly_id
    SET STATISTICS 1000;

您可以SET STATISTICS在表或索引上使用。这是一篇关于索引的有趣文章：

https://akorotkov.github.io/blog/2017/05/31/alter-index-weird/

请注意，当前文档确实列出SET STATISTICS了索引。

因此，我尝试了 1、10、100、1000 和 10,000 的阈值，并从具有 467,767 行和 28,088 个不同值的表中得到这些结果：

Target   Estimate  Difference  Missing
     1   13,657    14,431      51%
    10   13,867    14,221      51%
   100   13,759    14,329      51%
 1,000   24,746     3,342      12%
10,000   28,088         0       0%

显然，您无法从一个案例中得出任何一般性结论，但SET STATISTICS看起来非常有用，我很乐意将其牢记在心。我很想总体上提高一点目标，因为我怀疑这对我们系统中的许多情况都有帮助。

postgresql distinct

1 个回答

  1. Best Answer

    首先，请注意：您的查询可以写得更简单

    SELECT count(DISTINCT assembly_id) FROM assembly_prods;

    另外，您的统计查询是错误的，因为n_distict也可能是负数。你应该查询：

    SELECT CASE WHEN s.n_distinct < 0
            THEN - s.n_distinct * t.reltuples
            ELSE s.n_distinct
       END AS n_distinct
FROM pg_class t
   JOIN pg_namespace n ON n.oid = t.relnamespace
   JOIN pg_stats s ON t.relname = s.tablename
                      AND n.nspname = s.schemaname
WHERE s.schemaname = 'public'
  AND s.tablename = 'assembly_prods'
  AND s.attname = 'assembly_id';

    对于像这样的简单查询，统计信息应该包含一个很好的估计。

    如果估计不对，请尝试ANALYZE上桌。这也将修复新TRUNCATEd 表的结果。TRUNCATE不会导致 PostgreSQL 自动分析表（这里可能有改进的空间）。

    如果这改善了结果，请查看通过配置更频繁地分析表

    ALTER TABLE assembly_prods SET (autovacuum_analyze_scale_factor = 0.05);

    也可以设置autovacuum_analyze_scale_factor为 0 并提高autovacuum_analyze_threshold到表的每日变化率。

    如果ANALYZE单独不改善估计，增加样本的大小：

    ALTER TABLE assembly_prods ALTER assembly_id SET STATISTICS 1000;

    一个新的ANALYZE现在应该产生更好的估计。

    为更复杂的查询获得良好的n_distinct估计变得越来越困难。有时扩展的统计数据会大大改善估计。

    据我所知，PostgreSQL v12 在这方面没有带来任何改进。

    • 5

相关问题