主页 / dba / 问题 / 167525
Accepted
Mad Scientist
Asked: 2017-03-19 10:15:42 +0800 CST

具有 btree 索引的 jsonb 列的统计信息不一致

  • 772

我注意到涉及 jsonb 列的查询的性能在 VACUUM ANALYZE 运行期间差异很大，同时测试它。在分析表格后，我似乎随机地得到了完全不同的执行计划。

我在这里使用 Postgres 9.6。我的测试设置如下，我在 jsonb 列“params”中插入一个键“x”，其值介于 1 和 6 之间，其中 1 是最稀有的值，而 6 是最常见的值。我还有一个常规的 int 列“single_param”，其中包含相同的值分布以进行比较。：

CREATE TABLE test_data (
    id      serial,
    single_param    int,
    params      jsonb
);

INSERT INTO test_data
SELECT 
    generate_series(1, 1000000) AS id, 
    floor(log(random() * 9999999 + 1)) AS single_param,
    json_build_object(
        'x', floor(log(random() * 9999999 + 1))
    ) AS params;

CREATE INDEX idx_test_btree ON test_data (cast(test_data.params->>'x' AS int));
CREATE INDEX idx_test_gin ON test_data USING GIN (params);
CREATE INDEX ON test_data(id)
CREATE INDEX ON test_data(single_param)

我正在测试的查询是用于分页结果的典型查询，我按 id 排序并将输出限制为前 50 行。

SELECT * FROM test_data where (params->>'x')::int = 1 ORDER BY id DESC LIMIT 50;

运行后，我随机得到两个解释分析输出之一VACUUM ANALYZE：

Limit  (cost=0.42..836.59 rows=50 width=33) (actual time=39.679..410.292 rows=10 loops=1)
  ->  Index Scan Backward using test_data_id_idx on test_data  (cost=0.42..44317.43 rows=2650 width=33) (actual time=39.678..410.283 rows=10 loops=1)
        Filter: (((params ->> 'x'::text))::integer = 1)
        Rows Removed by Filter: 999990"
Planning time: 0.106 ms
Execution time: 410.314 ms

或者

Limit  (cost=8.45..8.46 rows=1 width=33) (actual time=0.032..0.034 rows=10 loops=1)
  ->  Sort  (cost=8.45..8.46 rows=1 width=33) (actual time=0.032..0.032 rows=10 loops=1)
        Sort Key: id DESC
        Sort Method: quicksort  Memory: 25kB
        ->  Index Scan using idx_test_btree on test_data  (cost=0.42..8.44 rows=1 width=33) (actual time=0.007..0.016 rows=10 loops=1)
              Index Cond: (((params ->> 'x'::text))::integer = 1)
Planning time: 0.320 ms
Execution time: 0.052 ms

不同之处在于，与 where 子句匹配的列数的估计值在两个计划之间是不同的。第一个估计是 2650 行，第二个是 1 行，而实际数字是 10 行。

以下可能使用 GIN 索引的查询版本似乎对 json 列使用了 1% 的默认估计值，这也导致了上述错误的查询计划：

SELECT * FROM test_data where params @> '{"x": 1}' ORDER BY id DESC LIMIT 50;

我最初的假设是 Postgres 不会对 jsonb 列有任何统计信息，并且总是使用估计值，就像使用@>运算符进行查询一样。但是对于为能够使用我创建的 btree 索引而编写的查询，它使用不同的估计值。有时这些足够好，有时它们很糟糕。

这些估计来自哪里？我猜它们是 Postgres 使用索引创建的某种统计信息。对于列统计信息，可以选择收集更准确的统计信息，这些统计信息有类似的吗？或者任何其他方式让 Postgres 在我的情况下选择更好的计划？

postgresql index-tuning

1 个回答

  1. Best Answer

    目前（9.6 版）， Postgres没有任何关于文档类型（如、或. （已经讨论过是否以及如何改变它。）相反，Postgres 查询计划器使用恒定的默认频率估计（就像你观察到的那样）。jsonjsonbxmlhstore

    但是，对于像您的idx_test_btree. 该手册为您提供了以下提示：

    提示：虽然频率的每列调整ANALYZE可能不是很有效率，但您可能会发现对收集的统计信息的详细程度进行每列调整是值得的 ANALYZE。在子句中大量使用WHERE且具有高度不规则数据分布的列可能需要比其他列更细粒度的数据直方图。请参阅ALTER TABLE SET STATISTICS或使用 default_statistics_target 配置参数更改数据库范围的默认值。

    此外，默认情况下，有关功能选择性的信息有限。但是，如果您创建使用函数调用的表达式索引，则会收集有关该函数的有用统计信息，这可以大大改进使用表达式索引的查询计划。

    收集的统计量取决于 的一般设置default_statistics_target，可以用每列设置来否决。列的设置自动覆盖依赖的索引。

    的默认设置100是保守的。对于 1M 行的测试，如果数据分布不均匀，可能有助于大幅增加它。再次检查这一点，我发现您实际上可以使用 调整每个索引列的统计目标，ALTER INDEX目前没有记录。请参阅有关 pgsql-docs 的相关讨论。

    ALTER TABLE idx_test_btree ALTER int4 SET STATISTICS 2000;  -- max 10000, default 100

    索引列的默认名称并不完全直观，但您可以通过以下方式查找：

    SELECT attname FROM pg_attribute WHERE attrelid = 'idx_test_btree'::regclass

    应该将类型名称int4作为您案例的索引列名称。

    最佳设置STATISTICS取决于几个因素：数据分布、数据类型、更新频率、典型查询的特征、...

    在内部，这设置了 的值pg_attribute.attstattarget，其确切含义是（根据文档）：

    对于标量数据类型，attstattarget既​​是要收集的“最常见值”的目标数量，也是要创建的直方图 bin 的目标数量。

    ANALYZE如果您不想等待 autovacuum 启动，请运行：

    ANALYZE test_data;

    您必须ANALYZE使用该表，因为您不能ANALYZE直接索引。检查（如果要验证效果之前和之后）：

    SELECT * FROM pg_statistic WHERE starelid = 'idx_test_btree'::regclass;

    再次尝试查询...

    有关的：

    • 7

相关问题