postgresql:为什么空值比空字符串提高了性能？

Question

关闭。这个问题需要details or clarity .它目前不接受答案。












想改善这个问题吗？通过 editing this post 添加详细信息并澄清问题.

8 个月前关闭。




Improve this question




我们有一个包含 6M 记录的表。它包含分层实体，即每个实体都有一个链接到 parent - parent_id 字段。
95% 的行是顶级实体，即它们有一个空的 parrent_id 字段(空字符串)。
单个实体的递归查询(以获取其所有子实体)存在性能问题。查询计划器错误地估计了 child 的数量，并通过 parrent_id 字段更喜欢“seq scan”而不是“index scan”。
我认为规划器以这种方式工作是因为我们的 parrent_id 值分布不均匀。
pg_stats 显示:
most_common_value: ''
n_distinct:73(实际上几乎所有的子实体(表的 5%)都有不同的 parrent_id)
似乎分析器没有足够的行。但是，当我们将空字符串值更改为 NULL 时，一切都会变得更好。规划器使用索引。
我不是专家，我想知道这个带有 NULL 的技巧是否是常见的做法，或者这只是一个意外，随着时间的推移，分析器过程可能会破坏统计数据，而我们又回到了缓慢的性能？

Answer 1

我想你身上发生的事情是这样的:

CREATE TABLE rare (x integer);

INSERT INTO rare
SELECT CASE WHEN random() < 0.05
            THEN i
            ELSE 0
       END
FROM generate_series(1, 100000) AS i;

ANALYZE rare;

SELECT null_frac, n_distinct, most_common_vals, most_common_freqs
FROM pg_stats
WHERE tablename = 'rare'
  AND attname = 'x';

 null_frac | n_distinct | most_common_vals | most_common_freqs 
-----------+------------+------------------+-------------------
         0 |       1471 | {0}              | {0.9526333}
(1 row)

SELECT count(DISTINCT x) FROM rare;

 count 
-------
  4904
(1 row)

而对于 NULL 值，它看起来像这样:

TRUNCATE rare;

INSERT INTO rare
SELECT CASE WHEN random() < 0.05
            THEN i
            ELSE NULL
       END
FROM generate_series(1, 100000) AS i;

ANALYZE rare;

SELECT null_frac, n_distinct, most_common_vals, most_common_freqs
FROM pg_stats
WHERE tablename = 'rare'
  AND attname = 'x';

 null_frac  |  n_distinct  | most_common_vals | most_common_freqs 
------------+--------------+------------------+-------------------
 0.95093334 | -0.049066663 |                  | 
(1 row)

不同的是，在第二种情况下，PostgreSQL 意识到非空值几乎是唯一的(它们实际上是唯一的)，因此它将不同值的数量表示为负比率。否定，将其与正常情况和比率区分开来，因为如果数据发生变化，它仍然更准确。
PostgreSQL 只取表的一个样本来计算统计信息，因此不同值的绝对计数(在本例中为 1471)当然离标准很远，但分数(当使用分数大小计算时!)非常准确。
在第一种情况下，PostgreSQL 会估计

SELECT DISTINCT x FROM rare;

有 1471 个结果行，而在第二种情况下它会估计 100000 * 0.049066663 ≈ 4907行。
现在这解释了你所看到的，但是你能做些什么来改进估计呢？
答案是取更大的样本:

ALTER TABLE rare ALTER x SET STATISTICS 1000;

ANALYZE rare;

然后 PostgreSQL 将采用更大的样本并提出更准确的估计。