PostgreSQL 教程: 优化空值的估计数量

五月 17, 2024

摘要:在本教程中,您将学习如何优化空值的估计数量。

目录

介绍

虽然 NULL 值被技术纯粹主义者看不起,但它可以方便地表示未知或不存在的值。

但是特殊值需要特殊对待。在使用 NULL 值时,需要记住一些实际的注意事项。布尔逻辑变成了三种值,NOT IN结构会开始变得奇怪。目前还没有定义 NULL 值是否被认为低于或高于常规值(特殊子句NULLS FIRSTNULLS LAST有助于解决这一问题)。在聚合函数中使用 NULL 值也是粗略的。因为 NULL 值实际上根本不是值,所以规划器需要额外的数据来容纳它们。

除了基本的关系级统计信息之外,分析器还会收集关系中每一列的统计信息。这些数据存储在系统表pg_statistic中,它可以使用pg_stats视图方便地显示。

空值的占比属于列级统计信息。它对应于pg_stats中的null_frac属性。

示例

让我们开始设置一个小的测试用例:

CREATE TABLE test2 (id integer, str text);

INSERT INTO test2 (id, str)
  SELECT i,
       CASE WHEN i % 24 = 1 THEN NULL
            ELSE repeat('x', 64)
       END AS str
    FROM generate_series(1, 240000) AS s(i);

VACUUM ANALYZE test2;

在此示例中,某些行中的str列值为 NULL:

EXPLAIN SELECT * FROM test2 WHERE str IS NULL;
                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on test2  (cost=0.00..4847.00 rows=10056 width=69)
   Filter: (str IS NULL)
(2 rows)

优化器会将总行数乘以空值比率:

SELECT round(reltuples * s.null_frac) AS rows
FROM pg_class
  JOIN pg_stats s ON s.tablename = relname
WHERE s.tablename = 'test2'
  AND s.attname = 'str';
 rows
-------
 10056
(1 row)

这非常接近 10056 的真实值。

优化空值数量的统计信息

不管什么时候你显著地改变了表中的数据分布后,我们都强烈推荐运行 ANALYZE。这包括向表中批量加载大量数据的情况。运行ANALYZE(或者VACUUM ANALYZE)保证规划器有表的最新统计信息。 如果没有统计数据或者统计数据过时,那么规划器在查询规划时可能会选择出很差劲的执行计划,导致在任意表上的查询性能低下。

需要注意的是,如果启用了 autovacuum 守护进程,它可能会自动运行ANALYZE;有关详细信息,请参阅参数 autovacuum_analyze_thresholdautovacuum_analyze_scale_factor

了解更多

PostgreSQL 优化