主页 / dba / 问题 / 311006
Accepted
AndreKR
Asked: 2022-04-16 09:19:54 +0800 CST

除了索引之外，我还需要统计信息吗？

  • 772

假设我有一张包含应该经过几个步骤的图像的表格：

CREATE TABLE images (filename text, extracted bool, cropped bool, resized bool);
    
INSERT INTO images (filename, extracted, cropped, resized)
VALUES
    ('foo', false, false, false),
    ('bar', true, false, false),
    ('baz', true, true, false),
    ('qux', true, true, true);

在某些时候，我有一个查询来查找所有被裁剪但仍需要调整大小的图像：

SELECT count(*) FROM images WHERE cropped AND NOT resized;

现在我相信使查询快速的最好方法是部分索引：

CREATE INDEX ON images (cropped, resized) WHERE (cropped AND NOT resized);

我将其设为局部，因为这cropped AND NOT resized是一种相对罕见的状态，而可能有数百万张图像已经完全处理，还有数百万张图像尚未裁剪。

我现在的问题是，除了索引之外，我还需要统计信息吗？

其中之一？

CREATE STATISTICS stat1 (dependencies) ON cropped, resized FROM images;
CREATE STATISTICS stat2 (ndistinct) ON cropped, resized FROM images;
CREATE STATISTICS stat3 (mcv) ON cropped, resized FROM images;
ANALYZE images;

我找到了我之前错过的章节How the Planner Uses Statistics （或者更确切地说与Planner 使用的统计信息混为一谈），但它只讨论了如何将统计信息转换为行估计。鉴于显然没有关于索引的统计数据，我不清楚如何选择索引。

postgresql statistics

3 个回答

  1. 你在很大程度上想多了。您的查询非常简单，只有几种方法可以执行。无论是返回 7000 行还是 2000 行，这都无关紧要，因为无论哪种方式，索引似乎都比微不足道的替代方案要好。

    如果您确实想要运行更多种类的查询，这些查询有更多机会做出错误的规划器选择，那么包括 mcv 种类的扩展统计信息可能很重要。

    你的两个例子完全不匹配。您问题中的计数表会导致行估计与您的答案中显示的大不相同。如果没有扩展的 MCV 统计信息，它将提供大约 5,000,000，如果有扩展的统计信息，它将提供大约 1。当然不是 6872 对 1782。

    • 1
  2. Best Answer

    我的实验表明，目前我似乎不需要这些统计数据来使用索引，但仍不清楚为什么会这样。在深入了解源代码后，我想我可以回答我自己的问题。

    本质上，使用索引的决定是btcostestimate()依次做出的genericcostestimate()。

    它有助于记住每个表可以使用哪些类型的统计信息：

    • 元组数
    • 对于每一列：不同值的数量（有时称为“基数”）
    • 对于每一列：最常见的值
    • 对于每一列：剩余（不太常见）值的直方图
    • 如果已配置：dependenciesstats（“A 列中有多少个值只有一个值出现在 B 列中。”）
    • 如果已配置：ndistinct统计信息（A 和 B 列中唯一值组合的数量。）
    • 如果已配置：mcv统计信息（A 和 B 列中最常见的值组合。）

    对于每个索引，Postgres 确定可以使用索引检查哪些条件（“索引条件”或“索引质量”）。基于这些，genericcostestimate()（使用clauselist_selectivity()）计算索引的选择性，同时考虑到那些扩展的统计数据。这实际上反映在我的实验中，因为我通过以下mcv类型的扩展统计数据获得了更好的行估计：

    截屏
    截屏
    实际时间的差异取决于缓存。

    部分索引的谓词也会被考虑在内，但前提是它引入了额外的限制，所以这里不相关。

    所以我认为如何选择索引是这样的：首先检查索引谓词以查看索引是否可用。然后计算特定条件的选择性，如果没有扩展统计数据，这确实有点错误。但是再往下看，当计算实际成本时，它是如此之低，因为索引太小，以至于即使错误的行估计成本也非常低。

    所以答案是肯定的，对于良好的行估计仍然需要理论上的扩展统计信息，但也不需要，仍然选择没有扩展统计信息的索引，因为它太小了。

    • 0

  3. 您的查询没有充分过滤表。因此 postgres 查询优化器不使用此索引并选择顺序表扫描（或表全扫描）。

    您可以更改表设计和查询设计。或者，您应该同意顺序扫描。

    • -3

相关问题