POSTGRESQL: CREATE STATISTICS – 高级查询优化

飞象数据 2022-04-01

936

PostgreSQL 使用CREATE STATISTICS
进行查询优化是一个重要的话题。通常情况下，PostgreSQL 优化器（查询计划器）做得很好。这不仅适用于 OLTP，也适用于数据仓库。但是，在某些情况下，优化器仅仅是缺乏正确完成工作所需的信息。其中一种情况与字段间的相关性有关。让我们深入了解这意味着什么。

违反统计独立性

通常，数据根本不相关。汽车的颜色可能与消耗的汽油量无关。您不会期望狗数量与咖啡价格之间存在相关性。但是，有时字段之间确实存在相关性，这可能会在查询优化期间导致问题。

为什么会这样？在 PostgreSQL 中，每列都存储了统计信息。PostgreSQL 知道不同条目的数量、数据的分布等——默认情况下它不知道值之间是如何关联的。一些例子：你知道”年龄“和“身高“相关（婴儿出生时不是 6 英尺高），你知道“国家”和“语言”通常是相关的。但是，PostgreSQL 查询规划器不知道这一点。该问题的解决方案是扩展统计信息（CREATE STATISTICS
）。

让我们创建一些相同的示例数据：

test=# CREATE TABLE t_test (id serial, x int, y int, z int);
CREATE TABLE
test=# INSERT INTO t_test (x, y, z)
          SELECT id % 10000,
                   (id % 10000) + 50000,
                   random() * 100
          FROM generate_series(1, 10000000) AS id;
INSERT 0 10000000
test=# ANALYZE ;
ANALYZE

我们在这里做的是创建 1000 万行。神奇之处在于前两列：我们看到“y”与“x”直接相关——我们只需加 50000 以确保数据完全相关。

检查优化器估算

在下一个示例中，我们将查看一个简单的查询，并了解 PostgreSQL 如何处理统计信息。为了使计划更具可读性并使统计信息更易于理解，我们将关闭并行查询：

test=# SET max_parallel_workers_per_gather TO 0;
SET
test=# explain SELECT x, y, count(*) FROM t_test GROUP BY 1, 2;
                            QUERY PLAN
------------------------------------------------------------------------
HashAggregate (cost=741567.03..829693.58 rows=1000018 width=16)
     Group Key: x, y
     Planned Partitions: 32
     -> Seq Scan on t_test (cost=0.00..154056.75 rows=10000175 width=8)
(4 rows)

我们在这里看到的非常有趣：规划器假设将返回大约一百万行。然而，事实并非如此——如以下清单所示：

test=# explain analyze SELECT x, y, count(*) FROM t_test GROUP BY 1, 2;
                              QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=741567.03..829693.58 rows=1000018 width=16) (actual time=2952.991..2954.460 rows=10000 loops=1)
     Group Key: x, y
     Planned Partitions: 32 Batches: 1 Memory Usage: 2577kB
     -> Seq Scan on t_test (cost=0.00..154056.75 rows=10000175 width=8) (actual time=0.036..947.466 rows=10000000 loops=1)
Planning Time: 0.081 ms
Execution Time: 2955.077 ms
(6 rows)

如您所见，规划器大大高估了group的数量。为什么会这样？规划器只是将“x”中的条目数乘以“y”中的条目数。所以 10.000 x 10.000 等于 100 万。请注意，结果并不精确，因为 PostgreSQL 在这里使用的是估算值。错误的估算会大大降低性能并导致严重的问题。

CREATE STATISTICS：解决仓储问题

因此，需要一个解决方案：CREATE STATISTICS
允许您在给定字段列表的情况下创建有关不同条目的预期数量的统计信息：

test=# CREATE STATISTICS mygrp (ndistinct) ON x, y FROM t_test;
CREATE STATISTICS
test=# ANALYZE t_test;
ANALYZE

在这种情况下，需要 x
和 y
的统计数据。使用 ANALYZE
可以轻松刷新这些统计数据。PostgreSQL 将自动为您维护这些扩展统计信息。关键问题是：这对我们的估算意味着什么？让我们再次运行相同的查询并检查估算：

test=# explain SELECT x, y, count(*) FROM t_test GROUP BY 1, 2;
                            QUERY PLAN
-----------------------------------------------------------------------
HashAggregate (cost=229052.55..229152.39 rows=9984 width=16)
     Group Key: x, y
     -> Seq Scan on t_test (cost=0.00..154053.60 rows=9999860 width=8)
(3 rows)

哇，9984 组。我们正中目标。如果您的查询更复杂，则此类改进会对性能产生巨大影响并显着加快速度。您在此示例中看到的是CREATE STATISTICS
使用“ ndistinct
”方法的“ ”。在 GROUP BY
的情况下，此方法正是您所需要的。如果您想加快标准WHERE
子句的速度，您可能需要深入研究dependencies
方法以提高统计准确性。

最后 …

如果您想了解更多关于查询优化的一般信息，您可能需要查看我关于 GROUP BY 的博客文章。它包含一些关于如何更快地运行分析查询的宝贵技巧。

postgresql

文章转载自飞象数据，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

POSTGRESQL: CREATE STATISTICS – 高级查询优化

违反统计独立性

检查优化器估算

CREATE STATISTICS：解决仓储问题

最后 …

评论